Normal people, by which I mean those who don’t process data all day will often create data that is present in a crosstab table.

This is a poor structure for storing and processing information. We say that its “denormalised” because the three different columns “Europe”, “USA”, “Asia” contain information of the same type – “budget amount”. We want the same data in table with the columns Year, Region, Budget Amount. To get data in this format we have to “unpivot” the crosstab table.

This can be done using Workbook Queries. In a previous post I explained how to do a union query in a similar way you can use the crosstab table as datasource but this time we will transform it before loading by unpivoting the columns containing the regional data.

1. Select table
2. Click “From Table” on the “Get and Transform” section of the Data tab
3. Select the 3 columns we wish to unpivot
4. Click unpivot columns

The result after a bit of renaming is,

This table can be now be used as the source for a pivot table that can display the data much more flexibly than the original crosstab. Here I have shown it transposed, filtered and summed and charted.

Best of all, the data is still bound to the original table. To show updated values, first refresh the tables bound to workbook queries and then any pivot tables.