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}.
Read More: VBA to Transpose Multiple Columns into Rows in Excel
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}
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.
Download Practice Workbook
You can download the practice workbook that we have used to prepare this article.
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
- 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
- How to Transpose Rows to Columns in Excel
- VBA to Transpose Array in Excel
- Transpose Multiple Columns into One Column in Excel
