Back
Excel

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

Share on Facebook Share on Twitter Share on Linkedin Share via OneNote Share via Email Print

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:

1

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:

2

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:

 

3

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:

4

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

Top