Who knows what could happen in the future but for now humans using software cleanup and analyze data (not nerdy robots!). This post reviews examples of awkwardly twisted data that require transposing, rotating, repeating, etc before it can be properly analyzed.
Is Your Data Twisted In Knots, Backwards Or Upside Down?
Let’s look at the most common methods to restructure data and then several examples.
Methods To Restructure Data
- Manual – if it’s a small data-set, a clear pattern, and a 1 time task then the good ol’ copy/paste might be the best option.
- Formulas – if you haven’t learned VBA or Get & Transform you can often use formulas to re-arrange your data.
- VBA – great way to re-arrange your data especially if it’s a re-occurring requirement. Spend the time upfront to save many hours later on!
- Get & Transform – you don’t have to be a programmer to learn! If formulas are too slow and you don’t want to learn VBA then try Get & Transform!
- Pivot Table – what? The old ALT D P trick can properly un-pivot some patterns. It doesn’t work in all cases but if you’re lucky it can make your day!
Let’s Look At Some Formula Solution Examples
‘VIDEO 00122 NORMALIZING EXPORTED DATA’ All columns of data are stacked into a single column. It’s almost impossible to analyze.
What should we do? Define the pattern & re-arrange the data. MY SOLUTION (YouTube)
‘VIDEO 00135 CALI DATA CLEANUP’ As you might guess, the data didn’t belong to my dog Cali. This fictitious sample follows the same pattern as the actual data (which was thousands of rows). I combined several steps to create this pivot-able data set.
Now we can easily pivot the data or analyze with formulas. MY SOLUTION (YouTube)
‘VIDEO 00142 HORIZONTAL LIST TO VERTICAL LIST (LAYERED TRANSPOSE)’ Along with re-arranging data we need to repeat certain values.
Easier than the previous example but it still requires a few steps. I can see how VBA could solve this but I used formulas of course! MY SOLUTION (YouTube)
‘VIDEO 00148 INSERT ROWS, CASCADE NUMBERS’ repeat each row 3 times but only repeat 1 number each time.
Programmers would start writing VBA code. Me? I used a Bob Umlas trick along with a few other steps to quickly solve this. MY SOLUTION (YouTube)
‘VIDEO 00117 TRANSPOSE STACKED TABLES’ Once again, all the data in stacked in 1 column but the pattern was more consistent & easier to solve.
Ken solved this with Excel’s Get & Transform feature. Oz du Soleil is also a big Get & Transform fan!
I solved it with a few easy formulas as the pattern is consistent. MY SOLUTION (YouTube)
Here is another similar case of stacked data that we need to fix! MY SOLUTION (YouTube)
Once Data Is Re-arranged Analyze It With Pivots And Formulas!
Do We Always Have to Restructure Data Sets?
It usually makes sense to restructure our data so that it’s easier to analyze it with formulas and pivots. However, there are exceptions when we can use some complex formulas to get the answers we need without any restructuring.
What Awkward Data Sets Have You Re-structured?
Add a comment below describing how you have re-structured a data set or how you used formulas to analyze your data without restructuring.
About Me
My name is Kevin Lehrbass. This is my personal blog about Microsoft Excel. I live in Markham, Ontario, Canada. I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst.
Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and blog posts.
Away from Excel I enjoy playing with my dog Cali, learning Spanish, playing Chess and drawing nerds.
Pingback: Why and How to Unpivot Data | My Spreadsheet Lab