Software

Format Excel 2010 sparklines to expose details

Use sparklines to display details about your data right in the sheet! Just don't forget to format them to fine-tune the picture.

Excel 2010's new sparklines have an immediate and significant impact. With a quick glance, you can see trends, growth, loss, and so on. In other words, you can spot what's working and what isn't. The good news is that they're easy to add to a spreadsheet, but inserting them is just the beginning. With a little easy formatting, sparklines are even more forthcoming.

To insert sparklines, select a column to the right of the data you want to emphasis. Then, select one of the options in the Sparklines group on the Insert tab. (I used Column for the example.) The sparklines in the following sheet share some interesting information without actually perusing the actual data values:

  • Jones is the best performer.
  • Jones is the top performer in half the regions.
  • Although Hancock is the top performer in the North, he's the poorest performer in two other regions (half the regions).

As is, these sparklines leave a bit of the story untold, or perhaps I should say, under told. Michaels has a poor showing in the Western region, right? Unless you look closely, you might miss that it's worse than you first think—Michaels is actually in the hole! Formatting sparklines will expose the details you might otherwise miss. In this case, it takes just a few quick steps:

  1. Select the sparklines (in this case, that's F2:F7). Doing so will display the context Sparkline Tools Design tab.
  2. Click the Design tab.
  3. In the Show group, click Negative Points. Immediately, that negative value pops out—there's no way you'll miss that bad showing!

I think the Negative Points default color (based on the default theme) is too saturated. A brighter red will pop better. Fortunately, you can quickly change the color:

  1. Select the sparklines to expose the Design tab.
  2. In the Style group, click the Marker Color dropdown.
  3. Check the Negative Points option and select a brighter red from the palette.

There's another setting that you can improve. By default, Excel determines the vertical axis for each set of values. Those settings might not draw the best picture from row to row, as far as relationships go. After selecting the sparklines, choose Same For All Sparklines from both Vertical Axis Minimum Value Options and Vertical Axis Maximum Value Options.

This change might draw attention to something else; the Totals and Commission values skew the overall results. I did this on purpose to emphasis a good rule of thumb when working with sparklines—use them on related and consistent data. In this case, the Totals and Commission values are certainly related to the data values, but they're not consistent with the data values. These two rows don't represent individuals. They represent all the records. In order to get a more accurate visual representation of your data, don't include these types of rows in your sparklines. You can use sparklines, but insert them separately.

Sparklines provide quick insight into your data, but formatting exposes important details. Familiarize yourself with the different formatting options so your sparklines always tell the most accurate story about your data.

If the Sparkline options are dimmed, you've opened an .xls file. Save the workbook as an .xlsx workbook, close it, and reopen it.

About Susan Harkins

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks

Free Newsletters, In your Inbox