A Sparkline Trick - Using the Horizontal Axis as a Reference Line

In this blog post I’ll walk through some of the ways you can use sparklines in Excel 2010 using sample sparklines from the book store demo file:

In the above example we have the data for the employee sign-up contest, this is a contest where employees try to get customers to sign-up for a bookstore club card.  The contest has gone on for several weeks now, and each employee has a goal of 20 sign-ups per week.  Right now the sparklines are conveying the history of sign-ups for each employee, for example: Sam started off slow but picked it up at the end and Bay was the opposite.

For this release of sparklines there isn’t a way to set a reference line, but similar to chart there are ways to work around it.  One of the ways to get a reference line is to leverage the horizontal axis.  Since the axis crosses at zero, the first step is to subtract the goal from the actual results:

Once you do that employees that make the goal for the week will have a positive number and ones that miss the goal will have a negative number for that week.  Then we can bind the sparklines to the new numbers and turn on the horizontal axis:

Now that we have the reference line in there the sparklines are conveying more information than before: we can tell that Sam has been missing the goal the majority of the time and Bay has been above it throughout the competition.  There are a few different formatting options you can do such as turning on the negative points to see when the goal is missed or changing the sparkline type to a win / loss type:

This covers the sparkline post for today, my final sparkline post will be soon!