Excel 365 Pivot Tables: Based on Data with some records excluded

Chris The Rock

Active Member
Joined
Feb 24, 2002
Messages
287
Before I begin:
I'm not a novice by any means, but I find myself lacking in this particular area of expertise. And because of that, I don't know exactly how to succinctly ask this question in Google or on this message board. There's a good chance it's been answered - or that the answer is deceptively simple. That's what I'm hoping.

I have produced a pivot table report at work, and the results are displaying perfectly. There's one catch, though:

Using a Report-Level field as a filter, I have excluded some records from the results based on the "STATUS" field, for which I've applied a filter so only "Active" ACCOUNT records are part of the results.

However, the items that are excluded from the results still show up when the user clicks the Column Header in the ACCOUNT field. If the user clicks them, they'll get no results - as designed. This is confusing for the user, who is expecting those items not to not be part of the data to begin with. And when I say "user", of course I mean "my manager". Suggesting that my manager simply ignore those items isn't a suitable solution.

This seems to work differently than in a standard Excel Table, where when filters are applied to one field, the list of choices for filtering in the other fields are affected by the other filters in place.

So I'm looking for a way to base my pivot table on a filtered list of data, that has all items with the STATUS field not equal to "ACTIVE" already excluded from the list, or some other option to make the filtering work in a similar fashion to Excel Tables.

Before I begin a journey into learning about Power Pivot and the Excel Data Model (which very well could hold the answers for me), I want to consult with some experts here to see if I've simply missed an option, or if there's a trick to doing this that is simple to execute.

And if it is in fact done within the Data Model, any hints would be helpful. I literally have anther tab open to chandoo.org right now looking at an article on the Excel 2013 data model (hoping that it is still valid for Excel 365).

Thank you to anyone who can help, even if it's just pointing me to another post or article where this issue is addressed. I'm simply unable to find anything with my feeble question-asking skills.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

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