Adding Some Spark to Your Spreadsheets

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

In the table we have the sales for the month of June: a column for total sales for the month, multiple columns for each day’s sales, and rows that break out different book categories.  Sparklines will really be able to enhance the table and add to the understanding of the numbers by plotting out the month’s sales day by day in a single column, in line with the data. 

To get started, we’ll insert a column where we want to place the sparklines and go to the insert tab on the ribbon:

For this first release of sparklines in Excel, we’ve added support to create line, column, and win/loss sparklines.  Line and column are similar to their charting counterparts, and win/loss is similar to the 100% stacked column chart.  Throughout these blog posts we’ll examine the different sparkline types and how they are used.  This time we’ll choose the line sparkline.  Clicking the Insert -> Sparklines -> Line button from the ribbon will give you the sparkline insert dialog:

In the dialog there are two input boxes (known as RefEdit controls), one for the data range and one for the location range.  As I’m sure you’ve already guessed the location range is where you want the sparklines to be displayed and the data range is the values that the sparkline will plot.  At this point we have a couple easy options for getting the sparklines into the table.  One option is to select the data range for a single sparkline and press OK on the dialog to create the first sparkline.  Then, fill it down the table using the same method you would use to drag fill formulas.  Sparklines in Excel will fill, adjust, copy, cut, and paste like formulas do.

You can also create multiple sparklines at once.  Just select the cells where you want the sparklines to live, and then select the entire data block they will bind to:

Once they are created in the table you’ll see a bright blue border around them when the active cell selection is on a sparkline … since they were created all at once, or using a fill, they are a part of a group.  Being in the group means they share formatting properties and can share axis options like min and max if you want them to scale together.  You can add and remove sparklines from groups similar to grouping shapes in Excel.  We’ll dive into the grouping, formatting, and axis options later in the week, but at this time it’s important to note that they are in a group on creation.

Now that they are created in the table, we can read the sparklines and quickly tell that most book categories started off low in the month, but then jumped in sales mid-way through the month.  To help us see this, we can turn on a formatting option that highlights the lowest point in the sparkline:

Now it’s clear that most book categories have their low point in the start of the month, except for history, whose sales got worse as the month went on.  This is an understanding that would have been hard to get to had you just read the numbers throughout the month, but can easily be identified when sparklines are applied.

To finish out the table we’ll add the high point to the sparklines, a couple more columns for min and max, and then color code the text to match the color of the points:

One other thing to note here is that since the sparklines are in an Excel table you can add another row of data and the sparkline group would grow and add another sparkline to the table … and you can add more days to the end of the table and the sparklines would pick up the points.  This works similar to other Excel features bound to a table.

And that’s it, simple and easy way to create sparklines in Excel.  For the next post I’ll dive in on some of the formatting options I used in this post, and a bunch more.