0

I have a table which records revenue for each product, split into type of revenue: price, fees, tax etc.

source data

To analyze the data I'd like to build a PivotTable to show the revenue breakdown, e.g.:

PivotTable

So to build this PivotTable I need to somehow create an intermediate table of the following format so the PivotTable can be used:

data for pivot table

My question is, how do I create a PivotTable from the original table automatically without manually constructing the second table?

Ralph
  • 9,284
  • 4
  • 32
  • 42
ytk
  • 311
  • 1
  • 5
  • 15

1 Answers1

2

Here are the steps you have to do:

(1) Enter the data.

enter image description here

(2) Go to the Data tab and select From Table/Range.

enter image description here

(3) A new window will open up with the Query Editor. Here you need to go to the Transform tab, select the three columns you wish to unpivot and then click Unpivot Columns.

enter image description here

(4) The result will be the following (shown in the Query Editor)

enter image description here

(5) Now you can Load this data to the sheet using Close & Load To... on the Home tab

enter image description here

(6) Select the option to show the data in a pivot table and indicate where the pivot table should be placed before clicking OK

enter image description here

(7) The final result should look something like this once you have dragged the various field into the respective boxes: product and attribute into rows while values goes into the values box

enter image description here

Ralph
  • 9,284
  • 4
  • 32
  • 42
  • 1
    That is exactly what I am looking for. Thank you! (p.s. I use Excel 2010 so had to download PowerQuery add-on from Microsoft to get this feature.) – ytk Nov 15 '17 at 01:40