Starting with its 2010 version Microsoft Excel has a feature called sparklines. Excel sparklines are small one-cell mini-charts that allow the user to quickly see:
- trends over time, such as sales by quarter
- the relationship between various components, such as sales by location for quarter 1, or
- win-lose scenarios similar to a comparison between fair market value for an investment compared to its purchase price.
Each sparkline is inserted into its own cell and references a source range of cells containing the data being charted. Just like Excel charts, sparklines are ‘live’: the sparkline adjusts itself as the data in the source range changes. You can have multiple sparklines in a worksheet. Also any particular data cell can be a source for multiple sparklines.
The example in this blog post is based on the following worksheet comparing “something” by quarter for three locations.
To enter a sparkline select the Insert tab on the ribbon and then select the type of sparkline.
To see the trend for Location 1, click the Line type. This displays the Create Sparkline dialog box.
Using either the keyboard or the mouse, enter the Data Range containing the cells to chart and the Location Range where you want the sparkline to be inserted and click the OK button. Follow the same procedure for the other two locations. You can also copy a sparkline by copying the cell containing the sparkline to a new location. Excel will relatively adjust the Data Range in the same manner as copying a formula.
A separate Sparkline Tools tab will be displayed on the ribbon whenever the cell cursor is on a cell containing a sparkline. This tab allows you to change various properties of the sparkline including type and color.
To see the relationship of each location within a quarter, create a second set of sparklines choosing Column as the type and rows 3:5 as the Data Range. The following screen capture shows the worksheet with all of the sparklines in place.
Note that the visual detail provide by the sparkline is dependent on the row height setting. Taller rows will make the changes/differences appear more dramatic.
Copyright 2014 – VBA Consultants Ltd