One of the cool features of Power BI is the ability to cross filter and highlight charts. With a simple click on a column or line in one chart we can instantly highlight the selected item in all other charts and tables in our report.
In the animated image below you can see that when I click on a bar in the Gross Profit by Brand Name chart on the left, the chart on the right highlights a portion of the bars to reflect the selected brand.
Excel doesn’t have the same functionality built in, but we can achieve similar results with Slicers or Radio Buttons, or VBA. Let’s take a look at some non-VBA examples using visitor data for Hawaii.
Download the workbook
Download the workbook and follow along (it will be easier to understand if you do). The workbook also contains links to tutorials explaining how to build these interactive charts.
Enter your email address below to download the sample workbook.
Data Source: HTA and DBEDT
Excel Chart Highlighting – Overlapping PivotCharts
The PivotChart below highlights visitors for the destination selected in the Slicer (darker green bars) as a proportion of the total visitors (lighter green bars).
The PivotChart above actually consists of two PivotCharts, as you can see in the image below where I’ve moved the charts apart:
The first/bottom PivotChart is based on a PivotTable in columns A:C and displays the Total Visitors.
The second PivotChart sits above and is based on the PivotTable in columns E:G. It displays the visitors to the Destination selected in the Slicer.
The vertical axis for the second PivotChart is fixed to match the first PivotChart. The fill for this PivotChart area is set to 'None' so that the chart below shows through. Likewise, the vertical and horizontal axis fonts for the top PivotChart are set to 100% transparent.
The downside of this type of chart is the risk that the charts will not align perfectly when opened on other users’ PC’s and or other versions of Excel. Also, since the maximum for the second chart’s vertical axis is hard keyed, it runs the risk of being incorrect if the PivotTable source data changes.
For those reasons, this isn’t my preferred method.
Excel Chart Highlighting – Radio Buttons
In the image below I’ve used radio buttons to allow the user to select which region they want to highlight in the chart. You can see that O’ahu has been selected and both the row in the table and the line in the chart are highlighted in yellow:
Users can toggle through the different radio buttons to change focus. This is useful in line or scatter charts where there are many series.
Techniques Used
Radio Buttons - These allow the user to select which region to highlight in the chart. You’ll find radio buttons on the Developer tab > Insert > Form Controls. How to enable the Developer tab.
Cell F4 contains the radio button number that is selected.
This value is used by the Conditional Formatting for the yellow fill in the selected region in cells B5:E14 and the INDEX formula in column AD.
The table of values in C5:E14 is built using the GETPIVOTDATA function and references a PivotTable in columns AF:AH.
Highlight Selected Region’s Series in Chart - The selected region's line is highlighted in yellow. This is a duplicate series (called 'Selected) in the chart source data (contained in columns S:AD). This series sits on top of the original/selected region's line, which is why it is last in the list of Legend Entries in the Data Source dialog box below:
The INDEX formula in column AD of the chart source data (image below) contains the values for the 'Selected' series which changes based on the radio button that is selected.
Image above - Chart Source Data
Tip: Use the GETPIVOTDATA function to get the values from your PivotTable, that way you can still benefit from the PivotTable functionality, but aren’t limited to PivotCharts. Of course, you could use the SUMIFS function to build your chart data source table if you prefer.
Label Lines instead of Legend - Charts that contain lots of lines are easier to interpret if you can label each line.
The trick here is to add labels to the last point in the chart and set the label to the 'Series Name' and position it to the 'Right'.
Tip: To add a single label you must first select the line with a single left click, then left click the last point again to select just it, then add labels.
Add leader lines to your labels if you need to position the label slightly above/below the line.
Want more?
These examples combine various techniques to create an interactive experience for the user, and there are many more ways we can provide this and similar forms of interactivity.
In my Excel Dashboard course, I aim to equip you with lots of skills and ideas like those above, but also give you the confidence to try new things and experiment with Excel’s tools to create custom solutions to your needs.
If you want to explore what Power BI is capable of, then please consider my Power BI course. Or become a data visualisation master and can get both courses in a discounted bundle available from the Power BI course page.
