3 Ways to Convert Multiple Rows to Single Column in Excel

Generating data using various formulas is quite common in Microsoft Excel. Usually, cells are organized as a table with rows and columns. In some cases, you might need to set all your data from multiple rows in a single column in Excel. The TRANSPOSE function can switch columns and rows. But to convert all the data into one single column is a difficult task. This article will help you to convert multiple rows of your Excel sheets to a single column. Whether you want to arrange the column starting from rows or columns, we have got you covered in three methods. You will get to use these easy tricks with the help of a few clicks. All the methods have step-by-step instructions with a figurative description.

📁 Download Excel File

Download the Excel file below.

Learn to Convert Multiple Rows to a Single Column in Excel with These 3 Approaches

The article is going to introduce three ways to convert multiple rows to single column in Excel. The methods will provide function-based and macro-based solutions elaborately. The solutions will be covering functions like OFFSET, ROUNDUP, ROWS, MOD, IFERROR, INDEX, INT, and COLUMNS. Using Macro-Enabled Workbook, one method will discuss VBA code and its application. Therefore, users will get a walkthrough of these solutions to one of the complex problems. A dataset of vaccination status will be converted in the following methods to help you understand.

Excel has a variety of functions. These are applicable to many sections of analytical operations. Combining these functions can enable more access to operations. In this method, four functions have been combined to get a specific result. Here, we use a combination of OFFSET, ROUNDUP, and MOD functions.

⬇️⬇️ STEPS ⬇️⬇️

• First, select the cell from where the column will start. For the image below, the cell is F5.
• Second, copy the following formula on the Formula Bar.

• Hit the Enter key.

• Use the Fill Handle icon to copy the formula till the data is available to convert.

📕 Read More: 4 Quick Ways to Merge Rows with Same Value in Excel

There are ways to customize data by merging another set of functions. For the conversion only to a single column, you can use this method. Customizing it, you can try even more complex data arrangements. Here, we use a combination of IFERROR, INDEX, INT, ROW, COLUMNS, and MOD functions.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, select the cell from where the column will start. For the image below, the cell is F5.
• Next, copy the following formula on the Formula Bar.

• Now, hit the Enter key.

• Finally, use the Fill Handle icon to copy the formula till the data is available to convert.

📕 Read More: 6 Ways to Combine Multiple Rows in One Cell in Excel

Using VBA, you can employ Macro code to apply on all the existing worksheets. This is the only process by which you can convert all your cells of one sheet into one column of a new sheet. Moreover, it is also the only method that will convert your data into a single column, by columns; rather than by rows like previous methods.

⬇️⬇️ STEPS ⬇️⬇️

• Initially, press Alt+F11 to open Visual Basic Application (VBA).
• Now, select the Module option from the Insert You can also press Alt+I+L for the action.
• Write the following code on the Module option.

Sub Convert_Multiple_Rows_to_Single_Column()
Dim XData As Range
Dim XSh, NewXSh As Worksheet
Dim Z, xLcol, xStr, xStc As Long
Set XSh = ThisWorkbook.Sheets("VBA")
xStr = ActiveCell.Row
xStc = ActiveCell.Column
xLcol = XSh.Cells(xStr, Columns.Count).End(xlToLeft).Column
Application.ScreenUpdating = False
Set NewXSh = ThisWorkbook.Sheets.Add(After:=XSh)
XSh.Activate
With XSh
For Z = xStc To xLcol
Set XData = .Range(Cells(xStr, Z), Cells(Rows.Count, Z).End(xlUp))
XData.Copy NewXSh.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Next Z
End With
Application.ScreenUpdating = True
MsgBox "Done"
End Sub

• You should see the window like the image below.

• Now, go back to the workbook window and choose cell B5.
• Press Alt+F8 or choose the Macros option from the Developer tab.
• In this stage, the Macro window will pop up to confirm the macro. Click on Run.

• A new sheet Sheet 1 has been generated with all the data arranged in one single column from the VBA. You should get an interface like the following picture.

📄 Important Notes

🖊️  You should change the ranges according to your dataset, before applying the formula.

🖊️  For the VBA method, you might need to change the sheet name within the provided code. You can change it according to whatever you require.

📝 Takeaways from This Article

The article allows the readers to understand the variety of options available to convert multiple rows to a single column in Excel.

📌  Using the OFFSET function over returns from ROUNDUP, ROWS, and MOD functions are convenient using cell references.

📌  A combination of IFERROR, INDEX, INT, ROW, and COLUMNS functions can be used. This formula can arrange the single column precisely with all the details added.

📌  With the help of VBA a certain Macro can be run on the cell ranges. This method arranges the column by the source columns along with their formats.

Conclusion

To convert multiple rows to a single column in Excel, two combinations from OFFSET, ROUNDUP, ROWS, MOD, IFERROR, INDEX, INT, and COLUMNS functions can be used. Using VBA, codes can help out with the problem as well. In case of further queries, readers are requested to leave a comment for the author. The author will try their best to come up with a suitable solution. Follow ExcelDen to get more access to solutions regarding your Excel problems.

Related Articles

• 3 Ways to Combine Duplicate Rows and Sum Values in Excel
• Merge Duplicate Rows in Excel (3 Ways)
• 3 Suitable Ways to Combine Rows in Excel with Same ID
• 6 Suitable Ways to Convert Multiple Rows to Single Row in Excel