What are Excel sparklines and how can you use them to highlight trends in your data? Basically, sparklines are miniature (lightweight) versions of line or bar charts that are easy to create, adapt, and scale. In this article, we’ll find out how to create sparklines and see some typical uses of these useful gadgets!
Excel Sparklines: Mini Charts
Sparklines are all about convenient data visualization. You can think of them as mini charts. If you just need a quick graphic to illustrate your data, but you don’t want to create a full-blown Excel chart, then Excel sparklines are perfect!
- Sparklines are not chart objects; instead they exist as background images within a cell.
- They will instantly update when the data changes.
- You can drag a sparkline cell to automatically create new sparklines in additional rows.
- Excel Sparklines are customizable.
- You can type text, numbers, or formulas directly into the cell containing a sparkline. You can even use conditional formatting on the cell.
Example: Stock Markets
Suppose you want to keep track of the ups and downs of your favorite stocks from week to week.
First you might create a table of stock prices in Excel.
Then click on the cell in which you want to place a sparkline chart. I decided to place the chart into cell H2 because that cell is immediately to the right of the data that I want to visualize.
Next, go to INSERT, and choose the type of chart you want to include. Here, we have chosen “Line.” (You can modify the colors and other characteristics later.)
This brings up a dialog box. Just fill in the range of data (B2:G2 in our example).
Click OK, and voilà! You should now see you very first sparkline chart!
Customize and Duplicate
Now comes the really fun part! You can add markers at each data point and choose different colors for maximum and minimum points.
Then, you may drag the cell (H2 in this example) down to instantly create similar charts for all of the data in you table. (The result should look like the screenshot at the beginning of this article.)
Additional Formatting Tricks
One very nice thing about Excel Sparklines is that they are part of the background of the cell in which they are drawn. In other words, you can still put text, numbers, formulas, and other items in the cell. I find that extra textual items clutter up the chart, but you may find this feature quite useful.
Sparklines and Conditional Formatting
On the other hand, you could add conditional formatting rules to the cells in order to catch the reader’s attention. (For a crash course on conditional formatting, check out: How to Use Excel Conditional Formatting.)
For example in our stock market example, we could add a conditional formatting rule to highlight markets that have gone up since last week. In my example, I wanted to shade the cell green if there was a gain from last week to this week, and red if there was a loss. This will take two separate rules.
To do this, click the cell containing the first Sparkline chart (H2). Click Conditional Formatting from the Home tab, and select “New Rule…”
In the next dialog box, select “Use a formula to determine which cells to format.” Type the following into the formula box:
= (G2 – F2) < 0
Then pick a color like red to represent “loss” (because the difference would be negative).
After you’ve completed this series of steps, do it all over again, but now for gains. This time, your formula should have the inequality going the other direction:
= (G2 – F2) > 0
Here, you should select a color like green for “gain.”
Finally, select the cell H2 and drag it down to ensure that your formatting rules will apply to H3 and H4 as well!
Here are screenshots of the completed conditional formatting rules and the result on our sparkline cells.
Adjusting the Dimensions
Because Excel Sparklines are background images, they will stretch or compress to fit the cell.
In our stock market example, it’s hard to tell where the ups and downs really are. So let’s increase the height of rows 2, 3, and 4 to better illustrate the differences in height on each chart. I will also make column H more narrow, which further helps to accentuate height differences. Notice how the Sparklines stretch vertically and compress horizontally to fit the cell’s new dimensions.
Excel Sparklines provide an easy way to add lightweight graphics to your data.
- A Sparkline chart is a good choice for summarizing data within a row of a table. They typically will be placed in the same row, to the right of the data.
- A Sparkline chart not a full-blown Excel chart. Instead, it exists as a background image within a cell. The chart will scale with the cell.
- There are many style features, including adding markers for maximum and minimum points and colors for various elements.
- You can place text, numerical data, formulas, and other items in the same cell with a Sparkline chart.
- Cells containing Sparkline charts can be enhanced using conditional formatting.
- Once you have defined a chart in a given cell, then you can drag the cell down to automatically create charts for each row of your data.