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

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 (Dropdown 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 checkboxes 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. The 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 the 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

     


Similar Readings


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

Download working file

Now download the working file from the link below.


Related Articles

Zhiping Yan

Zhiping Yan

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 a 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

ExcelDemy
Logo