Sparklines for trend analysis

One of the new features from Excel 2010 is Sparklines, which allow you to create mini graphs within a cell to highlight trends or changes in data.

To follow along go to 4.1

In the example below we want to create mini line graphs in the yellow cells (C5 to E5) to show the trend in the relevant columns.

sparklines

By clicking on the Insert tab and the ‘Line’ options in Sparklines (see above), you will see the following toolbar appear (see below). Simply show it where the data is and where you want to put the Sparkline and click OK. The result will be the same as in C5 and D5 shown below.

sparklines

Note that you can perform similar customisation as for normal graphs including using column or win/ loss graphs, changing colours etc.

One of the key features available is to be able to change the way the axis operates. Below we have made the data a bit more extreme. Even though Product A has much more sales than Product B, the Sparklines are a similar size. If you are only interested in the trend then this might be OK.

sparklines

But if you want to be able to see trend and relative size, then you need to tell the 3 Sparklines that they must work off the same axis. This is easy.

You need to:

  • Highlight the cells containing the Sparklines
  • Click on the new Design Tab
  • You may need to click the GROUP button if these cells are not grouped
  • Click on the Axis dropdown
  • Click on Same for All Sparklines under Vertical Axis Minimum
  • And again, click on Same for All Sparklines under Vertical Axis Maximum

Note below that now, Product B and C are shown to be minute relative to Product A (which makes sense if you look at the numbers).

sparklines

Back to: Excel Dashboards and Management Reporting > Useful Excel Tools for dashboarding