How to Unstack Data from One Column to Many using Excel? There are 2 techniques
- The first method will use the INDIRECT function
We build a series of references that will be interpreted by the INDIRECT function
- The WRAPROWS function
This new function will unstack the data with a single formula
All Data in a single column
Sometimes, data extraction systems present all data in a single column, which makes analysis difficult.
However, you can unstack the data to split it into multiple columns using the TRANSPOSE and INDIRECT functions. Here is how to do it:
Step 1: Use TRANSPOSE to reorder the data
The TRANSPOSE function works like the copy-paste special Transpose.
You specify a range of cells in rows as an argument, and the function will return the result in columns. In Office 365, the function automatically manages array formulas, making it easy to create the formula and extend the result to as many cells as needed.
The formula construction is simple; just select the range you want to transpose and write
=TRANSPOSE(B2:B6)
With other Excel versions, you must select as many cells as needed and validate your formula using Ctrl + Shift + Enter.
Step 2: Analyze your reference
Create a second TRANSPOSE function for the second block of cells
=TRANSPOSE(B7:B11)
Notice that only the values of the rows change while each range has five cells (important for the next steps).
Step 3: Create a series number
Each block of data has 5 rows, so we will create a series of numbers with a step of 5
- We can create a series of numbers with the fill handle.
- Or we can use the SEQUENCE function (a series of 20 rows, starts at 2 and step of 5)
=SEQUENCE(20,,2,5)
Step 4: Create a dynamic range of cells with the INDIRECT function
To avoid writing a TRANSPOSE function for each block, we will use the series of numbers to build the references of each block. The only way to customize a reference is to use the INDIRECT function.
- First, write the whole TRANSPOSE function as text between double quotes in the INDIRECT function.
=TRANSPOSE(INDIRECT("B2:B6"))
- Then replace the original row value (the number 2) with cell E2 outside the doubles-quotes
- Link the string and the reference to E2 using the symbol &.
=TRANSPOSE(INDIRECT("B"&E2
- Remove the last reference of the row and replace it with the reference E2 using the symbol &.
- Add +4 to the value in E2, and now the formula is complete:
The WRAPROWS function
The function WRAPROWS of Excel 365, released in 2022, unstacks data with just 2 arguments
- The column to unstack (the column B)
- The number of columns we want to have, here 5
=WRAPROWS(B2:B66,5)
