Suppress Display of Old Data in Excel Pivot Table 2

[chouna]

I have a pivot table that picks up the quarterly sales from an external file. However, I update that file to include only the months for the respective quarter. E.g. If we are in quarter 2, the file will only contain Apr, May, Jun data.

The problem I am facing is that the pivot table “remembers” the previous months. I have the Months as my column and whenever I click on the column to filter, it will show me months Jan to Jun although it will not have data for Jan – Apr. How do I suppress this display of previous entries?

 

[gizmo1973]

Delete the rows from the source data is probably the easiest route.
If you need to keep them for your records then change the data selection are in the Pivot wizard and then refresh either way

Regards, Phil

"If in doubt, hit it with an end user!

 

[chouna]

The source data does not have them. I started out with quarter 1 which had data for Jan-Mar. Then the whole file has been zapped and replaced by data for quarter 2 (Apr-Jun). But it seems that Excel still remembers I had data for Jan-Mar and retains those months in the column's criteria (only for display). Although it does not show any data for these months, it can confuse users.

 

[gizmo1973]

Try running the Wizard to force it to see what is physically there and not what was there

Regards, Phil

"If in doubt, hit it with an end user!

 

[xlbo]

If you have XP, run this code ONCE for each sheet with a pivottable that you want to "clean"

Sub Clean_Pivots()
for each pt in activesheet.pivottables
 pt.pivotcache.missingitemslimit = xlmissingitemsnone
next
end sub

This sets the "MissingItemsLimit" (see help for more detail) which then remains set until it is changed manually (by code) - this effectively stops historical data from being shown in a pivottable

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question

 

[ETID]

if not xp then...

a manual way is to do the following...

in your source data, rename the offending column > refresh the pivot > restore the old column name in the source data > refresh the pivot again.

 

[chouna]

Geoff,

Is there anything you can't do in Excel (beside giving it out for free?)

That was really helpful but honestly, I think this should not be done in VB. Why pvt table does not reset the number of items confounds me. Thank you very much for this tip.

 

[xlbo]

chouna - yes I agree - it is a very irritating "feature" of XP. I can understand why it might be there but to not have any manual options for it seems silly.
and yes - there is plenty that I cannot do - I learn from these fora on a daily basis !

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question