Pivot Table Refresh

J

JMay

If I Apply an auto-filter to the data-source of my Pivot Table
It does not effect to refreshed results of my PT.

Can someone confirm this -- that is from a Pivot table data-sources
records - any hidden rows (from an auto-filter) do not get omitted.
The PT uses ALL record, Hidden or not..
TIA,
 
S

Shane Devenshire

Hi,

That is the expected behavior. If you hide the rows of the data souce using
any technique, the pivot table ignores that, it uses all the data in the data
area.

1. You can add a dummy column to the data source with formulas such as this
=SUBTOTAL(1,C2)
2. In this case C2 is a numberical cell in your data. Copy the formula down.
3. Apply a filter to the data sourse just like you normally do. Although
you can't see it the formulas on the hidden rows return #Div/0
4. Change the pivot table source range to include the new column, the one
with the formula
5. Place the new field in the Page (Report) area
6. Display all the entries in the page area except the Div/0.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Pivot Refresh Error when calling Excel Macro from VB.NET program 0
Pivot Table Page Break Refresh Problem 0
Pivot Table Refresh not working properly 1
Pivot Table Refresh? 1
date filter in a pivot table 1
How do I save Pivot Table Data 4
Pivot ungrouping on refresh? 3
Keep the word (blank) out of Pivot Table? 2

Top