Highlight Actions in Microsoft Excel with Multiselecting

How to implement Highlight Actions in Microsoft Excel with the option to select more than one category (Multiselecting)

Multiselect Highlight Actions Excel IntroClearly and Simply proudly presents another guest article: Leonid Koyfman, long-time contributor to this blog, enhances the approach to Highlight Actions in Microsoft Excel with the option to select more than one category. Leonid developed an impressively simple and elegant technique. Enjoy.

The recent post Highlight Actions in Microsoft Excel provided a mechanism to replicate Tableau’s highlight actions in Microsoft Excel and focused on the specific use case when a selection of a row within the data table, or a bar of the “bar charts”, automatically highlighted related products on the XY scatter chart and shaded off all other data points.

Getting closer to what can be done in Tableau, the suggested approach lacks an important Tableau functionality: multi-selection. In the Highlight Action Example in Tableau we are not limited to a single item selection. We can select multiple Sub-Categories with a mouse or CTRL-Click and this action will highlight the corresponding data points on the XY scatter chart.

Today’s post includes a set of techniques and tricks to extend the dashboard presented in Highlight Actions in Microsoft Excel to mimic Tableau’s multiselecting feature.

Excel Dashboard with Multiselection Highlight Action

The Excel dashboard with a multiselection highlight action could look like this:

Multiselect Highlight Actions Excel (Dashboard) - click to enlarge

To select multiple Sub-Categories we can use Ctrl+Click or Shift+Click or make the selection with a mouse.

The Highlight Actions in Microsoft Excel post provides details on how the initial dashboard was set up, so I’ll focus on what is different in the new version.

  • The VBA routine Worksheet_SelectionChange assigns to every unique row in the selection a value of 2 to the power of the row number, accumulates these values, and writes the result to the cell “myIndex.”. This way the accumulated value will be unique for any combination of the selected Sub-Categories. If nothing is selected, “myIndex” is set to zero.
  • The table [tab_bar_chart] on the Calculations sheet has a new column Flag whose values are calculated by formula:     
         
    =BITAND(2^(ROW()-MIN(ROW(tab_bar_chart))),myIndex)

    where ROW()-MIN(ROW(tab_bar_chart) is a row number of the current record in the table [tab_bar_chart]

    BITAND function was added in Excel 2013. It returns a bitwise ‘AND’ of two numbers.

    The formula checks if a row number of the current record in the table [tab_bar_chart] participates in the accumulated value stored in the “myIndex” cell and returns a value of 2 to the power of that row number or 0.

    For example, when Furnishing, Storage, Bookcases and Art Sub-Categories are selected on the dashboard, the Calculation sheet with the table [tab_bar_chart] and the cell “myIndex” looks like this:Multiselect Highlight Actions Excel (Calculations) - click to enlargeThe value of 646 in the “myIndex” cell is equal to 2+4+128+512 and the corresponding non-zero values in the Flag column indicate the selected Sub-Categories.

    For Excel versions prior to Excel 2013 a user-defined function with the code below should do the trick:

    Public Function BITAND(x As Long, y As Long)
        BITAND = x And y
    End Function
        

  • Table [tab_xy_scatter_data] on the Calculations sheet has a new column Flag whose values are calculated as

    =INDEX(tab_bar_chart[Flag],
    MATCH([@[Product Sub-Category]],tab_bar_chart[Product Sub-Category],0))

    The formula looks up the Flag value from the table [tab_bar_chart] for a current Sub-Category. Values in this column are used in the logical tests in the formulas for scatter-chart data series on the Calculations sheet:

    Sales highlighted =IF([@Flag],[@Sales],NA())

    Profit highlighted =IF([@Flag],[@Profit],NA())

    Sales shaded off =IF(NOT([@Flag]),[@Sales],NA())
         
    Profit shaded off =IF(NOT([@Flag]),[@Profit],NA())
        

  • The value in the cell Calculations!C7 is now not a single Sub-Category, but a comma delimited list of selected Sub-Categories. It’s handled by an array formula (entered by pressing CTRL-Shift-Enter):

    {=TEXTJOIN(“, “,1,
    REPT(tab_bar_chart[Product Sub-Category],
    SIGN(tab_bar_chart[Flag])))}      
         
    The TEXTJOIN function was added in Excel 2016. It combines the text from multiple ranges and/or strings, and includes a specified delimiter between each text value to be combined. 

    Please be advised that TEXTJOIN is not available in all versions of Excel 2016. The function is included in Office 365 subscriptions and Office Professional Plus 2016, but not in e.g. Office Professional 2016. Since the result of Calculations!C7 is only used to display a title above the chart, this counts for little and the formula is simply wrapped up in an IFERROR-statement to avoid ugly #NAME errors. If you are using a version not including TEXTJOIN, you could still use a helper column in the table concatenating the selected sub-categories or write your own User Defined Function in VBA.
         

  • The rule for conditional formatting in the Dashboard is based on a logical test of values in the corresponding rows of the Flag column in the table [tab_bar_chart].

If you are interested in the details, please download the workbook (see download link below) and leave a comment if you have any questions. The workbook and VBA code is open without password protection.

Download Link

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

Download Excel Highlight Actions Multiselect (zipped Excel 2013-2016 workbook, 234K)

Robert’s Remark

Thank you very much Leonid for the great idea, this fantastic guest article and for investing so much of your precious time into your invaluable contributions to this blog. Greatly appreciated.

Stay tuned.

Add a Comment

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