Pivot table - text data does not count correctly

HelenL

New Member
Joined
Nov 17, 2008
Messages
30
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
ABCDEFGH
1DirectorateQuestion 1Question2Question 3
2constructionYesYesNo
3marketingNoNoYes
4salesYesYesYes
5FinanceNoNoYes
6OtherNoNoNo
7
8
9
10
11
12Directorate(All)
13
14Row LabelsCount of Directorate
15No2
16Yes2
17Grand Total4
18
19
20
21
22unable to get the other questions - 3 questions in total to list in the same manner
23
24
SampleData
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,051
Office Version
  1. 2016
Platform
  1. Windows
I think you have to rebuild your table like this:

Map5
ABCDEFGHIJK
1DirectorateQuestionAnswerDirectorate(Alle)
2construction1Yes
3construction2YesAantal van DirectorateKolomlabels
4construction3NoRijlabelsNoYesEindtotaal
5marketing1No1325
6marketing2No2325
7marketing3Yes3235
8sales1YesEindtotaal8715
9sales2Yes
10sales3Yes
11Finance1No
12Finance2No
13Finance3Yes
14Other1No
15Other2No
16Other3No
Blad1
 
Joined
Feb 8, 2002
Messages
3,027
Office Version
  1. 365
Platform
  1. Windows
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.
 
Solution

HelenL

New Member
Joined
Nov 17, 2008
Messages
30
I think you have to rebuild your table like this:

Map5
ABCDEFGHIJK
1DirectorateQuestionAnswerDirectorate(Alle)
2construction1Yes
3construction2YesAantal van DirectorateKolomlabels
4construction3NoRijlabelsNoYesEindtotaal
5marketing1No1325
6marketing2No2325
7marketing3Yes3235
8sales1YesEindtotaal8715
9sales2Yes
10sales3Yes
11Finance1No
12Finance2No
13Finance3Yes
14Other1No
15Other2No
16Other3No
Blad1
Many thanks you for your help
 

HelenL

New Member
Joined
Nov 17, 2008
Messages
30
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,143
Messages
5,622,977
Members
415,943
Latest member
JakeG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top