Well, in this blog post I am going to cover a very common scenario – not having your data the right way around to do something with it i.e. make a pivot table from it. However I am going to show you how to do that with a not (as yet) well known add-in for Excel called Power Query. If you want to be able to sort and analyse your data properly, it needs to be in what is called a normalised format (basically organised down as opposed to across) but the natural tendency is go straight to organising it across.
The file is here: unpivoted_data
Wong Way
Usual (not normalised way)
| SURNAME | FIRST NAME | PERSONNEL NO. | Grade | 24-Nov | 25-Nov | 26-Nov | 27-Nov | 28-Nov |
| Hebron | Lavinia | 12345 | Elf | 7.5 | 7.5 | 7.5 | 7.5 | 7 |
| Dogsbody | Spaniel | 12344 | Hobbit | 6 | 6 | 6 | 6 | 5.6 |
| Waggletooth | Festus | 12222 | Orc | 7.5 | 7.5 | 7.5 | 7.5 | 7 |
Same data – Normalised (way you need to have it if you want to run a pivot table)
| SURNAME | FIRST NAME | PERSONNEL NO. | Grade | Attribute | Value |
| Hebron | Lavinia | 12345 | Elf | 24-Nov | 7.5 |
| Hebron | Lavinia | 12345 | Elf | 25-Nov | 7.5 |
| Hebron | Lavinia | 12345 | Elf | 26-Nov | 7.5 |
| Hebron | Lavinia | 12345 | Elf | 27-Nov | 7.5 |
| Hebron | Lavinia | 12345 | Elf | 28-Nov | 7 |
| Hebron | Lavinia | 12345 | Elf | 29-Nov | DO |
| Hebron | Lavinia | 12345 | Elf | 30-Nov | DO |
| Dogsbody | Spaniel | 12344 | Hobbit | 24-Nov | 6 |
| Dogsbody | Spaniel | 12344 | Hobbit | 25-Nov | 6 |
| Dogsbody | Spaniel | 12344 | Hobbit | 26-Nov | 6 |
| Dogsbody | Spaniel | 12344 | Hobbit | 27-Nov | 6 |
| Dogsbody | Spaniel | 12344 | Hobbit | 28-Nov | 5.6 |
| Dogsbody | Spaniel | 12344 | Hobbit | 29-Nov | DO |
| Dogsbody | Spaniel | 12344 | Hobbit | 30-Nov | DO |
| Waggletooth | Festus | 12222 | Orc | 24-Nov | 7.5 |
| Waggletooth | Festus | 12222 | Orc | 25-Nov | 7.5 |
| Waggletooth | Festus | 12222 | Orc | 26-Nov | 7.5 |
| Waggletooth | Festus | 12222 | Orc | 27-Nov | 7.5 |
| Waggletooth | Festus | 12222 | Orc | 28-Nov | 7 |
| Waggletooth | Festus | 12222 | Orc | 29-Nov | DO |
| Waggletooth | Festus | 12222 | Orc | 30-Nov | DO |
If you have to “normalise” data in Excel normally – well I’ll put it this way…it ain’t easy…but it’s literally the work of minutes with Power Query (I am using Excel 2010 – it’s not available in earlier versions)
[fvplayer src=”https://s3-eu-west-1.amazonaws.com/the-excel-expert/power_query_unpivot.mp4″]
These are the steps
- Make sure you have Power Query installed (you will probably need to google for it or ask your IT administrators to install it for you)
- It will appear as a new ribbon on your Excel.
- Click in your data
- Click on the Power Query tab.
- Click on From Table
- Power Query will select the data you have the cursor in (note that in this example you will have to make sure you have selected all the data all the way across)
- Click on OK.
- Power Query is fired up.
- Highlight the first four columns: Surname, First Name, Personnel no and Grade. You can use the Ctrl key to select more than one
- Click on the Transform Ribbon
- Click on the tiny triangle just to the right of Unpivot columns
- From the dropdown choose Unpivot Other columns
- Your data has now been unpivoted.
- We still have Dos in our Values column.
- Highlight the Values column
- From Transform, click on Replace Values
- In the Values to Find box, type in DO
- In the Replace With box, enter 0 (zero ). Power Query hates blanks…
- When you are finished, click on Home – Close and Load and this will bring the (normalised) data back into Excel – ready to be pivoted.
Here is the same data ready to be pivoted.