How to refresh drop-down list of pivot table

[Michael Pan]

We find that after a pivot table is created, the the drop-down list values of
filter in the table will not be refreshed as soon as the change of data.

E.g, orginally in the pivot table there is a value A in the column,
afterward in the data source value A is replaced by value B. But we can find
that value A is still in the list of drop-down values.

Is this a bug of Excel or there is some configuration of this? Is there any
fix?

 

[KC]

It is not a bug, I think it was made a manual effort for a purpose, imagine
if you had many pivottables and referencing large data range, and every time
you change and data in pivotable range all the related pivottables would have
to recalculate (not a good idea, waste of resources).

1) You can right click on the pivotable and click ‘refresh data’

2) One of the best ways could be to refresh the pivottable when you select
the worksheet which contains the pivotable. Right click on worksheet tab and
click select code.
Use the below code

Option Explicit
Private Sub Worksheet_Activate()

Dim ptTemp As PivotTable

For Each ptTemp In Me.PivotTables
ptTemp.RefreshTable
Next ptTemp

End Sub


Regards,
-kc
*Click YES if this helps

 

[Michael Pan]

KC, thanks for your reply. I had already tried to refresh the pivot table
manually. But the obsolete values are still there after refresh.

Another concern of mine is whether the in-time value refresh really costs
many system resources. If you filter on a column of a table, not pivot, the
values of the list can be most updated. I donn't see any difference.

Regards,

Michael

 

[KC]

I got what your saying

try this link http://contextures.com/xlPivot04.html
and let me know if this helps

-kc
*Click YES if this works