One of the most common data cleanup issues is changing the structure of the data. Perhaps rows need to become columns and columns rows. In some cases you actually may need to turn a report back into a database. The terminology for this is often mistaken which means when you try and find a solution you use the wrong tools in Excel. The terminology to get right is transpose vs unpivot.
Transpose (turn rows into columns and columns into rows)
When you transpose, you want everything to change around. So below the title and dates move from rows to columns. The total number of cells used also stays the same. So the Original is 4 columns by 5 rows (20 cells) and the Transposed is 5 columns by 4 rows (20 cells). You have just swivelled them around.
The easiest way to Transpose data in Excel is to use the Paste Special Transpose button. You can learn all about Paste Special in the Excel beginner course.
Unpivot (you need to normalise the data)
An unpivot reformats the data to make it easier to use in Excel. So below you will notice it ‘creates’ additional rows and makes sure that each bit of data appears in its own line. It will generally create a new column/s and increase the overall number of cells used i.e. going from
- 4 columns by 5 rows (20 cells) to
- 3 columns by 13 rows (36 cells)
We have found that most times you actually want to do an Unpivot rather than a Transpose. The problem is that performing an unpivot has been very difficult in Excel for years. However, now the new Get & Transform tool (aka PowerQuery) allows you to do this very easily.
Want to learn more about Microsoft Excel? If you prefer attending a course and live in South Africa look at the Johannesburg MS Excel 3 Day Advanced Course or the Cape Town MS Excel 3 Day Advanced training course. If you prefer online learning or live outside South Africa, look at our online MS Excel training courses.