Pivot tables - show items with no data but still filter

KatP

New Member
Joined
Sep 2, 2020
Messages
17
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a pivot table that has division, area and score and a slicer to filter by month.
I want to filter by division and month but see all the areas within that division regardless of whether they have a score or not.

If i use the ‘show items with no data’ setting for the area. It shows me all the areas but I only want to see the areas under a particular division.

Any help is greatly appreciated!
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Put a dummy set of data, zeroes, of all areas and divisions, as the first rows of the data range ... with the real data following.
 
Upvote 0
Put a dummy set of data, zeroes, of all areas and divisions, as the first rows of the data range ... with the real data following.
Thanks Glen, unfortunately this wouldn’t be an option as this would need to be done every month for all areas not submitting data which would be alot of work. Hoping there is another way around this!
 
Upvote 0
No, it needs to be done once only ... as far as I can figure ... which is why I gave it as a solution (and why I said to put is at the start of the range ... so it's obvious it's fixed, and not to be replaced ever).
 
Upvote 0
But I want to filter by month and if an area is not entered that month then it will not appear. This would mean I would need to enter the areas with no scores every month.
 
Upvote 0
No, that is not the way it works ... here is a PivotTable with items missing for March, but the items exist elsewhere:
 

Attachments

  • PT_All_Items.JPG
    PT_All_Items.JPG
    26.6 KB · Views: 29
Upvote 0
No, that is not the way it works ... here is a PivotTable with items missing for March, but the items exist elsewhere:
But I want to be able to filter by division also to remove areas that are not in that division but keep the ones that have no data for that month
 
Upvote 0
But I want to be able to filter by division also to remove areas that are not in that division but keep the ones that have no data for that month
So, you still don't believe it will work. I mean ... to add the data and see what happens would take how long .... 5 minutes? Less than 5 minutes? Every time I say it will work, you say it won't, but don't actually test it out. This could go on ad infinitum.
 
Upvote 0
Make a key by concatenating division and area in a new column in the base data, and put it in Page area and filter multiselect, starting with division you want.
 
Upvote 0
Make a key by concatenating division and area in a new column in the base data, and put it in Page area and filter multiselect, starting with division you want.
I've been thinking about this .... and now I realise what you want, and that won't do it. I'll carry on thinking, and see if I can come up with a solution.
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

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