Occasional Visitor

Hello together, 

I have the following question about pivot-tabulars: I have four Attributes (A, B, C and D) and five answers (Very hard, hard, normal, easy, very easy). I want to show the numbers of the answers in a pivot-tabular, like in column H for the attribute A. The problem is, that Excel takes the answers of attribute A to the attributes B, C and D. (screenshot below).

If someone know, how I can fix this problem please let me know.



Thank you!

Best regards


2 Replies


You have a cross tabular table. In order to use a pivot table you have to unpivot your data.

Best way: Power Query.



Your data don't have the correct layout to serve as source for a pivot table. They should use a database-like layout:

Person Attribute Score
Person 1 A hard
Person 1 B normal
Person 1 C hard
Person 2 A normal
Person 2 B hard
Person 2 C very hard
Person 2 D hard
Person 3 A hard
Person 3 B easy
Person 3 C normal
Person 3 D easy
Person 4 B normal
Person 4 C hard
Person 4 D normal
Person 5 B hard
Person 5 C easy
Person 5 D normal
Person 6 A normal
Person 6 B easy
Person 6 C normal
Person 7 A normal
Person 7 C hard
Person 7 D normal
Person 8 A hard
Person 8 B easy
Person 8 C hard
Person 9 A very hard
Person 9 B easy
Person 9 D hard
Person 10 A normal
Person 10 B normal
Person 10 C normal
Person 10 D hard

A pivot table based on these data can have Score in the Rows area, Attribute in the Columns area, and Person in the Values area:


The pivot table looks like this (I used Tabular Layout):