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.

**Contents**hide

## ๐ 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.

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

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

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

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