Back
Excel

Sparklines – Lining Up the Points

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 sales for each year, the cost of sales, and net profit for that year all divided by the category of book.  One of the things you can do with sparklines in Excel 2010 is to stick them in cells above or below each other and have the points line up.  Doing this will allow you to see trends for multiple fields in a related way.

2

Here I’ve taken the table, hooked up sparklines to it, and broke it out into a dashboard like sheet.  This conveys the trend of sales and profit for each year for past 10 years.  The sales are represented by the line sparkline, and the profit is the win / loss sparkline below it.  You can see in some places, like romance, that even though sales have fallen, we still turned a profit … and the opposite is true for sports.

Axis options can make a big difference here.  Right now the axis for the sparkline group is set to automatic, so each sparkline is showing the trend for its data, if we make the sparklines share a single axis, you can also tell which category has more sales than others:

3

In this example, mystery has traditionally had a lot more sales than history, but now they are about the same.  To create something like this isn’t too hard in Excel:

4

Here you can see that the line sparklines and win / loss sparklines are just one cell above the other and their points line up.  One other thing to note here is that I’ve added conditional formatting after the end of the win / loss sparkline.  There are upcoming blog posts on the new conditional formatting features, so more details to come on that.  You can vary the design of this a lot, and format the cells around and the sparkline cell itself to show highs and lows:

5

In the first example above, the number is in the cell with the sparkline.  Since sparklines in Excel sit in the cell like data bars and icon sets, numbers can exist with them in the same cell.  In the second example they are in the cells next to the sparkline, color coded to match the high and low point … and the last example shows the original table with the sparklines embedded in line with the data.

And on that note I’ll wrap up the sparkline post for today, still a couple more coming this week, stay tuned!