Pivot tables - show items with no data but still filter

KatP

New Member
Joined
Sep 2, 2020
Messages
14
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!
 

Some videos you may like

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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.
 

KatP

New Member
Joined
Sep 2, 2020
Messages
14
Office Version
  1. 365
Platform
  1. Windows
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!
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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).
 

KatP

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

ADVERTISEMENT

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.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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: 2

KatP

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

ADVERTISEMENT

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
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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.
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,547
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,957
Messages
5,621,822
Members
415,859
Latest member
Vain

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
Top