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.

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?

City15-Nov16-Nov17-Nov18-Nov19-Nov20-Nov21-Nov
New York50606264644745
Miami71797980807375
Los Angeles63717379757266
Princeton47627163424045
Chicago49586864424041
Houston73848279636574
San Francisco59626465676463

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.

Spartlines in Excel Figure 1.1

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).

Spartlines in Excel Figure 2.2

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.

Spartlines in Excel Figure 1.3

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.
    Spartlines in Excel Figure 2.1

    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.
    Spartlines in Excel Figure 2.2

    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.
    Spartlines in Excel Figure 2.3

    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.
    Spartlines in Excel Figure 2.4

    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.
    Spartlines in Excel Figure 2.5

    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.
    Spartlines in Excel Figure 2.6

    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.

Spartlines in Excel Figure 3.1

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.

Spartlines in Excel Figure 3.2

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.

Spartlines in Excel Figure 3.3

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).

Read More…

How to Use Sparklines in Excel [With a Snail Farming Example]

Change the color of text and background of cells – An Excel Font and Fill Color Complete Guide

How to show the Developer Tab on the Ribbon

 

Download working file

Now download the working file from the link below.


I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as an SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet via programming. This will save me a lot of time. I am keen to learn new things.

We will be happy to hear your thoughts

      Leave a reply