How to Consolidate Data from Multiple Columns in Excel (7 Easy Ways)

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.


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

Applying Consolidate Feature to Consolidate Data from Multiple Columns

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.

Applying Consolidate Feature to Consolidate Data from Multiple Columns

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.

Applying Consolidate Feature to Consolidate Data from Multiple Columns

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

Applying Consolidate Feature to Consolidate Data from Multiple Columns

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

Applying Consolidate Feature to Consolidate Data from Multiple Columns

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

Applying Consolidate Feature to Consolidate Data from Multiple Columns

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

Use of Combined Functions to Consolidate Data in Excel

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.

Use of Combined Functions to Consolidate Data in Excel

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

Using Ampersand (&) Operator to Consolidate Data in Excel

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.

Using Ampersand (&) Operator to Consolidate Data in Excel

  • 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


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)

Using CONCAT or CONCATENATE Function to Consolidate Data in Excel

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.

Using CONCAT or CONCATENATE Function to Consolidate Data in Excel

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)

Applying TEXTJOIN Function to Consolidate Data in Excel

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.

Applying TEXTJOIN Function to Consolidate Data in Excel


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.

Use of Notepad to Consolidate Data in Excel

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

Use of Notepad to Consolidate Data in Excel

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

Use of Notepad to Consolidate Data in Excel

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

Use of Notepad to Consolidate Data in Excel


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.

Using VBA to Consolidate Data 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.

Using VBA to Consolidate Data in Excel

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

Using VBA to Consolidate Data in Excel

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

Using VBA to Consolidate Data in Excel

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

Using VBA to Consolidate Data in Excel

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

Practice Section


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!


Related Articles

Arin

Arin

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo