I'm trying to make a pivot table from data where there is one column I want to pivot on (Hair Color) and then a lot of columns I want to calculate counts for. These count columns all have a common list of values. I'm able to make individual pivot tables for each of those count columns but I'm wondering if there's a way to combine them.
Here's my data:
Here's the pivot table I've made for just the first column. Wondering if I can somehow just add the other column counts for grapes and bananas in rows C and D.
1 Answer
To do this, you will need to reshape your data so that you have a column called "Fruit", which has values "Apples", "Grapes" and "Bananas".
You can do that with PowerQuery. This may seem a little long-winded, but stick with it, it's very useful!
Select any cell in your data and use Data>Get & Transform Data>From Table/Range.
In the Power Query Editor, select the three fruit columns and use Transform>Any Column>Unpivot Columns>Unpivot Only Selected Columns. You should then have a dataset that looks like this:
You could stop here, but let's tidy it up a bit.
Select the "Attribute" column and right-click then use "Replace Values", replacing "Do you like " with nothing like this:
Now do the same thing, but replace ? with nothing. So afterwards, you have just the fruit names in the column.
Now right-click Attribute and use Rename Column and rename the column to Fruit.
Similarly, rename the Value column to Answer.
Now use Home>Close & Load to put the finished dataset back into your workbook.
You can now create the pivot table you want, like this:
If new columns or rows are added to the source data, you can just right-click the query result (shown in green rows above) and use "Refresh" to add the new fruits or hair colors and their answers to the results. Similarly, then refresh the pivot table to see the new items.
0
