Slicers are a great tool for easily filtering PivotTables and Pivot Charts, but what if you want to use a Slicer to control a regular chart like this:
Note: This technique applies to Excel 2010 onwards.
Download the Workbook
Enter your email address below to download the sample workbook.
Creating Slicer Controlled Interactive Excel Charts
The process to set up Slicer controlled interactive Excel charts is easier than you might think, so don’t be put off by the number of steps involved.
Step 1: Get some data and give each column a named range. Here is mine:
Step 2: In a separate column create a list of your named ranges. I’ve formatted mine in an Excel Table in cells G5:G9 called TableRegions:
Step 3: Insert a PivotTable and use the table from step 2 as your source data. Put the ‘Region’ field in the rows area of the PivotTable (note: your PivotTable will list all regions when you first create it):
Tip 1: Remove the Grand Totals from the PivotTable. You don’t need them.
Tip 2: give the first cell in your PivotTable (below the header) a named range. Mine is cell I6 and it’s called ‘trigger’. This just makes it easier to reference later on.
Step 4: Insert a Slicer for the Region field:
Tip: Change the Slicer caption to something useful: right-click the Slicer > Slicer Settings:
Step 5: Create a Dynamic Named Range that picks up the region from the PivotTable that is selected in the Slicer, and returns the appropriate range from the Chart Source Data (as shown in step 1). Mine is called ‘chart_rng’ and you can see it in the Name Manager below:
If you’re familiar with dynamic named ranges then this one might look a little different to what you’re used to. Let’s take a closer look at the formula:
Yes, we’re using a nested IF Formula to return a range. The IF function can return a range as opposed to just a single value like you’re probably used to.
Tip: Take particular note of the use of double quotes in the IF formula above.
In English the formula reads:
IF the value in the cell called trigger is north, then return the named range ‘north’, else IF the value in the cell called trigger is south, then return the named range ‘south’, else IF the value in the cell called trigger is east, return the named range ‘east’, else return the named range ‘west’ since there’s no other ranges it could be.
A simpler approach would be to use the INDIRECT function like so:
But beware of the volatility of the INDIRECT function.
Step 6: Insert your chart. Create a regular chart for just one series, e.g. North. Then (1) edit the chart source (right-click the chart > select data) and (2) replace the cell ranges in the ‘Series Values’ with (3) your dynamic named range.
Tip: in the ‘Series Values’ field make sure you keep the sheet name/workbook name part of the reference and only replace the cell references with your named range.
Step 7: Insert a dynamic chart title that changes based on the region selected in the Slicer
Step 8: Apply any formatting to your chart and then merrily click your Slicer to toggle through the different views.
Other cool Slicer Tricks
A big thank you to Bob Abrams for the IF function technique to select named ranges.
If you liked this or know someone who could use it please click the buttons below to share it with your friends on LinkedIn, Google+, Facebook and Twitter.