Excel Dashboards and Reports: Fake a Sparkline Reference Line
Sparklines give you the ability to show data trends in your Excel dashboards and reports. One useful feature that’s missing in the Excel 2013 implementation of sparklines is a reference line. For example, it might be useful to show performance relative to a goal. If the goal is displayed as a reference line in a sparkline, the viewer can quickly see whether the performance for a period exceeded the goal.
One approach is to write formulas that transform the data and then use a sparkline axis as a fake reference line. This figure shows an example. Students have a monthly reading goal of 500 pages. The range of data shows the actual pages read, with sparklines in column H. The sparklines show the six-month page data, but it’s impossible to tell who exceeded the goal, and when they did it.
The lower set of sparklines in this figure shows another approach: Transforming the data such that meeting the goal is expressed as a 1, and failing to meet the goal is expressed as a -1. The following formula (in cell B18) transforms the original data:
This formula was copied to the other cells in B18:G25 range.
Using the transformed data, Win/Loss sparklines are used to visualize the results. This approach is better than the original, but it doesn’t convey any magnitude differences. For example, you cannot tell whether the student missed the goal by 1 page or by 500 pages.
This figure shows a better approach. Here, the original data is transformed by subtracting the goal from the pages read. The formula in cell B30 is:
This formula was copied to the other cells in the B30:G37 range, and a group of Line sparklines display the resulting values. This group has the Show Axis setting enabled, and also uses Negative Point markers so the negative values (failure to meet the goal) clearly stand out.