Back
Excel

Sparkline Axis Options

In this blog post I’ll walk through some of the axis options for sparklines in Excel 2010 using sample sparklines from the book store demo file:

1

The above sparklines are displaying the number of books by category for each month.  The sparklines in this example are giving the history of the count of books for each category, and it looks like they are at their lowest over the past half year or so.

Let’s take look at the axis options for these sparklines.  Selecting the sparklines gives you a sparkline contextual tab in the ribbon:

2

Most of the options listed are similar to charting. You can turn on and off displaying the horizontal axis for the sparkline, plot data RTL, configure the date axis, and set the vertical min and max.  Let’s talk about the vertical min and max since they have a unique setting for sparklines.  By default, they are set to automatic, which means the sparkline’s vertical min and max are different for each sparkline, and based on the data each sparkline is bound to.  You can choose a custom value for the min or max, and it works like chart … or you can also choose the same for all sparklines option, which will look at the data the group is bound to, and set a min and max that is the same for all of the sparklines in the group:

3

Here we can see that some categories, like history, have fewer books when compared to romance.  This option works well when its important to see the scale of the group, but you can often end up with a flat line at the bottom of the cell and a set of full bars touching the top of the cell if your data is spread to far apart, say if you’re tracking stock values and some stocks are between 1 dollar and some are around 400 dollars.

Another option is the date axis … it works similar to charts date axis.  For this example we can select the dates the inventory counts were taken and use it as the date axis:

4

You can see that we missed a couple months and that is now reflected in the sparklines.  It’s important to note that the axis scaling options and date range option are group based.  There are times with the same scale option where you may need to break out a total row or summary sparkline into a different sparkline group so you don’t throw off the scaling for the non-total sparklines.

That’s all the posts for sparkline this week, next week we’ll dive into some specific techniques for using them in Excel and some tricks around leveraging the horizontal axis as a target line.  Stay tuned!