How to Convert Multiple Rows to Columns and Rows in Excel?

How to Convert Multiple Rows to Columns and Rows in Excel? To convert multiple rows in one column into multiple columns and rows, you can use a formula with the following steps:

Convert multiple rows to columns with formula

Select an empty cell where you want to output the data, for example, cell C2, and enter this formula:

=OFFSET($A$1,(ROW()-2)*3+INT((COLUMN()-3 )),MOD(COLUMN()-3,1))
Then, press Enter to get the first data set pattern, and drag the fill handle down to the cell you need, then drag the fill handle right to the cells as needed until no more data is displayed. See screenshot:

convert rows to columns step 1

How to Convert Multiple Rows to Columns and Rows in Excel

Note:

This formula can be understood as:

OFFSET($A$1,(ROW()-f_row)*rows_in_set+INT((COLUMN()-f_col)/col_in_set), MOD(COLUMN()-f_col,col_in_set))
In the formula:

f_row is the row number of this formula.
f_col is the column number of this formula.
row_in_set is the number of rows that make up one data record.
col_in_set is the number of columns in the original data.
You can change them as needed.