Stackable sparklines

Sparklines can be very useful in dashboards. However, they are somewhat limited as they stand, with the result, that I often use real charts formatted to look like sparklines in a chart instead. This way, I can have conditional formatting in my chart to have 2 different colours when a value is below target (and not just below zero).

However, after playing around with linked pictures and sparklines, I realized that they could be combined and I could actually create the chart above by stacking 2 different sparklines together, or have a stacked sparkline like what I’ve done in the first chart below. I can also combine two different types of sparklines as well as shown in the second chart.

If you wish to download the sample data, charts and sparklines I used you can do so here.

How is it done?

First create your table of data. Add a total row to the data:

Let’s imagine that data 2 is the series that we wish to show at the bottom of the chart and data 2 at the top. In this case, create a sparkline for the data 2 series and for the total series. To do this, highlight the data you wish to create a sparkline from and click on Insert > Column in the sparkline section of the insert tab of the ribbon

Check that the data range matches the data you wish to show and then enter the cell reference (Location Range) for the cell where you wish the sparkline to be created. Then press the OK button. Repeat the process for the total series. You should then have 2 sparklines.

Click on the cell for the total sparkline. Then on the Home tab of the ribbon, click on the small down arrow next to the copy button and choose to copy as picture.

Accept the default settings as shown below and press the OK button.

Click on another empty cell and press Ctrl+V. A picture of the graph should appear in the cell. With the picture still selected, click in the formula bar and enter an equal sign and then the cell reference for the sparkline. This links the picture to the original sparkline and ensures that when the sparkline updates, so does the picture!

Repeat the process for the data 2 sparkline.

Then simply click on the first picture, hold down the shift button and click on the second picture and click on the Picture tab > Align > Align Left. Then repeat choosing Align Top.