Interesting PIVOT Table Dilemna

[jetsfan5]

Hi all-

I have what I think is an interesting 'issue' with my PIVOT table. It honestly might have a simple solution that I am overlooking, or a clever one, at this point, hard for me to tell.

I essentially have a file set up with two sheets (1) the data itself and (2) multiple PIVOT tables which are automatically refreshed with the click of a button. This is useful as the PIVOT table results need to be updated weekly (in the past, it was done manually each time).

My situation is with one of the PIVOT tables report age groups in bands (e.g., 0-5, 5-10, 10-15, 15-20, etc.). The problem is that not every time we run this report will every age group band have a data point that fits it. At present, that means that band will not show up in the output of the PIVOT table, so it might show 5-10 and then skip to 15-20. Naturally, I would still like every band to show, even if the count is 0 (i.e., I would like to preset a fixed list of bands that will be reflected in the PIVOT table every month, regardless of if a person fits in that band).

Any advice here would be GREATLY appreciated as this has been driving me nuts the last several weeks!!!

Best,
JF

[TMS]

As far as I know, a Pivot Table will reflect and analyse the data that is present, not what you would like it to imagine is present.

If you want all bands, you would probably need to have dummy entries for each band and reduce each count by 1. I think that might work, but it's just a guess.


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

What version of Excel are you using? Is 2102 meant to be 2021 or a variant of 365? Current versions are 2019, 2021 and 365.