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.

Multiple rows to single column dataset


approach

1. Combination of OFFSET, ROUNDUP & MOD Functions

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.
=OFFSET($B$5,ROUNDUP(ROWS($1:1)/3,0)-1,MOD(ROWS($1:1)-1,3))
  • Hit the Enter key.

OFFSET and other functions to convert multiple rows to single column

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

๐Ÿ”จ Formula Breakdown

OFFSET($B$5,ROUNDUP(ROWS($1:1)/3,0)-1,MOD(ROWS($1:1)-1,3))

๐Ÿ‘‰ย  ROWS calls the array number. In the following range, you can get row numbers.

๐Ÿ‘‰ย  MOD returns the remainder of the division by 3.

๐Ÿ‘‰ย  ROUNDUP returns the rounded number away from zero. It helps you to get unnecessary fractional values.

๐Ÿ‘‰ย  OFFSET gives you a reference on a chosen range of rows and columns.

๐Ÿ‘‰ย  Finally, the formula brings the Name from B5.

๐Ÿ“• Read More: 4 Quick Ways to Merge Rows with Same Value in Excel


approach

2. Applying Combined Formula

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.
=IFERROR(INDEX(B$5:D$8,INT((ROW(A1)-1)/COLUMNS(B$5:D$8))+1,MOD(ROW(A1)-1,COLUMNS(B$5:D$8))+1),โ€โ€)
  • Now, hit the Enter key.

INSERT and other functions - multiple rows to single column

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

๐Ÿ”จ Formula Breakdown

IFERROR(INDEX(B$5:D$8,INT((ROW(A1)-1)/COLUMNS(B$5:D$8))+1,MOD(ROW(A1)-1,COLUMNS(B$5:D$8))+1),โ€โ€)

๐Ÿ‘‰ย  ROW calls the row number. You should use the function multiple times in this formula.

๐Ÿ‘‰ย  COLUMNS returns the array number of columns for reference.

๐Ÿ‘‰ย  INT returns the integer number. It helps you to get rid of extra fractional values.

๐Ÿ‘‰ย  INDEX gives you a reference at the intersection of rows or columns on a chosen range.

๐Ÿ‘‰ย  IFERROR neglects the error. It shows the result that is available ignoring the error.

๐Ÿ‘‰ย  Finally, the formula brings the Name from B5.

๐Ÿ“• Read More: 6 Ways to Combine Multiple Rows in One Cell in Excel


approach

3. Employing VBA Code

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.

VBA - multiple rows to single column

  • 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

(Visited 144 times, 1 visits today)
Farihat

Farihat

An Excel enthusiast who wants to share and loves to solve Excel problems. I try to explore available options to guide readers to the maximum ways how they can solve. The world is limitless, and so are the problems in it. Let's help each other to grow. I would love to hear feedback and queries from readers. You can visit my LinkedIn for further information.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo