Hello Excellers, welcoe back to another #ExcelTips, #FormulaFriday in my Excel 2021 series. Today I will show you how to unstack data in Excel that looks like the sample data set below WITHOUT Formulas!. I have inherited an Excel worksheet that contains address data. Unfortunately, each address line stacked on top of another makes any data analysis or use more difficult.
The data set is a list of addresses with three lines, Street, Town and Code. The address lines are not in columns but rows. Not a problem. A few clicks can sort this out. So, follow the steps below to begin filling out the new data set.
- Point the cursor to G4 for the Street
- I5 for the Town
- J6 for the Code
- Select the first three lines of the data set and drag the formula down to the bottom of your data set.
- Hit Ctrl+C
- Paste | Paste Special Values
- With the data set still selected, press F5
- Select GoTo | Special
- Next select Blanks. All of the blank cells are highlighted
- Right click | Delete | Shift Cells Up
The data moves up and aligns correctly.
So, that is how easy is it to unstack your Excel data set. The address data can now be used as a data table or database records that are more useful and easier to analyse.
Do you want even more Excel tips?. How about reading all of my Macro Monday or Formula Friday blog posts?. Why not bookmark them. They are updated every week.
