Pivoting an Excel Datatable

Suppose you want to pivot the following Excel table:

Country Name Series Name Year Value
Afghanistan Birth rate, crude (per 1,000 people) 2011 [YR2011] 36.556
Afghanistan Birth rate, crude (per 1,000 people) 2012 [YR2012] 35.254
Afghanistan Death rate, crude (per 1,000 people) 2011 [YR2011] 8.375
Afghanistan Death rate, crude (per 1,000 people) 2012 [YR2012] 8.095
Albania Birth rate, crude (per 1,000 people) 2011 [YR2011] 12.651
Albania Birth rate, crude (per 1,000 people) 2012 [YR2012] 12.756
Albania Death rate, crude (per 1,000 people) 2011 [YR2011] 6.71
Albania Death rate, crude (per 1,000 people) 2012 [YR2012] 6.812
Algeria Birth rate, crude (per 1,000 people) 2011 [YR2011] 24.658
Algeria Birth rate, crude (per 1,000 people) 2012 [YR2012] 24.579
Algeria Death rate, crude (per 1,000 people) 2011 [YR2011] 5.855
Algeria Death rate, crude (per 1,000 people) 2012 [YR2012] 5.895

Karunager Molugu has kindly shown me how to do this:

Load the table into Excel and

Create Pivot with these

On Rows

Country Name

Year

On Columns

Series

On Values

Values

Pivot 1

Right click on the country name and select field setting. In Layout & Print, set options as below

Pivot 2

Output now looks as follows:

Pivot 3

Right Click and go to Pivot Table Options. Totals & Filters section, uncheck remove totals

Pivot 4

On Display tab; uncheck show expand/collapse box

Right click on the Country Total line (“Afghanistan Total”) and uncheck Subtotal “Country Name”

Pivot 5 

Now you should have this:

Pivot 6

This is the optimal form for loading into Tableau.

Leave a Reply

Your email address will not be published. Required fields are marked *