An interactive Multiple Row Bullet Graph in Excel displaying the gap and data labels only when hovering over a bar with the mouse
The previous post presented An Alternative Design of Bullet Graphs in different versions and provided the implementation in a Microsoft Excel template workbook for free download.
Long time Clearly and Simply readers and contributors Matt Grams (check out Matt’s guest post: Bullet Graphs for Excel: A Simple Way?) and Leonid Koyfman (Leonid's LinkedIn Profile) liked the alternative design in general, but also came up with a justified and constructive criticism regarding the Multiple Row Bullet Graph:
They noted that
- the chart is too busy and a bit overloaded, especially if you are using it in a relatively small size, e.g. on a dashboard with other views
- in certain edge cases, the data labels may overlap and become unreadable
Valid points. I fully agree with Matt and Leonid.
To make the chart less busy and more readable, Leonid suggested to turn it into an interactive Bullet Graph which displays the span of the gap (the thin line with arrows between actual bar and target line) and the data labels for gap and target only, if the user hovers over the bar with the mouse:
Please note the mouse cursor over the bar of Category 6.
The underlying technique and VBA code is described here: Mouse Driven Actions on Excel Dashboards.
This interactive version definitely looks much cleaner, but it also comes with a few disadvantages:
- The user has to activate the chart (click on the chart area) first to make the mouse hover action available
- The approach is not applicable for dashboards delivered in a static version like PDF or printed. It only makes sense, if the user directly interacts with the chart in Excel
- Unlike the original version, this solution requires VBA code
- Because of the VBA code, it takes more time to transfer the template from the example workbook to your own model. You can’t simply drag the sheet over to your file, you also have to copy the entire code from all objects, modules and class modules
- It requires Microsoft Excel 2013 or later
Anyway, it still is a viable and interesting alternative to the original version.
Here is the Excel 2013/2016 workbook for free download:
Download Interactive Bullet Chart (zipped Microsoft Excel 2013-2016 workbook, 32K)
Many thanks to Matt and Leonid for the feedback and suggestions. Much appreciated.
Stay tuned.