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
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:
=IF(trigger="north",north,IF(trigger="south",south,IF(trigger="east",east,west)))
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:
=INDIRECT(trigger)
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
Use Slicer Selection in Formulas
Thanks
A big thank you to Bob Abrams for the IF function technique to select named ranges.
Please Share
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.
Paul George says
Mynda,
Today I read your article on “Using Slicers doesn’t have to be limited to PivotTables and Pivot Charts.” In looking at the chart I noticed when a region was not selected the chart reflected the sales for the north region. My thought was it would be great for the chart to show a total for all of the regions when a region had not been selected on the slicer. So in trying to make this change it helped me to better understand how this was developed. It took me a few attempts but I finally figured it out. I add a “All Regions” column to the right of the West column and named the range. I changed the formula for the Dynamic Chart Label to =IF(COUNTA(J6:J9)=4,”All Regions Sales 2015″,Sheet1!$J$6&” Sales 2015″). And I changed the name range for chart_rng to begin with =IF(COUNTA(Sheet1!$J$6:$J$9)=4,All_Regions,IF(trigger=”north”,….). I am uncertain if it is the most efficient way for the total for all regions to appear when a selection has not been made on the slicer, but it works.
Thank you for your easy to follow instructions.
Mynda Treacy says
Nice touch, Paul. Thanks for sharing.
Mynda
Patrick Kok says
Hi Mynda,
I’ve learn new things today and would like to know how should I do if I’ve a stack chart for example if I wanted to stack each region with 3 product like below;
North_ProductA, North_ProductB, North_ProductC,
South_ProductA, South_ProductB, South_ProductC,
East_ProductA, East_ProductB, East_ProductC,
West_ProductA, West_ProductB, West_ProductC,
Thanks you for your guidance.
Have a nice day!
Mynda Treacy says
Hi Patrick,
Great to hear you picked up a few ideas from this post.
I would avoid stacked charts as they make comparisons of all bar the bottom series difficult to compare because the subsequent series don’t start at the same point. Instead I recommend using panel charts as you can see in this tutorial.
You can imagine your data either arranged with a chart for each product and the region on the horizontal axis, or a chart for each region with the product on the horizontal axis.
I hope that helps.
Mynda
Patrick Kok says
Hi Mynda,
Thanks you for your reply actually I’m using your ideal in this interactive chart to present it in such a way that I’ve 5 different manufacturing lines and each lines produce different product with different head count but I’m face some issue with the dynamic range for the chart.
I’ll post it in the forum with the file so that some guru could help me out.
Thanks you for your time and really appreciate it.
Best Regards,
Patrick
Dana says
Hey Mynda,
Timing on this is fantastic – just what I need to complete a project! And your directions are always so clear and concise. Thank you for that!
I only have one problem – the dynamic chart title will not accept a named range. The error message is not specific but lists things to check, i.e. using quotes. What am I missing?
Thanks again.
Mynda Treacy says
Hi Dana,
Great to hear it’s been helpful.
Chart titles cannot accept a named range. Your options are to put your formula in a cell and link to that cell, or use a Shape or Text Box as your Chart Title and link the Shape to your named range.
Let me know if you get stuck.
Mynda
Steve H says
Hi, I am trying to replicate your slicer interactive chart and I cannot get Step 6 to work for me. I have followed to the letter as per your instructions but when I try and change the series value to the dynamic names range that contains the IF trigger formula Excel says that “we found a problem with formula references”…. can you assist ? I can send you my workbook that I am using if that helps.
Steve
Mynda Treacy says
Hi Steve,
Yes, please raise a question on our Excel Forum and upload your workbook so we can see where things went wrong.
Cheers,
Mynda
Mukul says
Hi Steve
was this problem fixed, im having the same problem.
Mynda Treacy says
Hi Mukul,
Steve’s formula was wrong so it may not be the same problem as yours. Please raise your question on our Excel Forum and include your Excel workbook so we can see where the issue is.
Thanks,
Mynda
David Wilkinson says
Thanks! Your lessons are always completely organized.
Mynda Treacy says
Cheers, David 🙂