Pivot table displaying variables independently

Occasional Visitor

Dear Community,
I have a question about creating a pivot table in Excel:
When I insert multiple field names (names of the columns of the data table) as rows, they are always displayed depending on each other. As an example: If I have a dataset for 26 houses and in the first column is the color (red, green or blue) and in the second column is the year of construction and I create a pivot table with both properties as rows then it will display (depending on what is at the top) how many red, green or blue houses were built at which year of construction. However, what I want is for this information to be listed INDEPENDENT of each other. So just for each variable the different expressions and the number to which that applies (as it is shown in the picture!). Currently I have to create a separate pivot table for each category which makes it extremely confusing. Can someone help?

I'm using a Mac with the most current excel version.

Thanks and greetings,

6 Replies


You cannot do what you want in a single pivot table.


You could do a workaround and create separate pivot tables and use GETPIVOTDATA() to fetch the values and put them in the structure you need.


@Detlef Lewin 

Does it work on Mac ?

The support article states YES.

@Detlef Lewin , thank you. I'm not on Mac, nice to know it works now with data models.

It didn't mention data models but GETPIVOTDATA().