CODEWITHSUNDEEP
excelpivot-table
Anthony M
Anthony M

Reputation: 75

Use column labels from an Excel table as the rows in a Pivot Table

We have a table that looks like this:

Year  Malaria  Tuberculosis  Dengue Fever  Ebola  [etc etc]
-----------------------------------------------------------
2015  20       185           17            3
2016  25       149           22            54
2017  24       132           24            4
2018  22       108           20            7

We want to have a pivot table that automatically shows the columns as rows and adds new rows as columns are added, like this:

Year          Total
-----------------------
[+] Malaria          91
[-] Tuberculosis    574
    2015        185
    2016        149
    2017        132
    2018        108
[+] Dengue Fever     83
[+] Ebola            68
[More rows...]  
-----------------------
TOTAL               816

How would we do this?

It doesn't have to be a PivotTable; that just feels like it would be the most logical place to find a solution.

Upvotes: 3

Views: 3186

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520968

I was able to do this using a combination of Excel's unpivot capability, combined with pivot tables.

First, we can try unpivoting your data:

  • Highlight your current table, including the headers
  • Then from the Data section of the ribbon, select From Table
  • Highlight all the columns containing data, but not the Year column, and then select Unpivot Columns

enter image description here

Close the dialog and keep the changes. Excel should place the unpivoted data into a new worksheet, looking something like this:

enter image description here

Now the final step is to create a pivot table using this unpivoted data. Here is a screenshot of my pivot table setup:

enter image description here

Note that Attribute (e.g. Malaria, Tuberculosis) is placed above the Year. This lets us open or close each disease and the corresponding data for all the years.

This leads to the following pivot table, which is along the lines of what you expect:

enter image description here

The major step I had to take here to get this to work was to first unpivot your data. Just building a pivot table around your original table did not seem to lead anywhere.

Upvotes: 4

Related Questions