Pivot table - text data does not count correctly

[HelenL]

Pivot table
1. Text data entered into 3 columns does not count up correctly displayed in the pivot. (see sample date please)
2. Unable to identify on results which question is which from the column labelling (See Pivot tab please)
3. How to filter to get a count for a particular Directorate as a percentage of the total responses

I have tried very hard with this, but did not succeed.
Help very much appreciated. Thank you.

Excel pivot problem Mr Excel.xlsx
	A	B	C	D	E	F	G	H
1	Directorate	Question 1	Question2	Question 3
2	construction	Yes	Yes	No
3	marketing	No	No	Yes
4	sales	Yes	Yes	Yes
5	Finance	No	No	Yes
6	Other	No	No	No
7
8
9
10
11
12	Directorate	(All)
13
14	Row Labels	Count of Directorate
15	No	2
16	Yes	2
17	Grand Total	4
18
19
20
21
22	unable to get the other questions - 3 questions in total to list in the same manner
23
24
SampleData

 

[mart37]

I think you have to rebuild your table like this:

Map5
	A	B	C	D	E	F	G	H	I	J	K
1	Directorate	Question	Answer				Directorate	(Alle)
2	construction	1	Yes
3	construction	2	Yes				Aantal van Directorate	Kolomlabels
4	construction	3	No				Rijlabels	No	Yes	Eindtotaal
5	marketing	1	No				1	3	2	5
6	marketing	2	No				2	3	2	5
7	marketing	3	Yes				3	2	3	5
8	sales	1	Yes				Eindtotaal	8	7	15
9	sales	2	Yes
10	sales	3	Yes
11	Finance	1	No
12	Finance	2	No
13	Finance	3	Yes
14	Other	1	No
15	Other	2	No
16	Other	3	No
Blad1

 

[MrExcel]

Hello @HelenL

@mart37 is correct, but you can do this in memory using the Transform tools on the Data tab of the Ribbon. Here is a short two-minute video showing the process.

 

[mart37]

@MrExcel I can't play the video. But a found this on internet: unpivot table. Thanks fo the tip.

 

[HelenL]

I think you have to rebuild your table like this:

Map5
	A	B	C	D	E	F	G	H	I	J	K
1	Directorate	Question	Answer				Directorate	(Alle)
2	construction	1	Yes
3	construction	2	Yes				Aantal van Directorate	Kolomlabels
4	construction	3	No				Rijlabels	No	Yes	Eindtotaal
5	marketing	1	No				1	3	2	5
6	marketing	2	No				2	3	2	5
7	marketing	3	Yes				3	2	3	5
8	sales	1	Yes				Eindtotaal	8	7	15
9	sales	2	Yes
10	sales	3	Yes
11	Finance	1	No
12	Finance	2	No
13	Finance	3	Yes
14	Other	1	No
15	Other	2	No
16	Other	3	No
Blad1

Many thanks you for your help

 

[HelenL]

Hello @HelenL

@mart37 is correct, but you can do this in memory using the Transform tools on the Data tab of the Ribbon. Here is a short two-minute video showing the process.

That's amazing, thank you so much, the video was invaluable for me to follow your steps correctly.