How to Unstack Data using Excel?

Last Updated on 28/09/2023
Reading time: 3 minutes

How to Unstack Data from One Column to Many using Excel? There are 2 techniques

  1. The first method will use the INDIRECT function

    We build a series of references that will be interpreted by the INDIRECT function

  2. 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.

Data in a single column in Excel

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.

Paste Special option 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)

Unstack data in Excel with Transpose

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)

Unstack second block of data in Excel

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)

Sequence to create a series of numbers

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"))

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

=TRANSPOSE(INDIRECT("B"&E2

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

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

Formula to unstack with dynamic range of cells

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.

Unstack 1 column to many with TRANSPOSE and INDIRECT

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

The WRAPROWS function

This new function, released in 2022 by Microsoft, 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)

Unstack data in Excel witth WRAPROWS

Leave a Reply

Your email address will not be published. Required fields are marked *

How to Unstack Data using Excel?

Reading time: 3 minutes
Last Updated on 28/09/2023

How to Unstack Data from One Column to Many using Excel? There are 2 techniques

  1. The first method will use the INDIRECT function

    We build a series of references that will be interpreted by the INDIRECT function

  2. 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.

Data in a single column in Excel

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.

Paste Special option 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)

Unstack data in Excel with Transpose

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)

Unstack second block of data in Excel

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)

Sequence to create a series of numbers

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"))

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

=TRANSPOSE(INDIRECT("B"&E2

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

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

Formula to unstack with dynamic range of cells

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.

Unstack 1 column to many with TRANSPOSE and INDIRECT

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

The WRAPROWS function

This new function, released in 2022 by Microsoft, 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)

Unstack data in Excel witth WRAPROWS

Leave a Reply

Your email address will not be published. Required fields are marked *