Sparklines in Excel (Insert, Change, & Modify to Show Data Trends)!

[Editor’ Note: This article (written by Zhiping Yan) will guide you completely on Sparklines in Excel. You will learn how to insert, and modify sparklines. She has also covered how to use Excel conditional formatting rules briefly.]

It is well known that we can draw a chart easily in Excel. But is it possible to graphically summarize data in a single cell? The answer is “Yes”. And the feature that can help you is called sparklines. In essence, sparklines are graphs. Actually mini graphs. It can summarize a row or a column of data in a single cell.

Table of Contents

Insert Sparklines in Excel
Modify Your Existing Sparklines
How to Use Excel’s Conditional Formatting Rules
Read More…
Download working file

Insert Sparklines in Excel

Suppose that we have the temperature of different cities for next 7 days. How can we graphically summarize temperature for each city in their respective cells?

City	15-Nov	16-Nov	17-Nov	18-Nov	19-Nov	20-Nov	21-Nov
New York	50	60	62	64	64	47	45
Miami	71	79	79	80	80	73	75
Los Angeles	63	71	73	79	75	72	66
Princeton	47	62	71	63	42	40	45
Chicago	49	58	68	64	42	40	41
Houston	73	84	82	79	63	65	74
San Francisco	59	62	64	65	67	64	63

Before inserting sparklines, we need to enter the above data into Excel (range A2:H9 in our case). Then select the range (I3:I9) where we want our sparklines to show and click on the Insert tab. Then select Line in Sparklines group of commands.

Figure 1.1

Then in the prompted Create Sparklines dialog box, fill our source data range B3:H9 into “Choose the data that you want” field and then click on the OK button. And in the Location Range field, input our range where we want to see the mini-graphs (read sparklines).

Figure 2.2

You can see now that line graphs summarizing the temperature for each city (I3:I9). From the line graphs, you can see that temperature will decrease sharply on 20 Nov for New York and Miami. By the way, if you look at Figure 1.3 closely, you will find Sparkline Tools Design is appearing in the tab list. With this tab, you can make any changes to your sparklines.

Figure 1.3 [click on the image to get a full view]

Modify Your Existing Sparklines

I just told you that sparklines can be modified via Sparkline Tools Design tab. And that tab will be opened automatically when sparklines are created. Another approach to open this tab is to click on any cell containing a sparkline.

From the Design tab, we can make the following changes to our sparklines:

Change the type of sparkline using the commands in the Type group. For example, clicking on Column, you can enable Excel to show column type sparklines.
Figure 2.1 [click on the image to get a full view]
Clicking on the Edit Data command (above part, not the drop down part of the command), then changing the Data Range and Location Range, you can always redefine your data for sparklines. Plus, the hidden data can also be maintained with the command Hidden and Empty Cells (Drop down part of Edit Data > Hidden and Empty Cells). If we hide column E, you can see that there will be only 6 columns for each sparkline. But if we click on Edit Data -> Hidden & Empty Cells and select Show data in hidden rows and columns, the hidden column E will be included in the sparklines and the number of columns will return to 7 again.
Figure 2.2 [click on the image to get a full view]

By selecting the check boxes in the Show group of commands, you can mark the High Point, Low Point, Negative Points, First Point or Last Point of the sparklines. If you select High Point, peak values of the sparklines will be marked. I have used Excel’s Conditional Formatting to highlight the peak values as shown in Figure 2.3. Here I have shown how you can work with Excel’s conditional formatting features. If you are not familiar with conditional formatting, you can read this article for more examples. Let’s take cell I3 as an example. Two high points of the sparkline in cell I3 have been marked and this result is inconsistent with the results returned by conditional formatting (conditional formatting is showing two top values (64, 64) on the left). And for Los Angeles (cell I5), there is only one peak value and therefore only one point has been marked in the sparkline.
Figure 2.3 [click on the image to get a full view]
We can also change the style or color of sparklines and the sparklines’ markers via commands in Style group. You see from Figure 2.4 that high points and low points are already marked using conditional formatting rules. Numbers in red are the highest values while numbers in green are the lowest values. Also, both high points and low points are marked for each sparkline. And with the commands under Marker Color drop down, you can even customize the color of high points and low points respectively or more. And the colors of these points are in consistence with the result of conditional formatting.
Figure 2.4 [click on the image to get a full view]

Through Axis commands (click on the drop down to see more commands), we can change the way the axes for each sparkline. For example, Vertical Axis Maximum Value can be set to the same for all sparklines. X-axis or y-axis scale can be set to be the same for all the sparklines. By default, each individual sparkline has its own scale. Look at Figure 2.5, it is much easier for us to make comparisons between cities after the y-axis scale is set to be the same for all sparklines. The temperature in San Francisco is lower than that of other cities.
Figure 2.5 [click on the image to get a full view]

Here Figure 2.6 illustrates how Ungroup command works. Top panel tells that all sparklines are grouped together as there is a blue box surrounding them. If you click on cell I4 and then click on Ungroup command, sparkline in cell I4 will be removed from above group. This can be demonstrated by the middle panel. The blue borders disappear now. But if you click on any other cells (bottom panel), the blue borders show again. The remaining cells are still grouped together.
Figure 2.6

How to Use Excel’s Conditional Formatting Rules

Let me show you now, how I have used Excel’s conditional formatting rules.

Select range B3:H3 and then click on Conditional Formatting in Home tab. In the drop-down list, select New Rule.

Figure 3.1

Now, fill New Formatting Rule dialog box and Format Cells dialog box as shown in Figure 3.2. And if you want to highlight the lowest points, select Bottom instead of Top in the Format values that rank in the field.

Figure 3.2

After you click on the OK button, you will see the red color in cell E3 and F3. Select range B3:H3 again and click on the Format Painter command. Now select the range B4:H4. The formatting rule applied in the range B3:H3 will now be applied in the range B4: H4. From the right panel of the below image, you can see that color of values in cell E4 and F4 has changed from black to red.

Figure 3.3 [click on the image to get a full view]

Note: If you want your sparklines to update automatically to include new data, you need to apply dynamic range. Enter dynamic range name when filling Create Sparklines dialog box (Figure 1.2).