How to Unstack Data from One Column to Many using Excel?

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.

1. First, write the whole TRANSPOSE function in the INDIRECT function as text between double quotes.

=TRANSPOSE(INDIRECT("B2:B6"))

2. Then replace the original row value (the number 2) with cell E2 outside the doubles-quotes
3. Link the string and the reference to E2 using the symbol &.

=TRANSPOSE(INDIRECT("B"&E2

5. Remove the last reference of the row and replace it with the reference E2 using the symbol &.
6. Add +4 to the value in E2, and now the formula is complete:

=TRANSPOSE(INDIRECT("B"&E2&":B"&E2+4))

Step 5: Copy the formula for the other rows

To finish, you just copy this formula for the other cells to unstack all your data easily.

By using these simple steps, you can unstack your data in Excel and organize it into multiple columns, making analysis more manageable.