How to convert Columns to Rows in Excel with Formulas

Sometimes, you may want to convert data in one column to organized data in Excel. This is especially true when you copy-paste unorganized data from a word editor to an Excel sheet. All the data is compiled across a single row in such a case.

Convert Columns to Rows in Excel with Formulas

While you can easily convert an arranged workbook or part of it from rows to columns using the Transpose function, that isn’t the case when the data is unorganized and spread across a single column.

If the unorganized data doesn’t have a pattern, you might first need to sort out a pattern. However, you are usually presented with unorganized data in a pattern.

Eg. Let us assume a case where you have a list of data organized across a single column in the sequence Name, DOB, and Grade.

Karan

01/06/1987

A

Justin

09/08/1964

D

Bob

04/05/1996

B

Jason

08/09/1984

C

In this case, you may want to organize the data in the sequence of Name, DOB, and Grade across rows and columns and not just a single column. This could be done using the OFFSET formula. The syntax for the OFFSET formula would become:

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

As explained in the example mentioned above, if the information is spread across column A starting A1, the formula would become:

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

While this formula divides the information across the 3 rows, the number of columns isn’t fixed. The number of columns depends on the length of the data. In the case of any blank column across the data, it is replaced by a 0.

Suppose you need this data arranged starting C2 and are aware that the data is to be organized across 3 rows, enter this formula in cell C2. Then pull the formula across 3 rows and down across the columns till you start getting 0 as entries.