Got a Report that's hard to use Functions on or Analyze? NORMALIZE Your Table using the PivotTable Wizard

The Dilema

You have a sheet set up something like this…

Acct   Jan Feb Mar 
Acct01 234 123 567 
Acct02 345 234 678 
Acct03 456 345 789

…and you’re having problems applying formulas and doing the analysis to it that you’d like to do. The reason is that this is a report or summary presentation of data and Excel’s data analysis tools are designed to work on NORMALIZED data.

Excel’s PivotTable (PT) Wizard to the RESCUE!

This little ‘trick’ might save your lunch some day.

  1. Start the wizard – Data/PivotTable & PivotChart Report…
    If you have Excel Version 2007+ use alt+D P to activate the PT Wizard

  2. Step 1 of 3 - Select Option Button: Multiple Consolidation Ranges – [Next]

  3. Step 2a of 3 - Select Option Button: I will create the page fields – [Next]

  4. Step 2b of 3 - With your cursor in the Range Textbox, select the data range on your sheet that you want to normalize – [Add] – [Next]

  5. Step 3 of 3 - [Finish]

  6. Drag the Row and Column buttons OFF the Layout – [OK] – [Finish]

  7. Mysteriously, you are on another sheet that has a 4-cell pivot table. Double click the BOTTOM RIGHT CELL

  8. Again, on another sheet – This is you data normalized, or at least closer to it. You’ll need to change headings at least.

     Row    Column Value
     Acct01 Jan    234
     Acct01 Feb    123
     Acct01 Mar    567
     Acct02 Jan    345
     Acct02 Feb    234
     Acct02 Mar    678
     Acct03 Jan    456
     Acct03 Feb    345
     Acct03 Mar    789

BTW, my example had only one column, Acct, of ROW data. If your instance has more than one column of ROW data, you will need to concatenate the ROW data, separated by a delimiter such as | into a single column before starting this process. This example with 2 ROW items…

A      B    C   D   E
Acct   Sub  Jan Feb Mar 
Acct01 A01A 234 123 567 
Acct02 A02B 345 234 678 
Acct03 A03A 456 345 789

…would become this using the formula C1: =A1&"|"&B1 by first inserting a COLUMN between the ROW data and the COLUMN data, in

A      B    C           D   E   F
Acct   Sub  Acct|Sub    Jan Feb Mar 
Acct01 A01A Acct01|A01A 234 123 567 
Acct02 A02B Acct02|A02B 345 234 678 
Acct03 A03A Acct03|A03A 456 345 789

The normalization process will not use the first two columns. Rather the Data in COLUMNS C:F

Then, when the proceed is completed, use the Data > Data tools > Text to columns feature to complete the table, returning Acct and Sub in this case to separate columns, naturally shifting the “Column” and “Value” columns to the right appropriately.

You might want to check out this EXCELLENT link ‘Relational Database Design’

If you have a comment or question regarding this procedure, feel free to reply below.

Here’s hoping all your data is just as normal as I :flushed:.

1 Like

Thank you Skip!

I found myself with a pile of data just like this a few months ago. I sorted it MANUALLY because I didn’t know about this.

Another tool in your toolbox.