# How to Convert Single Columns to Rows in Excel with Formulas

In this article, I will show how to convert single columns to rows using excel formulas. Basically, when we copy data from any word editor to excel, those data remain in unorganized condition. Working with those unorganized data may seem clumsy and time-consuming. So, to save more time and prepare a neat excel dataset, we may try to convert column data to rows. While doing the conversion, I will use several excel functions.

## 4 Methods to Convert Single Columns to Rows in Excel with Formulas

### 1. Excel Formulas with OFFSET Function to Convert One Column to Multiple Rows

First of all, I will use the OFFSET function along with ROWS and COLUMNS functions to convert data from a single column to multiple rows. For example, in my dataset, I have a column containing several peopleâ€™s names, ages, residing states, and professions. However, differentiating among these data seems tough, as they are disorganized. Letâ€™s follow the below steps to organize these data into multiple columns.

Steps:

• First, type the below formula in Cell D5.
`=OFFSET(\$B\$5,COLUMNS(\$B:B)-1+(ROWS(\$5:5)-1)*4,0)`

• Then hit Enter. As a result, you will get the below result. Now, using the Fill Handle (+) tool, drag down the formula of Cell D5 to the right.

• Consequently, the following will be the result. Again use the Fill Handle to drag down the selected row and get the below result.

• In the end, we will get all the columnâ€™s data organized in rows as below.

🔎 How Does the Formula Work?

âž¤ COLUMNS(\$B:B)

This part of the formula returns {1}. We have subtracted 1 from this part and the result becomes zero.

âž¤ ROWS(\$5:5)

This part returns {1}. Here again, we have subtracted 1 from that formula and get zero as result. Then, we have multiplied (ROWS(\$5:5) with 4 as we want 4 cells in each row.

âž¤ OFFSET(\$B\$5,COLUMNS(\$B:B)-1+(ROWS(\$5:5)-1)*4,0)

Eventually, after calculation, the formula returns {John}.

### 2. Apply TRANSPOSE Function to Convert Columns to Rows

We can transform data from columns to rows using the TRANSPOSE function in excel. This is a very easy and quick method to get the desired result. For instance, I have a dataset containing biographic data of several people. But, if I can organize these data row-wise, it would be more convenient to find a particular personâ€™s information.

Steps:

• Type the below formula in Cell C14.
`=TRANSPOSE(B5:E10)`

• Hit Enter and you will get the below result in an array. Now you can find information more easily.

Note:

In other versions of Excel, hit Ctrl + SHIFT + Enter to enter the formula as an array.

Read More: How To Transpose Data in Excel

### 3. Convert Single Column to Rows Using Excel Functions (OFFSET, MOD, ROW, INT, MOD & COLUMN)

Now, I will use a combination of several functions to convert data from single columns to rows. Such as, here I will use the OFFSET function along with MOD, ROW, INT, and COLUMN functions. Letâ€™s assume, in my dataset, there is a column that contains several peopleâ€™s biographical data randomly. Now I will organize these data in multiple rows according to each person’s Names, Professions, and Addresses.

Generic formula for this method is:

`OFFSET(\$B\$5,(ROW()-f_row)*rows_of_set+INT((COLUMN()-f_col)/col_of_set),MOD(COLUMN()-f_col,col_of_set))`

Here,

f_row is the row number of the above formula.

f_col is the column number of the above formula.

rows_of_set is the number of rows that makes one record of data.

col_of_set is the number of columns in data.

Steps:

• First, type the below formula in Cell D5 and hit Enter.
`=OFFSET(\$B\$5,(ROW()-5)*3+INT((COLUMN()-4)),MOD(COLUMN()-4,1))`

• As a consequence, you will get the following result. Now, using the Autofill, drag the formula of Cell D5 to the right.

• As a result, the following will be the output. Drag down the selected row to get the details of the rest of the people.

• Finally, you will get the ultimate result arranged in multiple rows.

🔎 How Does the Formula Work?

âž¤ ROW()

This part of the formula returns {5}. Then we subtracted 5 from the formula and got 0 as result.

âž¤ COLUMN()

This part of the formula returns {4}. Again we have subtracted 4 to get 0. Later, this 0 is passed into the INT function to get 0.

âž¤ COLUMN()

Again we have used the COLUMN function. It’s in the 2nd part of the formula and got 4 as result, and then 4 is subtracted from the formula. Then the result is passed within the MOD function and MOD(0.1) returns 0.

âž¤ OFFSET(\$B\$5,(ROW()-5)*3+INT((COLUMN()-4)),MOD(COLUMN()-4,1))

Finally, the above formula returns {John Smith}

### 4. Combination of Excel Functions (INDIRECT, ADDRESS, ROW & COLUMN) to Convert One Column to Rows

Similar to Method 3, Again I will use a combination of excel functions to get a single columnâ€™s data organized in multiple rows. However, this time I will use a different combination of functions such as INDIRECT, ADDRESS, ROW, and COLUMN functions to get my formula. I will use the same dataset that was used in Method 3.

Steps:

• Type the below formula in Cell D5.
`=INDIRECT(ADDRESS((ROW(\$B5)-4)*3+COLUMN(B5),2))`

• Hit Enter and, we will get â€˜John Smithâ€™ as a result. Drag the formula to the right so that we can get the below result.

• Then again use the Fill Handle to drag down the formula to the down and get the ultimate result arranged in multiple rows.

🔎 How Does the Formula Work?

âž¤ ROW(\$B5)-4)

This part of the formula returns {5}.

âž¤ COLUMN(B5)

This part of the formula returns {2}.

Finally, this part of the formula returns {John Smith}.

## Things to Remember

• While using the TRANSPOSE function, we have to be cautious as if any value in the original dataset is blank then the result of the TRANSPOSE Â formula will return â€˜0â€™ in place of the blank.

• If you change any data of the resulting array of the TRANSPOSE formula, an error (#SPILL!) will occur.

• You can use the Paste Special option too to convert data from single columns to rows.

## Conclusion

In the above article, I have tried to discuss several methods to convert single columns to Rows in Excel with Formulas elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.

## Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Hosne Ara

Hosne Ara is a materials and metallurgical engineer who loves exploring Excel and VBA programming. To her, programming is like a time-saving superhero for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B. Sc. in Materials and Metallurgical Engineering from Bangladesh University of Engineering and Technology, she's shifted gears and now works as a content developer. In this role, she... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF