Get our FREE VBA eBook of the 30 most useful Excel VBA macros.

Automate Excel so that you can save time and stop doing the jobs a trained monkey could do.

Claim your free eBook

How to make cross filter visuals in Excel (amazing interactive visuals)

Cross filter visuals in Excel

Power BI has a really nice way of cross-filtering visuals. When you click on a visual, it highlights the selected item in other charts while also retaining the total value in a lighter color. Can we cross filter visuals in Excel? No. But we can create something pretty close.

Power BI Example:

Power BI Example of cross filtering

This post was inspired by a question I received:

With a slicer linked to a pivot chart, is there a way of highlighting the column but keeping all the data?

So, let’s see how we might achieve something similar in Excel.

Download the example file: Click the link below to download the example file used for this post:

Watch the video

Watch the video on YouTube

The Data

The data in this example is from a single Table called Data.

Data for cross filtering

The data is all fixed values, except the Include column that contains the following formula:

SUBTOTAL function used for Cross Filtering

As the formula is in an Excel Table, it automatically copies down into the rows below.

This formula is critical to the entire process. It currently calculates to 1 in every row because every row is visible. But if we filter the Table, the hidden rows recalculate to 0. This is the method we use to identify if a value should be displayed in the chart.

Find out more about this technique here: Using Slicers with dynamic array formulas in Excel

The Calculations

There are various calculations required to achieve the cross-filter effect.

We are using dynamic array formulas for this solution. You will need Excel 2021 or later to work with the example file. However, it’s not essential, and this solution can work without dynamic array formulas.

Cross filtering calculations

The calculations have a category column and two data series columns (Selected and Unselected). This isn’t a post about the formulas, so I will not go through these in any detail.

The formulas for the Item chart are as follows

Cell B5:


Cell C5:


Cell D5:


These calculations are then repeated for the equivalent Region chart:

Cell C15:


Cell D15:


Cell E15:


As a subsequent step, we could use HSTACK to put all the calculations into a single array. Charts linked to a single array expand automatically; therefore, that might reduce the amount of manual effort required to maintain the charts for new data.

Create the Charts

Using the Item, Selected, and Unselected columns, create a stacked bar chart for the Items calculations.

Insert stacked chart

Repeat the above for the Region calculations.

Then, cut and paste the charts into a new Presentation worksheet.

Initial charts created in Excel

We will come back and format these charts shortly.

Create the Slicers

Now, let’s create the slicers.

  • Select a cell in the Data Table.
  • Click Insert > Slicer from the ribbon
  • In the Insert Slicers dialog box, select Item and Region, then click OK.
    Insert Slicers Dialog

This creates 2 new slicers. Cut and paste the slicers to the same worksheet as the charts.

Slicers in the presentation page

The Formatting & Presentation

All the calculations and connections are set up. Clicking the slicers filters both charts. It’s now all about formatting and presentation.

Format the Charts

Repeat the following steps for both charts

  • Delete the Title
  • Delete the Legend
  • Remove the chart border
  • Select suitable colors for the bars
  • Format the category axis
    Format Axis for Chart
    • Check the Categories in reverse order
    • Set the horizontal axis to cross at maximum category
  • Format the Data Series by setting the Gap width to at least 50%
    Format Data Series
  • Add data labels to the Selected series.
  • Format the data label however you wish, but ensure the number format displays blank for zero.
    Custom Number format for chart data labels

With East and West selected in the region slicer and Alpha, Bravo and Charlie selected in the region slicer, the charts will look a bit like this.

Formatted charts no border slicers applied

Format the Slicer

Now it’s time to format the slicers.

  • Right-click a slicer and select Slicer Settings from the menu:
    Slicer settings
    • Uncheck Display header
    • Select Ascending (A to Z)
    • Uncheck Use Custom Lists when sorting
    • Uncheck Visually indicate items with no data
    • Ensure Hide items with no data is unchecked
    • Click OK
  • Select a slicer, then from the Slicer ribbon, right-click on a slicer style and click Duplicate
    Duplicate Slicer style option
  • In the Modify Slicer Style dialog box, modify the slicer style to fit your requirements.
    Modify Slicer Style
  • After creating the slicer style, apply the style to both the Item and Region slicers.

For our example, the slicers look like this:

Slicers formatted borders removed

I have chosen quite a dark fill color for the slicer, but you might choose a bold font or other formatting to indicate which items have been selected.


The final step is to lay out the slicer and chart so the slicer looks like the axis labels of the charts.

If necessary, resize the slicer buttons by selecting the slicer, then change the button height in the Slicer ribbon.

Change slicer button height

Now, resize the chart so the bars visually align with the slicer buttons.

Clicking the slicer buttons cross filters the other charts.

Cross filter visuals in Excel with slicers

BOOM! Cross-filtering visuals in Excel – Done!

Conclusion – cross filter visuals

While Excel does not have cross-filtering, we can create a similar effect using slicers and standard charts. The Include column is the key to making this technique work; this identifies which items have been selected.

Some other cross-filtering solutions use PivotTables and Power Pivot, so make sure to check those out too:

Related Posts:

Headshot Round

About the author

Hey, I’m Mark, and I run Excel Off The Grid.

My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.

In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).

Do you need help adapting this post to your needs?

I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.

But, if you're still struggling you should:

  1. Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
  2. Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
  3. Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise.  List all the things you've tried, and provide screenshots, code segments and example workbooks.
  4. Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.

What next?
Don't go yet, there is plenty more to learn on Excel Off The Grid.  Check out the latest posts:

Leave a Reply

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