You might frequently need to consolidate two or more columns into one. However, to meet your needs, you must combine them into one column. You could believe it to be a difficult task. Keep your composure and read this article. In this post, we’ll demonstrate how to consolidate multiple columns into one in Excel. So let’s get going.

## Download Practice Workbook

You can download the practice workbook from the following download button.

## 2 Ways to Consolidate Columns in Excel

There are several options for merging columns in Excel, and I’ll explain to readers three alternative ways you can consolidate double or multiple columns into one. Those formulae will be demonstrated below.

### 1. Consolidate Columns Horizontally in Excel

Instructions for consolidating columns are just below, as well as extra examples and explanations of combining columns.

**1.1 Using Flash Fill**

- First of all, enter two names manually and then select both of them by Excel cursor.

- Now, like the below image, drag the fill handle down to the last cell.

- Notice that the selected two cells have copied to the last. Now by following the image we will choose
**Flash Fill**to consolidate the columns.

- In
**column D**First name and Last name has been merged using**Flash Fill.**

**1.2 Using Ampersand (&) Operator**

- Now we can try to consolidate data in 2 columns using the ampersand (&) by entering the following formula:

`=B5&" "&C5`

**Here,**

**B5&” “&C5= B5 **and **C5 **will be merged with a space defined in between two names by** &” “&**

- Drag the fill handle to the final cell, once more following the example in the figure below.

- Finally, our final data has emerged by consolidating the previous two columns.

**1.3 Using CONCATENATE Function**

- Just, input the following formula to attempt to combine data across two columns using the
**CONCATENATE function****:**

`=CONCATENATE(B5," ",C5)`

- After entering a formula, by following the example in the image below drag the fill handle to the last cell.

- In the end, by combining the two prior columns, our final data was revealed.

**1.4 Using TEXTJOIN Function**

- Simply use the formula below in
**cell D5**to use the**TEXTJOIN function**to attempt to merge data from two columns:

`=TEXTJOIN(" ",TRUE,B5,C5)`

- Now drag the fill handle to the last cell after entering a formula by imitating the procedure in the example in the image below.

- Our complete data was finally displayed by merging the two previous columns.

**Read More:** **How to Consolidate Data from Multiple Columns in Excel (7 Easy Ways)**

**Similar Readings**

**How to Consolidate Sheets in Excel (2 Suitable Examples)****Consolidation of Financial Statements in Excel (2 Easy Examples)****Consolidate Rows and Sum Data in Excel (4 Effective Methods)****Excel Consolidate Data from Multiple Worksheets in a Single Worksheet****[Fixed]: Consolidation Reference Is Not Valid in Excel (with Quick Fix)**

### 2. **Consolidate Columns Vertically in Excel**

We’ve placed certain goods into two columns here. Our objective is to combine columns into one list in order to get them all into one column. In our first way, I’ll do the task using the **IFERROR**, **INDEX**, and **ROWS** functions.

#### 2.1 Combining IFERROR, INDEX & ROWS Functions

- We can write the following formula in Cell
**D5**and then just click the**Enter**button.:

`=IFERROR(INDEX($B$5:$B$10,ROWS(D5:$D$5)),IFERROR(INDEX($C$5:$C$11,ROWS(D5:$D$5)-ROWS($B$5:$B$10)),""))`

**🔎**** How Does the Formula Work:**

**ROWS($B$5:$B$10)**

The **ROWS** function returns the number of rows in the range B5:B10:**Result:** {6}

**ROWS(D5:$D$5)**

D5:$D$5 is unique, it expands as the formula is copied to cells below. In Cell D5 it returns:**Result:** {1}

**ROWS(D5:$D$5)-ROWS($B$5:$B$9)**

After subtracting, the result is:**Result:** {-5}

**INDEX($C$5:$C$11, ROWS(D5:$D$5)-ROWS($B$5:$B$10))**

Then the **INDEX** function returns the value from the array (C5:C11) as specified by the row number {-5}. As it’s a negative number, the output shows an error:**Result:** {#VALUE!}

**IFERROR(INDEX($C$5:$C$11, ROWS(D5:$D$5)-ROWS($B$5:$B$10)),””)**

The **IFERROR** function checks for an error from the output of the INDEX function. If there is any, then it returns an empty string. In this case, it returns:**Result:** {}

**INDEX($B$5:$B$10, ROWS(D5:$D$5)**

Here also the **INDEX** function returns the value from the array for row number 1:**Result:** {Product-1}

**IFERROR(INDEX($B$5:$B$9, ROWS(D5:$D$5)), IFERROR(INDEX($C$5:$C$9, ROWS(D5:$D$5)-ROWS($B$5:$B$9)),””))**

Finally, The **IFERROR** function passes the calculation to the next portion when a portion starts to return errors. It is also right for the second portion, when errors return the calculation continues with the third portion. So finally it returns:**Result:** {Product-1}

- After that, drag the Fill Handle icon over the cells under Cell
**D5**until the formula returns the last data.

**2.2 **Using IF, INDEX, COUNTA & ROW Functions

- Now, to combine columns we can enter the formula in Cell
**D5**and then just click the**Enter**button:

`=IF(B6<>"",INDEX($B$5:$B$1048576,ROW()-COUNTA($B$5:$B$8)),INDEX($C$5:$C$1048576,ROW()-ROW($D$8)))`

**🔎**** Formula Breakdown:**

**ROW()-ROW($D$8)**

The **ROW** function returns the row number of Cell D5. Then the row number of Cell D8 will be subtracted from it. The output is:**Result:** {-3}

*INDEX($C$5:$C$1048576,ROW()-ROW($D$8))*

Then the **INDEX** function returns the value from the array (C5:C1048576) according to the row number specified by the previous piece of the formula. As it is negative so it returns:**Result:** {#VALUE!}

*ROW()-COUNTA($B$5:$B$8)*

It returns the sum after subtracting the non-blank cell numbers of the array (B5:B8) from the row number of Cell D5 and returns:**Result:** {1}

**INDEX($B$5:$B$1048576,ROW()-COUNTA($B$5:$B$8))**

Now the **INDEX** function returns the value from the array (B5:B1048576) according to that number of positions. That is:**Result:** “Product-1”

**IF(B5<>””,INDEX($B$5:$B$1048576,ROW()-COUNTA($B$5:$B$8)),INDEX($C$5:$C$1048576,ROW()-ROW($D$8)))**

The **IF** function will check Cell B5 whether it is empty or not. If so, then it will show the result according to the first INDEX operation, and if not then it will show the result of the second INDEX operation. Finally, it will return as**Result:** “Product-1”

- Now drag the fill handle down to fill the series.

#### 2.3 Using a VBA Code

Columns may be combined into a single list using **VBA macros** if you enjoy coding. In this case, Column **B** will be created by merging Columns **B** and **C**.

**📌**** Steps:**

- Suppose we have data like the image below. Now, we will follow the steps to open Visual Basics:
**Developer**>>**Visual Basic**.

- A VBA window will appear.
- Then enter the supplied codes.

We are grateful to **extendoffice.com** for this code.

```
Sub CombineColumns1()
Dim x As Range
Dim i As Integer
Dim LastRow As Integer
Dim zTxt As String
On Error Resume Next
zTxt = Application.ActiveWindow.RangeSelection.Address
Set x = Application.InputBox("please select the data range", "Merged List", zTxt, , , , , 8)
If x Is Nothing Then Exit Sub
LastRow = x.Columns(1).Rows.Count + 1
For i = 2 To x.Columns.Count
Range(x.Cells(1, i), x.Cells(x.Columns(i).Rows.Count, i)).Cut
ActiveSheet.Paste Destination:=x.Cells(LastRow, 1)
LastRow = LastRow + x.Columns(i).Rows.Count
Next
End Sub
```

- After that Hit the
**Run**button. - After running the code a pop-up will appear on the screen. Mow we will select the data range for our example the range is
**B5:C8**. - And now press
**OK**.

- After that, the data will look like the below image.

**Read More:** **How to Do Consolidation in Excel (2 Useful Cases)**

## Conclusion

Follow these steps and stages to understand the topic of consolidating columns in Excel. You are welcome to download the workbook and use it for your own practice. If you have any questions, concerns, or suggestions, please leave them in the comments section of our blog **ExcelDemy**.

**Related Articles**

**How to Consolidate Data from Multiple Rows in Excel (4 Quick Methods)****How to Automate Consolidation in Excel (with Easy Steps)****Consolidate Function for Text Data in Excel (with 3 Examples)****How to Consolidate Data in Excel from Multiple Workbooks (2 Methods)****How to Remove Consolidation in Excel (2 Handy Methods)****How to Use Grouping and Consolidation Tools in Excel (5 Easy Examples)**