How to convert multiple rows and columns to columns and rows in MS Excel?

The following formula can convert every four rows of data in a column to four columns of data in a single row.

=OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))

See the example below:

Insert the following in the A column in the relevant rows.

A1: Name 1
A2: Address 1
A3: Address 2
A4: Telephone 1
A5: Name 2
A6: Address 1
A7: Address 2
A8: Telephone 2
A9: Name 3
A10: Address 1
A11: Address 2
A12: Telephone 1

Insert the following formula in column C, D, E and F:

=OFFSET($A$1,(ROW()-1)*4+INT((COLUMN()-3)),MOD(COLUMN()-3,1))

Tags: Microsoft, Excel, Rows, Columns

Related