Back
Excel

Formatting Sparklines

In this blog post I’ll walk through formatting sparklines in Excel 2010 using sample sparklines from the book store demo file:

1

The above sparklines are displaying the net profit by month for the three book stores in 2008.  The net profit number alone can tell you which stores are doing well, but the sparklines help identify trends behind the number: Seattle and Houston both dipped in the summer, in general their profits climbed as the year went on, and Richmond didn’t do well until the last month.

Let’s take look at the formatting options for these sparklines.  Selecting the sparklines gives you a sparkline contextual tab in the ribbon, similar to selecting a chart or pivot table.  Below is a zoomed in picture of the two chunks that have most of the formatting options, show/hide and style:

2

Sparklines have a gallery similar to charting and tables where you can quickly choose a color scheme.  Some of the unique options for sparklines are on the show/hide chunk where you can enable points.  Checking a point option will turn on or highlight that point for each sparkline.  So if you turn on high points, for a line sparkline you will get a point on each sparkline that is the high point.  For column and win/loss the point that is the high point will be formatted differently.  If there is a tie for high point, both will be highlighted.  Negative points will turn on or highlight the negative points in the sparkline, if we turn them on with these sparklines we get:

3

The sparklines become more noticeable, emphasizing that Richmond only had one positive month throughout the year.  The gallery will also update to reflect the options checked in the show / hide chunk.

You can also choose colors from a color picker for all the points and sparkline color.  Next to the gallery are two dropdowns that will fly out color pickers and some other options.  For this example I’ll change the sparkline color to a gray and the negative points to a brighter red:

4

Formatting options are stored as you switch the type of sparkline, so picking a gray column sparkline and switching to a line sparkline will give you a gray sparkline.  Also note that the markers option is disabled.  This is due to the fact that a column sparkline type is selected, and the markers option only applies to line sparklines.  There is also a show axis option in the axis menu that applies to all sparkline types.

You can create all kinds of looks for the sparklines by mixing colors, points, and axis:

5

It’s important to note that the formatting is group based, and in the example above the sparklines are all in the same group so setting a single color / point option sets it for all of them.  If you wanted to make one of the sparklines a different format it would have to be removed from the group first.

For the next post I’ll get into the axis dropdown and talk about the options there.  Stay tuned!