Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.

Consolidate Columns Horizontally in Excel

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

drag the fill handle downward

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

Choose Flash Fill to consolidate the columns

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

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 &” “&

Consolidate Columns Horizontally in Excel

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

Drag the fill handle to the final cell

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

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)

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.

drag the fill handle to the last cell

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

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)

In cell D5 insert the TEXTJOIN Function

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

Now drag the fill handle to the last cell

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

Complete data was finally displayed

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


Similar Readings


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)),""))

Consolidate Columns Vertically in Excel

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

Drag the Fill Handle icon over the cells under Cell D5


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

Consolidate Columns Vertically in Excel

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

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.

Steps to open Visual Basics

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

We are grateful to extendoffice.com for this code.

Hit the Run button like in the image

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.

consolidate columns in Excel vertically

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

After that, the data will look like the 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

Al Ikram Amit

Al Ikram Amit

Hi there. I am Amit, completed my study from BUET. Currently, I am working as an Excel & VBA Content Developer at ExcelDemy. Now you can see my articles in ExcelDemy blog.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo