Pivit Tables - Refresh Filters

L

Liddx08

New Member
Joined
Jul 14, 2004
Messages
1
I'm using an Excel spreadsheet as a template for a pivot table. The problem is, each time that I change the data in the Pivot table range, the filter selections keep the values from the prceeding data. After reusing this data range several times, this filter list gets quite large.

Is there any way to clear this after each time the data is changed?

Thanks
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Sort by date Sort by votes
Drop the field from the PivotTable by changing its name in the data list (e.g., Date to Date1), refreshing the PivotTable, changing the field name back to its original setting, refreshing the PivotTable, and then restoring the field name to its ROW, COLUMN or PAGE position.
 
Upvote 0
Seems this is quite a common problem facing pivot
table users.

Perhaps later Excel versions should do something about it
 
Upvote 0
It's not a problem -- it's a feature! PivotTables fields "remember" previously encountered domain values so that when it's "Show items with no data" option is checked you'll produce a PivotTable with fixed dimensions. This allows the construction of GETPIVOTDATA extracts that won't return #REF! when the current data set doesn't currently contain an given domain value. This concept is illustrated below...
Book1
ABCDEFGHI
1Field1Field2SumofField2
2TRUE10Field1Total
3TRUE20FALSE
4TRUE30TRUE150
5TRUE40GrandTotal150
6TRUE50
7
8
90
10
Sheet1
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question
Slicer disconnects from pivottable when programatically filtering items in pivot fields
Replies
2
Views
714
Telefonstolpe
T
T
  • Question
Pivot Table - Conditional Formatting (or selecting) from a Data Table
Replies
0
Views
548
Timbucktoo
T
N
  • Solved
VBA Update Pivot when cell value changes
Replies
3
Views
1K
NormChart55
N
C
  • Question
Creating a button to select from a Pivot table filter
Replies
1
Views
425
Micron
M
C
  • Question
COUNTIFS for Filtered Data, to Measure Date Ranges
Replies
2
Views
1K
Fluff
Fluff

Forum statistics

Threads
1,214,561
Messages
6,120,225
Members
448,951
Latest member
jennlynn

Share this page

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