Pivot Tables: Using Multiple fields in a single table

[DrRichS]

Hi,

I've just started trying to use pivot tables. I surveyed some students using Forms and have downloaded the results. One of questions was "how do you rate the following in importance" Job Security, Co-workers, Public Facing, etc etc. So assentially they had 10 questions that they could answer Strongly Agree, Agree, Neutral, Disagree, Strongly Disagree.

What i'd ideally like is the column headings to be "Agree, Neutral etc" and the row headings to be the "questions" and then for it to count the values. However, it always seems to try and nest the questions and i can't get it to work... i have attached an image to show the pivot table and below it what i'd like...

Any ideas?

Many thanks
RIchard

 

[Alex Blakenburg]

Its going to depend how your underlying data is structured. See if this helps.

Book4
	A	B	C	D	E
1
2	Topic	Type	Count of Agree Strongly	Count of Neutral	Count of Agree
3	Work Flexibility	Coworker	2	1
4		Job security		1	2
5	Work Flexibility Total		2	2	2
6	Grand Total		2	2	2
7
8
9
10	Topic	Type	Agree Strongly	Agree	Neutral
11	Work Flexibility	Coworker	Y
12	Work Flexibility	Job security		Y
13	Work Flexibility	Coworker	Y
14	Work Flexibility	Job security			Y
15	Work Flexibility	Coworker			Y
16	Work Flexibility	Job security		Y
17
Sheet1

 

[DrRichS]

Hi @Alex Blakenburg - thanks for getting back to me. it isn't quite working because i think my original data is a little different to yours. ive posted another screen grab. each row is a different respondent so i'm trying to count how many agree etc.

Ideally i'd end with a table which has "Agree Strongly - Agree - Neutral - Disagree-Disagree Strongly" as the column headers, and each row would be the category "Coworker - Job Security - Work Flexibility" and then it would have the counts

Coworker relationships	Job Security	Work Flexibility (work from home / flexitime / not shift work)
Agree	Agree	Agree
Agree	Agree Strongly	Agree
Agree	Agree Strongly	Agree Strongly
Agree	Agree	Agree
Agree	Agree Strongly	Agree
Agree	Agree Strongly	Agree
Neutral	Agree Strongly	Neutral
Agree	Agree Strongly	Neutral
Neutral	Neutral	Neutral
Agree	Agree	Neutral
Agree	Agree	Neutral
Agree Strongly	Agree Strongly	Disagree
Disagree	Agree	Neutral
Agree	Agree	Neutral
Agree	Agree	Neutral
Agree	Agree Strongly	Neutral
Agree Strongly	Agree Strongly	Agree Strongly
Agree Strongly	Agree Strongly	Agree
Agree Strongly	Agree	Agree
Agree	Agree Strongly	Agree Strongly
Agree	Agree Strongly	Agree
Agree Strongly	Agree Strongly	Neutral
Neutral	Agree	Agree
Neutral		Neutral
Agree	Agree	Neutral
Agree	Neutral	Disagree

 

[Alex Blakenburg]

You can't use a pivot table with that layout.
You can use Power Query to transpose the data and either output it as a pivot table or summarise it using PQ.
Since you have MS 365 you can probably also do it using formulas.

 

[DrRichS]

@Alex Blakenburg Thanks very much for letting me know. I was going to do it a long winded way wiht formulas etc but people rave about pivot tables so thought i'd learn about them. If you have a second, Can you explain briefly why pivot tables aren't suitable here? it seems like it would be quite a useful / easy attribute for pivot tables?

 

[Alex Blakenburg]

Pivot tables are great but they expect the data to be in a database layout.
ie each row is a record and the value of every item in that row belongs to the same record / item
The data in your rows are not related. You create a pivot on just each column but that would be a bit cumbersome.

You will probably find people are now talking about Power Query like they used to talk about pivot tables, so have a look at a couple of videos and see if it peaks your interest.

Sample of what a pivot built on just one column would look like.

Book1
	G	H	I	J	K
40		Coworker relationships
41		Agree	Agree Strongly	Disagree	Neutral
42	Count of Coworker relationships	16	5	1	4
Sheet1