[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
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?
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.
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).
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.
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.
- 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.
- 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.
- 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.
- 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.
- 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.
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.
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.
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.
Download working file
Now download the working file from the link below.