How to highlight details for better insight with sparkline charts in Excel

The recent article, How sparklines deal with non-numeric values in Excel shows you how to avoid pitfalls with non-numeric values in the data set in Microsoft Excel. The best defense is to make sure there are no non-numeric values in a value data set, but that’s not always practical, especially in an imported or inherited sheet. But it’s an important step to ensure the validity of your data. In this article, we’ll continue to work with sparklines, but we’re not searching for errors, we’re exposing values you might otherwise miss! This a problem that’s inherent to the sparkline charts themselves because they’re so small. It’s easy for important details to be lost in the bigger picture. In this article, I’ll show you a few ways to make important details stand out.

SEE: 83 Excel tips every user should master (TechRepublic)

I’m using Microsoft 365 on a Windows 10 64-bit system, but you can use earlier versions. For your convenience, you can download the demonstration .xlsx file. Sparklines aren’t supported by the menu version, .xls. Excel Online will display existing sparklines when you open a workbook but you can’t create or modify them online.

About sparklines

Sparklines have been around for a long time. They’re tiny charts that fit in a cell and provide a quick glance into trends, growth, minimum and maximum values and so on. They’re easy to insert and the impact is significant. To insert sparklines, select the first blank adjacent column to the right of the data set. Then, click the Insert tab and select one of the options, such as Line, in the Sparklines group. Enter the data range (Figure A) and click OK. (If you select the data range, you may have to re-enter the location range; these settings don’t seem to be sticky like other similar settings.) With a glance, you can glean the following about the data:

• James and Martha have all had a recent upswing in sales.
• Luke’s and Rosa’s sales have gone down significantly after having a good showing.
• June and Nick have both increased steadily.

On the other hand, it’s difficult to tell who the top sales go to, but by going only by the sparklines, my bet’s on June, although Nick looks like a close contender. June has had a steady rise in sales with no dips and Nick’s dips aren’t as dramatic as the other’s. Still, you can’t be sure. Sparklines are a neat visual tool, but the default line won’t always tell the whole story.

Figure A

The untold story

Looking at only the sparklines, you might miss some important details because there’s no way to discern 0. Consequently, you might not realize that James made no sales in the Central region and actually lost money in the Northeast. Nor might you realize that Martha made no sales in the Southwest and had a poor showing in the Central region. In addition, Rosa’s sales for the Southeast are significantly less than the other regions.

SEE: Office 365: A guide for tech and business leaders (free PDF) (TechRepublic)  

The sparklines are small and as such, they won’t always expose details that offer important insight. Fortunately, there are ways to make these details stand out.

Formatting to expose details

Perhaps the first visual improvement should expose negative values—and it should really stand out. You can do so as follows:

1. Select the sparklines; that’s H3:H8.
2. Click the Sparkline contextual tab.
3. In the Style group, click the Marker Color dropdown and choose Negative Points (Figure B); I chose red. Only James has a negative sales value.

Figure B

Without that marker, you wouldn’t know that James had a negative sales value or that he was the only one with a negative sales value. That marker doesn’t help you find the top salesperson, though. As is, it is difficult to tell who the top salesperson is, who is on the bottom and so on. That can be fixed with another quick format change.

Excel, by default, evaluates a vertical axis for each record making a visual comparison between records difficult. Instead, you’ll probably want each record to use the same axis, which is easy to implement:

1. Select the sparklines (H3:H8).
2. Click the Sparklines contextual tab if necessary.
3. From the Axis dropdown (in the Group group) choose Same For All Sparklines in both the Vertical Axis Minimum Values Options and Vertical Axis Maximum Values Options (Figure C). 

Figure C

The change is subtle for the most part, but we’re a bit closer than before. What you see is a slight adjustment in those high and low points because the values are so close. When dealing with records where the values are much higher or lower than others, you will see a more distinct change. 

We just made the axis the same for all the records so now let’s display that axis as follows:

1. Select the sparklines (H3:H8).
2. From the Axis dropdown (in the Group group), choose Show Axis.

Figure D shows the results, which make it much easier to see how low those low points really are. Even in this simple example with little change, I do believe it’s more evident than before that June is our top salesperson. 

Figure D

If you still find the sparklines aren’t as forthcoming as you’d like, change the row height and/or the column width. Figure E shows the same sparklines, but the dips and rises are a bit more dramatic than before.

Figure E

Stay tuned

These basic changes can radically change the story the sparklines tell. In a future article, I’ll show you how to use them in a dashboard setting to compare each record to the average sales. 

Microsoft Weekly Newsletter

Be your company’s Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets.
Delivered Mondays and Wednesdays

Sign up today

Also see