Can a single pivot table in Google Sheets summarize multiple Likert Scale responses?

user_10951778 0
Viewed 57

I have a data set with multiple columns that contain Likert Scale responses. In the header of each of these columns is a Likert Scale question such as, "How much do you agree with this statement?". The values of each of these columns contain Likert Scale answers that range from: "Strongly Disagree", "Disagree", "Neutral", "Agree", and "Strongly Agree".

I cannot provide a working Google Sheet sample at this time (on my work computer and away from home without access to a personal computer) but I can create sample tables below showing my expected Pivot Table result and a sample data set.

What I would like to do is create a Pivot Table using the data from these Likert Scale questions to analyze the total number of Likert Scale answers for each question. The problem I am facing is that I would like to accomplish this using only one Pivot Table as opposed to creating a Pivot Table for each individual question. So, ideally, I would like a Pivot Table that looks similar to this with the Likert Answers as columns and Likert Questions as rows (or vice-versa) like so:

Strongly Disagree Disagree Neutral Agree Strongly Agree
Question 1 2 0 1 1 0
Question 2 1 1 0 0 2
Question 3 0 0 0 0 4

And the data set sample I would like to Pivot looks like this:

Question 1 Question 2 Question 3 Year Received
Strongly Disagree Strongly Disagree Strongly Agree 2020
Strongly Disagree Disagree Strongly Agree 2021
Neutral Strongly Agree Strongly Agree 2020
Agree Strongly Agree Strongly Agree 2022

Any help/input would be greatly appreciated even if the answer is simply, "Sorry, I do not think that this is possible with a single Pivot Table." which is the conclusion I have drawn.

As far as I am aware, this cannot be done in a single Pivot Table and instead requires an individual Pivot Table to be created for each question (which is not acceptable) or for this table to be created with a formula instead (which I have already done according to this method because I couldn't figure this one out via Pivot Table). I personally rarely, if ever, use Pivot Tables so I could just be missing something basic here and I am curious if any of you Pivot Table heroes out there can solve this one.

EDIT: To clarify, I am hoping to use a Pivot Table because I have Slicers connected to the source data to be able to filter the output table by different criteria such as YEAR.

spreadsheet flatten google-sheets pivot-table pivot
2 Answers

try if it works for you with slicers:

=INDEX({"", "Strongly Disagree", "Disagree", "Neutral", "Agree", "Strongly Agree"; 
 FLATTEN(A1:C1), QUERY(QUERY(SPLIT(FLATTEN(A2:C&"×"&A1:C1), "×"), 
 "select count(Col2) where Col2 is not null group by Col2 pivot Col1", ), 
 "select Col5,Col2,Col3,Col1,Col4 offset 1", )*1})

enter image description here

update:

=INDEX({"", "Strongly Disagree", "Disagree", "Neutral", "Agree", "Strongly Agree"; 
 SORT(FLATTEN(A1:C1)), QUERY(QUERY(SPLIT({FLATTEN(A2:C&"×"&A1:C1&"×"&D2:D); 
 {"Strongly Disagree"; "Disagree"; "Neutral"; "Agree"; "Strongly Agree"}&"×'×"&
 IF(G1="", 1, G1)}, "×"), 
 "select count(Col2) where Col3 is not null "&
 IF(G1="",," and Col3 >="&G1)&
 IF(G2="",," and Col3 <="&G2)&
 "group by Col2 pivot Col1", ), 
 "select Col5,Col2,Col3,Col1,Col4 offset 2", )*1})

enter image description here

demo sheet

user_56326290

Adding to player0 answer

Use the formula he provided. with some adjustments to the header. but in a new Sheets so when adding a slicer it dosent interfere with the source data.

=INDEX({SPLIT(" ,Strongly Disagree,Disagree,Neutral,Agree,Strongly Agree",","); 
 FLATTEN(Sheet1!A1:C1), QUERY(QUERY(SPLIT(FLATTEN(Sheet1!A2:C&"×"&Sheet1!A1:C1), "×"), 
 "select count(Col2) where Col2 is not null group by Col2 pivot Col1", ), 
 "select Col5,Col2,Col3,Col1,Col4 offset 1", )*1})

enter image description here

user_195296940
Related