How to Consolidate Columns in Excel (Vertically and Horizontally)

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:

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

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

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

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

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