Have you ever received an awkward data set? In my life as a Data Analyst this has happened to me many times in different shapes and forms.
‘Video 00135 Cali Data Cleanup’ is actually based on a true story.
The real life data set was much larger so any hopes of manually copying and pasting the data are not possible.
Our goal is to make a Pivot Table to analyze Cali’s data. To do this, we’ll need to re-arrange this data set.
Cali’s Awkward Data Set
What’s The Problem?
In order to make a Pivot Table each different piece of data should have a column and each cell should only have 1 piece of data in it.
This screen shot shows us what this data should look like when we are finished.
How Can We Do This?
There are many ways to do this. If you had to repeat this task many times then you would want to automate this using VBA (Excel’s programming language).
In this case it’s a one time only emergency so we use a combination of tricks!
Read the overview below or watch the video for a detailed explanation of all of the steps.
Re-arrange Region Values into 1 Column
We need to take the horizontal Region values (‘North’, ‘East’, ‘South’, ‘West’) and re-arrange them to be vertical.
You could use formulas but I used the ‘ALT D P’ trick (run the data through a pivot table and extract the re-arranged data from the Pivot’s cached data).
This sample shows that we now have the Region values re-arranged vertically in ‘Column’ but notice that some rows have 2 lines of data (we’ll remove the blank rows later).
Look at rows 4, 10, and 13.
‘Text to Columns’ to Split Multiple Rows
Rows that are like row 4, 10, 13, etc need to be split apart. We do this by highlighting column C and using Excel’s ‘Text to Columns’ feature.
For the ‘Delimiter’, we must use a carriage return (a carriage return is what put the rows in separate lines within a cell).
In the ‘Other’ box, we do this by holding down the ‘Ctrl’ button, pressing the letter J and then pressing ‘Next’ and ‘Finish’.
‘Alt D P’ Trick Again
Here you can see the result of the ‘Text to Columns’ step above.
I have concatenated ‘City’ and ‘Region’ into one column.
We will repeat the ‘Alt D P’ pivot cache trick to take two columns of data (‘Value’ and ‘Column1’) and put them into their own row.
A Few Final Touches!
- ‘Text to Columns’ on field ‘Value’ using – as a delimiter.
- Delete field ‘Column’
- ‘Text to Columns’ on field ‘Row’ using _ (Underscore) as a delimiter.
- Rename column headers as desired.
We Are Finished!
You can now select this data and create a Pivot Table to analyze who is selling the most ‘Liver Treats’ and who needs more ‘Grooming Kits’.
Download my Excel file
Download here or via my OneDrive (file 00135)
Watch My YouTube Video
Subscribe to my YouTube channel and learn more!
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.
Hundreds of millions of people use Excel everyday. 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 my blog posts.
Away from Excel I enjoy playing with my dog Cali (she doesn’t like Excel), learning Spanish, playing Chess and drawing nerds (it’s relaxing and gives me a break from data!)