Forum Discussion
Pivot Table Assistance
Hello All,
I am trying to see if Pivot Table or another excel function would be able to help me tabulate data faster. I do not need crosstabs, but rather counts of each response for each column. Survey response style.
So for Q1 (Column B), I'd want the number of 1's, 2's, 3's. For Q2 (Column C), I'd want the number of 1's, 2's, 3's. So on and so forth.
Currently I'm using Pivot Table to generate the counts for each question, but it's one column (question) at a time. The end goal would be to generate a simple topline report (see below). Nothing fancy. Just wanted to see if there was a faster way than doing one pivot table at a time.
Any insight is greatly appreciated!
Csek240 I don't really see how the data in the first picture relates to the summary in the second, but I trust that your data set has much more information than what is shown in the pictures.
Tabulating data is made easy with Power Query. It allows you to unpivot data and then group it by Question and Number and count each occurrence.
How exactly? That's difficult to explain without a more complete picture of the data you are dealing with. Can you share (OneDrive or similar) a file with a sample of anonymised data, though resembling your real data?
- Hi Riny,
I messaged you a one drive link with the report/data so you can see them better.
Thanks!
