Toggle Chart Using An Excel Slicer

The following chart is the result of the blog post.  It displays a chart based on the selection from a slicer.  It is run by a worksheet change event which toggles between a Stacked Bar chart and a 2D Column chart. The trick is to create two charts one for each of the regions and one for the grand total or all regions. That way you can toggle between region and grand total.  The following is what the grand total (All) looks like:

Excel VBA Slicer Selection

Filter Chart Series

The attached Excel VBA example shows how to toggle the stacked bar chart show to a regular 2D chart.  


While the following VBA procedure helps run the process.  It is a toggle between the two charts depending on what Slicer selection is made.  The All choice produces the consolidation where as the region choice produces just a column chart.


VBA Procedure Slicer

The following needs to be placed into the sheet module which contains the pivot table.

Option Explicit
Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Dim slItem As SlicerItem

With ActiveWorkbook.SlicerCaches("Slicer_Data")
For Each slItem In .VisibleSlicerItems
If slItem.Name = "All" Then
ActiveSheet.Shapes("Chart 1").ZOrder msoSendToBack
Else
ActiveSheet.Shapes("Chart 2").ZOrder msoSendToBack
End If
End With
End Sub

Dynamic Chart1

The above is the sheet with the pivot table (Sheet3) is the worksheet code name and Piv is the sheet name.

While the following shows a the region isolated in the Slicer and the chart reflecting the numbers for the region chosen in the slicer.

Toggle Chart Excel

The following Excel file highlights the technique, with the above practical example.