Some systems extract data where all the data are in one column like in the following situation

In this situation, you can't analyse your data. So you need to unstack data from one column to many.

Most of the people will perform a lot of copy-paste to reorder the data but of course it’s a waste of time and you will necessarily to mistake. But you can reorder data with the function TRANSPOSE and INDIRECT

Step 1 : Write a formula with TRANSPOSE

The TRANSPOSE function works like the copy-paste special Transpose. You specify a range of cells in rows as argument and the function will return the result in columns.

Because the result is returned in a range of cells, that’s what is called and array function. And the way to return array function is different between Office 365 and the other Excel version.

TRANSPOSE in Office 365

Since November 2018, Office 365 can manage automatically array functions because array formulas, like UNIQUE, SORT or SEQUENCE, has been released.

This has changed the way to create array formulas because you just have to write your formula and automatically, Excel is able to extend the result to many cells as needed.

If the function has not enough cells to return the result, all the array function will returns #SPILL

TRANSPOSE with other Excel version

With TRANSPOSE, the result will be display in more than one cell. So, to return the result in many cells you must

1. Select as many cells as needed (here 5 cells on columns)
2. Write your formula =TRANSPOSE(A2:A6)
3. Validate your formula with the keys Ctrl + Shit + Enter

Construction of the formula

The construction is very simple. You just have to select the range you want to transpose

=TRANSPOSE(B2:B6)

Validate with one of the techniques describe earlier and the result is returned in 5 columns

Step 2 : Analyze your reference

Next, let’s create a second TRANSPOSE function for the second bloc of cells A7:A11

So, if you look at the references of the 2 TRANSPOSE formulas, you can see that only the values of the rows is changing. More, each ranges have 5 cells.

Step 3: Increase the series a number

This part is easy 😊 We will create a series of number with the fill handle (video here)

Step 4: Insert INDIRECT in the reference of the range

Now we will use the value of the column E in the reference of the function TRANSPOSE. And the only way to customize a reference is to use the INDIRECT function

First, write the whole TRANSPOSE function in the INDIRECT function. The reference of the cells must be written as text; that means between double-quote

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

Then, delete the first reference of the row and replace it by 2 double-quotes

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

Between these 2 new double-quotes add the reference to the cell E2, the cell that has the value that we want to include in the reference. And to link the string and the reference to E2, you must use the symbol &

=TRANSPOSE(INDIRECT("B"&E2&":B6"))

Finally, we remove the last reference of the row (the value 6 in this case) and we replace again by the reference E2 with the symbol &

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

But this is not correct because the value in E2 is 2 and we want 6. So, we just have to add +4 to the value in E2 and now it works perfectly

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