How to implement a specific Highlight Action Use Case in Microsoft Excel
Highlight Actions in Tableau allow you to visualize all data points related to a user-selection across different views on a dashboard by coloring the relevant marks and shading off all others.
Highlight Actions are a great feature for interactive data exploration in general, but there is one specific use case where highlighting is extremely useful:
Imagine you have a data set with a hierarchy of dimensions like Product Category|Product Sub-Category|Product, State|County or something similar. If this is the case, chances are that the dashboard will show consolidated results per e.g. Product Sub-Category in a table or Bar Chart and a distribution of all Products by two measures (e.g. Sales and Profit) on an XY Scatter Chart. With a Highlighting Action in place, you can easily click on one Sub-Category in the first view and immediately see all related data (i.e. all Products belonging to this Sub-Category) on the XY Scatter Chart. Highlight Actions are preferable to Filter Actions in this case, because they show the members of the selected category within the context of the distribution of all data points.
Creating Highlight Actions in Tableau is a piece of cake. A few mouse clicks and you are good to go. But what if you want to have a similar feature on your Excel dashboard? Unfortunately, Excel does not provide actions across views natively, but with a few lines of VBA code and a simple charting trick, you can replicate Tableau’s Highlight Actions in Microsoft Excel, too.
We already had a couple of posts here providing similar functionalities with a comparable approach:
Bluffing Tableau Actions with Microsoft Excel
Highlighting on Excel Dashboards
Mouse Driven Actions on Excel Dashboards
Today’s post is along the same lines, but focusses on the specific use case described above. It explains how to create this particular Highlight Action in Excel and provides an example workbook for free download.
(more…)