Pivot table, show empty rows

**Hein** · 02-16-2009, 10:41 AM

Hi all,

In my Pivot table I have 3 fields in the "header - section" of each row
There is also the possibilty in the Page section to choose between subjects (eg physics, chemistry, biology etc)
When all rows are displayed there are 68 in total

When I choose Physics there are about 30 customers that have a value in 1 of the rows.
Excel shows 30 rows, but I would like all 68 row to be shown, because these are the values that are important to me.

I have tried a lot of settings in the pivot table but can't find the correct 1.

What happens a lot is the the rows are "multiplied", meaning that the 1st row header has every combination of the 2nd and 3rd and so one

Which setting is needed to get what I want?

Thanks for your help
Hein

PS
I've tried the field settings in both the columns as the rows
This works for the columns, so far not for the rows

**Cheeky Charlie** · 02-16-2009, 11:20 AM

Hi Hein,

If you could post what you have, and what you'd like to see, we can be sure to answer the right question, and to show it in action on your own example, as appropriate.

CC

**Hein** · 02-17-2009, 04:23 AM

Hi CC

Thanks for your reply
I have made a demo that hopefully sheds more light on the matter

Hein

**Cheeky Charlie** · 02-17-2009, 05:22 AM

Hi,

I see the issue is to do with the way you are, in a way, using the pivot table for vlookups as well as data consolidation - this means "show items without data" accounts for the entire potential items, not just the existing combinations (logically, how would Excel know the existing combinations to express?)

To get around this, how about something as attached?

HTH

PS There may well be different and/or better ways of doing this...