Why are separate pivot charts being filtered simultaneously?

BSWJON

Board Regular
Joined
Mar 24, 2014
Messages
109
Office Version
  1. 365
Platform
  1. Windows
I would appreciate your help with this conundrum.

I have a table, feeding 3 separate pivot tables. These in turn are summarised with separate pivot charts.

When I filter the axis categories using the button on graph 1, it filters graphs 2 and 3 also- even though they come from different pivot tables.

I'm not using connected slicers to filter the graphs (I've deleted them in an attempt to solve this issue).

How can I stop this from happening?
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Pivot tables usually use the same cache so when you use the same cache it filter all pivots.
You can fix that easily. Take a look at this video:
 
Upvote 0
The only way that I could replicate that behaviour was for the chart to be using the same Pivot Table.
Unfortunately the Pivot Chart only show the underlying data source and not the table it is based on.
Try runnning the slightly modified code below
sourced from:

If your charts are on multiple sheets uncomment my "set ws" line and reinstate the "For Each ws" & "Next ws" lines.

What you are looking to see is if the charts are using the same Pivot Table name which will be the first item in the message box.

VBA Code:
Sub GetPivotChartSources()
Dim ws As Excel.Worksheet
Dim chtObject As Excel.ChartObject
Dim cht As Excel.Chart
Dim pvt As Excel.PivotTable

Set ws = ActiveSheet
'For Each ws In ActiveWorkbook.Worksheets
    For Each chtObject In ws.ChartObjects
        Set cht = chtObject.Chart
        If Not cht.PivotLayout Is Nothing Then
            Set pvt = cht.PivotLayout.PivotTable
            'activate the sheet the pivot is on
            pvt.Parent.Activate
            pvt.TableRange2.Cells(1).Select
            MsgBox pvt.Name & vbCrLf & " is on " & pvt.Parent.Name & " using data from " & pvt.SourceData
        End If
    Next chtObject
'Next ws
End Sub
 
Upvote 0
Hi both,

Thanks for your responses.

Unfortunately neither solution worked- so I ended up deleting the pivot tables and starting again. No issues the second time around.

An annoying problem and it would have been nice to have found a more elegant fix than deleting everything but hey ho.
 
Upvote 0
Thanks for letting us know. Sorry we couldn't save from having to recreate the pivot tables.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top