Similarly type TransC4 and TransD4 in cells B13 and B14.

In this case, I selected B12 and typed TransB4 instead of the formula ‘ =B4’. Select any empty cell from where you want to transpose your dataset.Utilizing Cell Reference to Transpose Column to Multiple RowsĪnother way to transpose columns to multiple rows is to use cell references. Read More: How to Switch Rows and Columns in Excel Chart (2 Methods)ĥ. Thus you can transpose columns to multiple rows by using INDIRECT and COLUMN Functions. You will see the whole dataset becomes transposed. Now select B12:B14 and drag the Fill Handle icon horizontally to H14.Type the following formula in cell B12.I’m giving you the necessary instructions below. We can also apply the INDEX Function to transpose columns to multiple rows. Implementing Excel INDEX Function to Transpose Column to Multiple Rows Read More: How to Convert Multiple Rows to Columns in Excel (9 Ways)ģ.

Thus you can transpose a single column into multiple rows by combining OFFSET, ROWS, and COLUMN Functions.

Then, drag the Fill Handle icon vertically to cell D14 and you will see the Customer IDs in a 3×2 Matrix.

Now use the Fill Handle to AutoFill the adjacent cell D12.

Hit ENTER and you will see the first Customer ID in this cell.

OFFSET($B$5,0,0) -> Here, the OFFSET function offsets B5 Offsets 0 row and 0 column and will return the value of the B5 cell.

(ROWS($5:5)-1)*2 -> Also returns (We multiply this formula by 2 because the return array will be a 3×2 Matrix and we want 2 cells in each row).

It will return a range that is a particular number of columns and rows from the reference B5. Here, we used the OFFSET Function to return values taking the B5 cell as a reference.

Change columns to rows in excel