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.


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.

Excel Formulas with OFFSET Function to Convert One Column to Multiple Rows

Steps:

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

Excel Formulas with OFFSET Function to Convert One Column to Multiple Rows

  • 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.

Excel Formulas with OFFSET Function to Convert One Column to Multiple Rows

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

Excel Formulas with OFFSET Function to Convert One Column to Multiple Rows

  • 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.

Apply Formulas with TRANSPOSE Function to Convert Columns to Rows

Steps:

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

Apply Formulas with TRANSPOSE Function to Convert Columns to Rows

  • 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


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.

Convert Single Columns to Rows Using Excel Functions (OFFSET, MOD, ROW, INT, MOD & COLUMN)

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))

Convert Single Columns to Rows Using Excel Functions (OFFSET, MOD, ROW, INT, MOD & COLUMN)

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

Convert Single Columns to Rows Using Excel Functions (OFFSET, MOD, ROW, INT, MOD & COLUMN)

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

Convert Single Columns to Rows Using Excel Functions (OFFSET, MOD, ROW, INT, MOD & COLUMN)

  • 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))

Formulas with Combination of Excel Functions (INDIRECT, ADDRESS, ROW & COLUMN) to Convert One Column to Rows

  • 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.

Formulas with Combination of Excel Functions (INDIRECT, ADDRESS, ROW & COLUMN) to Convert One Column to Rows

  • 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.

Things to Remember

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

#SPILL!

  • 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

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo