Search code examples
excelexcel-formulaoffsettransposeworksheet-function

Transpose row four columns at a time (row to 4-column matrix)

I have data in the following layout (each 'digit' in its own column but all in Row 1 of Excel):

1 2 3 4 5 6 7 8 9 1 2 3 4 5 6 7

How might I easily convert this to:

1 2 3 4  
5 6 7 8  
9 1 2 3  
4 5 6 7

I want every four pieces of data to be added, in separate columns, to new rows underneath one another.

Solution

  • If your data starts in A1, please try in A3, copied across to D and down to suit: 

    =OFFSET($A$1,,COLUMN()+4*(ROW()-2)-5)