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
If YouTube doesn't work, click here
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.
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.
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.
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).