The formula we have used above to convert the list data of multiple rows into multiple columns and rows can be interpreted as follows − OFFSET($A$1,(ROW()- f_row)*rows_in_set+INT((COLUMN()f_col)/col_in_set), MOD(COLUMN()-f_col,col_in_set))į_row − row number of this offset formula.į_col − column number of this offset formula. The data is now displayed in multiple rows and columns as shown in the following screenshot. To convert all the list data into multiple columns and rows, we have to simply drag the autofill handle from cell C1.ĭrag the autofill handle from cell C1 to the right cells as our need, followed by dragging the autofill handle downward to the cells till the value zero is displayed. Now we get the first-row data of the list data in the cell C1 as shown in the following image. Let’s choose an empty cell (C1) in which we will store the first data of the list of data and type the following formula in cell C1 and press Enter. Let’s consider the following list of multiple rows data and try to convert those data into multiple columns and rows using excel formula.

Let's assume you already have a list of data with many rows that only has one column in an excel sheet, but you want to convert these data to multiple rows and columns, you can achieve this using an Excel formula.

Excel turn row into column