Pivot Tables have a Memory/Cache??

[daveyc18]

so, my original pivot table's data source HAD book names like "ECID1," ECID2," etc and was on sheet1.

now, I'm using an advanced filter to filter these guys out (ie "ECID1", "ECID2," etc being filtered out) from the ORIGINAL source data, then pasting the info on sheet1.

as expected, when I look at the pivot table, it doesn't include the books in the "row labels" of the Pivot. however, when I click the arrow down button for filtering the "row labels," i see still those books I excluded ("ECID1", etc are checked off).

not that this is causing issues, but it's tripping me out that pivot table shows those book names I excluded in the "row labels" filter (but not in the actual pivot table) even though the pivot source doesn't have those book names. does the pivot table filters have some sort of cache or memory? only way to clear is to re-create the pivot table?

 

[sheetspread]

Filtering doesn't delete the source, just hides it from view and certain calculations. Even if it was deleted the cache wouldn't change until you pressed "refresh" in the pivot table options ribbon.

 

[daveyc18]

Filtering doesn't delete the source, just hides it from view and certain calculations. Even if it was deleted the cache wouldn't change until you pressed "refresh" in the pivot table options ribbon.

i refreshed the pivots....as mentioned, pivot table's source data no longers has those books cause I excluded it from the ORIGINAL source file to begin with.


sequence:

-macro opens up original source data....uses advanced filtering. to exclude those books (ECID1, ECID2 , etc.)....then pastes to sheet1...so the data on sheet1 doesn't have those books

-refresh the pivots whose source data is sheet1 ....i dont see those books in the row labels, but for whatever reason, i still see "ECID1", etc. checked off even tho sheet1 doesnt have those books

but i do apologize for missing a major point....before this process....I was NOT doing the advanced filtering and was manually excluding those books by using the row labels filter in the Pivot table....so that's why I thought perhaps pivot table filters have some sort of cache/memory? cause even tho the new data doesn't have those books, its still showing up in the filter list.

 

[sheetspread]

Go to the leftmost side of the pivot table options ribbon, click options from the dropdown, choose the data tab, under Number of items to retain per field select None, then refresh, you won't see the removed choices in the filter.

 

[daveyc18]

Go to the leftmost side of the pivot table options ribbon, click options from the dropdown, choose the data tab, under Number of items to retain per field select None, then refresh, you won't see the removed choices in the filter.

awesome dude...wasnt affecting the numbers, but was bugging the hell out of me....learn something new every day! thanks!