Highlight Actions in Microsoft Excel

How to implement a specific Highlight Action Use Case in Microsoft Excel

Highlight Actions in ExcelHighlight 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.

Highlight Actions in Tableau

Let’s have a look at the role model first: Highlight Actions in Tableau.

Here is a very simple Tableau Public visualization of Products by Sales and Profit (data source: an extract from Tableau’s Superstore Sample database). The dashboard shows the sum of Sales and Profit for all Product Sub-Categories as Bar Charts at the top and the distribution of all Products by Sales and Profit on an XY Scatter Chart:

The visualization provides only one simple, but very helpful Highlight Action: if you click on a Product Category in the upper view (one of the bars or a row header), all related data points (i.e. all products belonging to this sub-category) will be highlighted in the XY Scatter Chart. To reset the selection, either press ESC or click on the selected sub-category again or click somewhere outside of the first view.

Highlight Actions in Microsoft Excel

Unlike Tableau, Excel does not provide a built-in feature to create Highlight Actions. However, with a simple charting trick and a few lines of VBA code, you can replicate Tableau’s Highlight Actions in Excel, too.

An example Excel dashboard could look like this:

Highlight Actions in Excel 1 - click to enlargeThe dashboard uses a horizontal layout (instead of a vertical one as in the Tableau visualization above), but the data and the views are exactly the same.

Now, here is the Highlight Action functionality: if you click into a row of the data table or on a bar of the “bar charts” on the left, the related products of this sub-category will automatically be highlighted on the XY scatter chart and all other data points will be shaded off:

Highlight Actions in Excel 2 - click to enlarge

Highlight Actions in Excel 3 - click to enlargeYou can select a sub-category by directly clicking into a cell of the left view or flip through the rows with the up and down arrow on your keyboard or by pressing ENTER or Shift-ENTER. To undo the selection, click somewhere outside of the left view or simply press ESC.

The Implementation of Highlight Actions in Microsoft Excel

I will not provide a detailed step-by-step tutorial how to implement the feature in Excel, but here are at least the most important cornerstones of the approach:

  • The view on the left side of the dashboard is a simple worksheet range displaying the list of sub-categories and the aggregated numbers (count of products, sums of sales and profit) which are consolidated on sheet [Calculations] using SUMIF and COUNTIF formulas
  • The “Bar Charts” inside this range are no real Excel charts. They are created using the Conditional Formatting Data Bars option
  • The highlighting of the selected sub-category in the left view with a green fill color is done by simple Conditional Formatting, too
  • The view on the right side of the dashboard is a standard Excel XY Scatter Chart. It includes three different data series, though: one for all the data, one for the highlighted data and one for the data points which are shaded off, if a sub-category is selected
  • The chart data series are prepared on sheet [Calculations] by simple IF, AND and NA() functions based on the sub-category of the data row and the selected sub-category
  • The heart of the solution is a cell on worksheet [Control] named “myIndex”. This cell is managed by the VBA code and contains the index of the selected sub-category respectively zero (if nothing is selected)
  • Another named range (“myTable”) defines the “clickable” area on the dashboard, i.e. the entire range of the left view
  • The approach takes advantage of the event-driven VBA routine Worksheet_SelectionChange, which is fired every time the user changes the selected cell or range by clicking on another cell or using the keyboard (arrow-keys, ENTER, TAB, etc.). The code then checks whether the activated cell is inside the range “myTable” and if so, writes the relative index of this row to the cell “myIndex”. Otherwise, it sets “myIndex” to zero
  • A few other event driven routines (Workbook_Open, Workbook_Close, Worksheet_Activate, etc.) enable the usage of ESC to undo the selection. If you do not need the option of deselecting by ESC, you can delete everything from the code except for the Worksheet_SelectionChange sub

So much for a short overview of the implementation. The workbook and VBA code is open without password protection. If you are interested in the details, please download the workbook (download link see below) and have a look for yourself. If you have any questions, please leave me a comment.

Download Link

Here is the Highlight Actions in Excel example workbook for free download:

Download Excel Highlight Actions (zipped Microsoft Excel 2010 – 2016 workbook, 203K)

Stay tuned.

Add a Comment

Your email address will not be published. Required fields are marked *