2

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:

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.

pivot table

1 Answer 1

3

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:

enter image description here

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:

enter image description here

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:

enter image description here

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .