Looking for ways to know **how to Consolidate Data **in** Excel **from** Multiple Columns**? Then, this is the right place for you. Sometimes, we need to consolidate data from multiple columns in Excel to do work more precisely. Here, you will find **7** different step-by-step explained ways to **consolidate data **from **multiple columns** in Excel.

**Table of Contents**hide

## Download Practice Workbook

## 7 Ways to Consolidate Data in Excel from Multiple Columns

We can consolidate data from multiple columns in Excel by using some easy ways.

### 1. Applying Consolidate Feature to Consolidate Data from Multiple Columns

In the first method, we will apply **Consolidate Feature** to **consolidate data from multiple columns** in Excel.

Here, we have** 3 datasets** containing the **Name** and **Purchases** of some customers for consecutive **3** months (**January**, **February**, and **March**).

Here, the above data set shows the values of **Purchases** of the customers in **January**.

The above data set shows the values of **Purchases** of the customers in **February**.

Finally, the above data set shows the values of **Purchases** of the customers in **March**.

Now, we will **consolidate** the** Purchase** value of the** 3** months using the **Consolidate** **Feature**. Follow the steps below to do it on your own.

**Steps:**

- First, open a new
**Worksheet**and prepare the worksheet for the**consolidated data**. - Then, select the Cell
**B4**.

- After that, go to the
**Data tab**>> click on**Data Tools**>> select**Consolidate**.

- Then, the
**Consolidate**dialog box will open. - Now, select
**Sum**as**Function**. - Next, in the
**Reference**box insert the Cell range**B4:C14**from the worksheet**January**. - After that, press
**Add**.

- Then, again in the
**Reference**box insert the Cell range**B4:C14**from the worksheet**February**. - After that, press
**Add**.

- Now, in the
**Reference**box insert the Cell range**B4:C14**from the worksheet**February**. - Then, press
**Add**.

- After that, activate the
**Top row**and**Left column**. - Then, press
**OK**.

- Finally, you will find the consolidated
**summed**of**Purchase**values of the three consecutive months applying the**Consolidate Feature**.

**Read More: ****How to Consolidate Data from Multiple Rows in Excel (4 Quick Methods)**

### 2. Use of Combined Functions to Consolidate Data in Excel

Now, we will use the **INDEX**, **INT**, **ROW**, **COLUMNS** and **MOD** functions to **consolidate **data in Excel from multiple columns.

Here, we have a dataset containing the **First Name**, **Last Name** and **State** of some people. We will **consolidate **the date of these columns into a column named as the **Combined Columns**.

Go through the steps to do it on your own.

**Steps:**

- First, select the Cell
**B9**. - Then, insert the following formula.

`=INDEX($C$4:$E$6,1+INT((ROW(A1)-1)/COLUMNS($C$4:$E$6)),MOD(ROW(A1)-1+COLUMNS($C$4:$E$6),COLUMNS($C$4:$E$6))+1)`

**Formula Breakdown**

**COLUMNS($C$4:$E$6)**——> the**COLUMNS**function returns the**column number**.**Output: {3}**

**ROW(A1)**——> the**ROW**function returns the**row number**.**Output: {1}**

**MOD(ROW(A1)-1+COLUMNS($C$4:$E$6),COLUMNS($C$4:$E$6))**——> the**MOD**function returns the remainder of selected numbers by division.**MOD({1}-1+3,3)**——> turns into**Output: {0}**

**INT((ROW(A1)-1)/COLUMNS($C$4:$E$6))**——> the**INT**function returns the**integer**part of a number.**INT(({1}-1)/3)**——> turns into**Output: {0}**

**INDEX($C$4:$E$6,1+{0},{0}+1)**——> the**INDEX**function returns the**value**of a**given location**in a**range**or**array**.**Output: “Jack”**

- After that, press
**ENTER**. - Then, drag down the
**Fill Handle**tool to**AutoFill**the formula for the rest of the cells.

- Finally, you will get the
**Combined Columns**column using the**Combined functions**.

**Read More: ****How to Consolidate Two Sheets into One in Excel (3 Useful Methods)**

### 3. Using Ampersand (&) Operator to Consolidate Data in Excel

We can use the **Ampersand (&) operator** to **consolidate data from multiple columns** in Excel.

Here, we have a dataset containing the** First Name** and **Last Name** of some people. Now, we will **consolidate** these two data into a column using the **Ampersand(&) operator**.

Go through the steps to do it on your own dataset.

**Steps:**

- First, select the Cell
**D5**. - Then, insert the following formula.

`=B5&" "& C5`

Here, we **consolidated** the data of Cell **B5** and Cell **C5** using the **Ampersand(&) operator**. We also added a **space** between the two cells.

- Now, press
**ENTER**. - Then, drag down the
**Fill Handle**tool to**AutoFill**the formula for the rest of the cells.

- Finally, you will get the consolidated
**Name**column using the**Ampersand operator**.

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

**Similar Readings**

**How to Use Grouping and Consolidation Tools in Excel (5 Easy Examples)****Remove Consolidation in Excel (2 Handy Methods)****[Fixed]: Consolidation Reference Is Not Valid in Excel (with Quick Fix)**

### 4. Using CONCAT or CONCATENATE Function to Consolidate Data in Excel

We can also use the** CONCAT** or **CONCATENATE **functions to consolidate data in Excel. These two functions can **consolidate text** from multiple columns following some steps.

**Steps:**

- First, select cell
**D5**. - Next, insert the following formula.

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

Here, in the **CONCATENATE **function, we selected Cell **B5** as **text1**, typed** blank (“”)** as **text2** and selected Cell **C5** as **text3** to **consolidate** the data of Cell **B5 **and Cell **C5**.

- Now, press
**ENTER**. - Then, drag down the
**Fill Handle**tool to**AutoFill**the formula for the rest of the cells.

- Finally, you will get the consolidated
**Name**column using the**CONCATENATE**function.

**Read More: ****How to Automate Consolidation in Excel (with Easy Steps)**

### 5. Applying TEXTJOIN Function to Consolidate Data in Excel

In the fifth method, we will use **the TEXTJOIN function** to **consolidate** the data of the **First Name** and **Last Name** column as the **Name** column in Excel. Follow the steps given below to do it on your own.

**Steps:**

- In the beginning, select Cell
**D5**. - Then, insert the following formula.

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

Here, in the **TEXTJOIN function**, we typed** blank (“”)** as **delimiter** to give space between selected cells.Then selected **TRUE** to **ignore_empty** cells. Next, selectedCell **B5** as **text1** and Cell **C5 **as **text2**.

- After that, press
**ENTER**. - Next, drag down the
**Fill Handle**tool to**AutoFill**the formula for the rest of the cells.

- Finally, you will find the consolidated
**Name**column using the**TEXTJOIN**function.

### 6. Use of Notepad App to Consolidate Data in Excel

Now, we will use **Notepad** to **consolidate data **from** multiple columns** in Excel. Go through the steps given below to do it on your own.

**Steps: **

- First, select Cell range
**B5:C14**. - Then, press
**CTRL+C**to copy the Cell range.

- After that, open a
**Notepad**file. - Then, press
**CTRL+V**to paste the selected range here in the**Notepad**.

- Next, press
**CTRL+H**to open the**Replace**dialog box. - Then, select the space between the two texts in the notepad file and
**Copy**it. - After that,
**Paste**it into the box.

- Then, type
**blank (“”)**in the**Replace**box. - Next, press
**Replace**.

- Now, you will find all the data like the image given below.

- Then,
**Copy**the entire text from the Notepad using keyboard shortcut**CTRL+C**.

- Now,
**Paste**it using the keyboard shortcut**CTRL+V**in Cell**D5**. - Finally, you will find the consolidated
**Name**column using**Notepad**.

### 7. Using VBA to Consolidate Data in Excel

In the final method, we will show you how you can use **VBA** to **consolidate data** from **multiple columns** in Excel.

Follow the steps below to do it on your own.

**Steps:**

- In the beginning, open the
**Developer**tab >> click on**Visual Basic**.

Alternatively, you can use the keyboard shortcut **ALT+F11 **too.

- Then go to
**Insert**>> select**Module**.

- After that, write the following code in your
**Module**.

```
Option Explicit
Sub Consolidate_Multiple_Columns()
Dim Column1 As Range
Dim Column2 As Range
Dim ranges As Range
Dim Row_Number As Integer
Set Column1 = Application.Selection
Set Column1 = Application.InputBox("Insert Range:", _
"Consolidating Multiple Columns", Column1.Address, Type:=8)
Set Column2 = Application.InputBox("Consolidated Column:", _
"Consolidating Multiple Columns", Type:=8)
Row_Number = 0
Application.ScreenUpdating = False
For Each ranges In Column1.Rows
ranges.Copy
Column2.Offset(Row_Number, 0).PasteSpecial _
Paste:=xlPasteAll, Transpose:=True
Row_Number = Row_Number + ranges.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
```

**Code Breakdown**

- First,
**Sub Procedure**as*Consolidate_Multiple_Columns()*. - Then, we declared
**Column1**,**Column2**and**ranges**as**Range**and**Row_Number**as**Integer**. - After that, we set
**Column1**as**Application.Selection**. - Then, we set the
**InputBox**in**Column1**variable to select the**range**that we want to consolidate. - Next, we set another
**InputBox**in**Column2**to select the cell where we want to keep the consolidated values. - Then, we selected
**Row_Number=0**. - Next, we set the
**Screen Updating**to**False**. - After that, we used a
**For**loop to go through the selected**ranges**. - Then,
**copied and pasted**the values one after another for that used**Offset**not to overlap any values. - After that, incremented the
**Row_Number**by using**Row_Number= Row_Number+ranges.Columns.Count**. - Next, we set
**Cut Copy Mode**as**False**and**Screen Updating**as**True**. - Then, click on the
**Save**box to save the**Module**.

- After that, go to the
**Developer**tab >> click on**Macros**.

- Now, this will open the
**Macro**dialog box. - Then, inside the box choose your
**Macro**. Here, we will choose the**Consolidate_Multiple_Column**code. - After that, press
**Run**.

- Now, the
**Consolidating Multiple Column**box will open. - Then, in the
**Insert Range**box insert the Cell range**C4:E6**. - After that, press
**OK**.

- Next, the
**Consolidating Multiple Column**box will open again. - Now, in the
**Consolidated Column**box select the Cell range**B9**. - After that, press
**OK**.

- Finally, you will find the
**Combined Columns**column using**VBA**.

## Practice Section

In the article, you will find an Excel workbook like the image given below to practice on your own.

## Things to Remember

- The
**INT**function will show**#VALUE!**Error when a**value**is inserted as**text**rather than in**number**. - The
**MOD function**will show**#DIV/0**Error if the divisor is 0.

## Conclusion

So, in this article, we have shown you ways to **consolidate data** from **multiple columns** in Excel. I hope you found this article interesting and helpful. If something seems difficult to understand, please leave a comment. Please let us know if there are any more alternatives that we may have missed. And, visit **ExcelDemy** for many more articles like this. Thank you!