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.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook that we have used to prepare this article.

## 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**}.

**Read More: VBA to Transpose Multiple Columns into Rows in Excel (2 Methods)**

### 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 (4 Methods)**

**Similar Readings**

**How to Transpose Duplicate Rows to Columns in Excel (4 Ways)****Excel Power Query: Transpose Rows to Columns (Step-by-Step Guide)****How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)****Transpose Multiple Columns into One Column in Excel (3 Handy Methods)**

### 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 i**s 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**}

**Read More: Convert Columns to Rows in Excel Using Power Query**

### 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**}.

**➤ INDIRECT(ADDRESS((ROW($B5)-4)*3+COLUMN(B5),2))**

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

**Read More: How to Convert Columns to Rows in Excel Based On Cell Value**

## 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**

**How to Transpose Every n Rows to Columns in Excel (2 Easy Methods)****Excel VBA: Transpose Multiple Rows in Group to Columns****How to Transpose Rows to Columns Based on Criteria in Excel****How to Transpose Rows to Columns Using Excel VBA (4 Ideal Examples)****How to Transpose Rows to Columns in Excel (5 Useful Methods)****VBA to Transpose Array in Excel (3 Methods)****How to Convert Column to Comma Separated List With Single Quotes**