Mouse Training London Ltd: Excel Pivot Tables- Flattening a Cross tab (Normalising data)

Monday, 21 March 2016

Excel Pivot Tables- Flattening a Cross tab (Normalising data)

Data for a human being

We usually work with and better understand data which is presented two-dimensionally in a matrix or cross-tabulation. In other words, the classic spreadsheet with headings up the top, descriptions down the side and data in the middle.

Data for a machine

But computers don't work like that, they need data in one-dimensional normalised tables. When you need source data for an Excel Pivot Table or for any sort of database table you have to "unpivot" a cross-tabulation into a simple list.

Converting one data presentation from one to the other is a horrible task involving a significant labour of moving and copying data. But not if you use Excel's old Pivot Table Wizard method.

Firstly, you have to get the shortcut trigger for the Wizard as it is not available on the ribbon and then you create a Pivot Table on Multiple Consolidation Ranges and from that you can easily generate your normalised data.

Getting the shortcut onto the Quick Access Toolbar

More Commands

You only have to do this once, after that you can use it as many times as you like. To change Excel's Quick Access Toolbar you can either click File tab, Options and Quick Access Toolbar or (far easier, I think) click at the end of the Quick Access Toolbar (top left of the Excel window) and choose More Commands.

Click PivotTable and PivotChart Wizard from the Commands Not in the Ribbon category. Finally, click the Add button to add it to the list on the right and click OK. Job done.

PivotTable and PivotChart Wizard, Commands Not in the Ribbon

Creating the Pivot Table

Multiple consolidation ranges

Click on any cell in your cross-tab data and then click the PivotTable and PivotChart Wizard shortcut that you've just created.

In Step 1 of 3 of the Wizard click the Multiple consolidation ranges option under Where is the data that you want to analyze? and the PivotTable option under What kind of report do you want to create?

Then click Next.

I will create the page fields

In Step 2 of 3 of the Wizard click the I will create the page fields option under How many page fields do you want?

Then click Next.

Select your range and click Add

Step 2b of 3 of the Wizard, select the range containing your cross tab data and then click the Add button to complete the All ranges list box.

There's no need to go to Step 3, click the Finish button and Excel inserts a new worksheet and displays your dataset as a Pivot Table.

Normalising the Data

Double-click the Grand Total cell

It looks rather unexciting, all you've done is recreate the original data but as a Pivot Table with a few totals. Now comes the exciting bit, double-click the Grand Total cell.

We all know what happens when you double-click a Pivot Table cell, it's the shortcut for Show Details and quick as a flash all your data is deconstructed and returned as a table onto a new worksheet. Normalised data in a simple list, how easy was that?

Normalised data

If you've ever had to do this sort of thing the long way round you will now want to kick yourself and weep.

If you don't want your data in an Excel table format, click the Table Tools tabs and look for the None format under the Light category in the Table Styles gallery and then the Convert to Range control in the Tools group. I can never see these because I am too busy weeping. Yes, I have frequently done this the long way round.