If you want to combine multiple columns into one list, normally, you can copy and paste the columns’ data one by one into the specified column. But, if the dataset is long then it won’t be feasible to do it. So here in this tutorial, I’ll introduce you to some easy methods to combine columns into one list in** Excel**.

**Table of Contents**hide

**Download Practice Book**

You can download the free Excel template from here and practice on your own.

**4 Easy Ways to Combine Columns into One List in Excel**

**Method 1: Use IFERROR, INDEX, and ROWS Functions Together to Combine Columns into One List in Excel**

Let’s get introduced to our dataset first. Here, I have placed some cloth items in two columns. Our goal is to bring them in one column that means combining columns into one list. In our very first method, I’ll use the **IFERROR**, **INDEX****, **and **ROWS **functions to do the operation. The **IFERROR **function is applied to return a result or message when an error is found or to return the output of the expression. The **INDEX **function returns a value or the reference to a value from within a data range. The **ROWS **function returns the number of rows in a reference or array.

**Step 1:**

**➤** Write the following formula in **Cell D5**:

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

**➤** Then just hit the **Enter **button.

**Step 2:**

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

Here it is! We have successfully combined the products in two columns into a single column.

**👇**** Formula Breakdown**

**➥**** ROWS($B$5:$B$8)**The

**ROWS**function returns the number of rows in the range

**B5:B8**:

**{4}**

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

**D5:$D$5**is special, it expands as the formula is copied to cells below. In

**Cell D5**it returns:

**{1}**

**➥**** ROWS(D5:$D$5)-ROWS($B$5:$B$8)**After subtracting, the result is:

**{-3}**

**➥**** INDEX($C$5:$C$7, ROWS(D5:$D$5)-ROWS($B$5:$B$8))**Then the

**INDEX**function returns the value from the array (

**C5:C7**) as specified by the row number {-3}. As it’s a negative number, so the output shows an error:

**{#VALUE!}**

**➥**** IFERROR(INDEX($C$5:$C$7, ROWS(D5:$D$5)-ROWS($B$5:$B$8)),””)**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:

**{}**

**➥**** INDEX($B$5:$B$8, ROWS(D5:$D$5)**Here also the

**INDEX**function returns the value from the array for row number

**1**:

**{Hat}**

**➥ IFERROR(INDEX($B$5:$B$8, ROWS(D5:$D$5)), IFERROR(INDEX($C$5:$C$7, ROWS(D5:$D$5)-ROWS($B$5:$B$8)),””))**Finally, The

**IFERROR**function passes the calculation to the next portion when the first 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:

**“Hat”**

**Method 2: Combine IF, ROW, COUNTA, INDEX Functions to Join Columns into One List in Excel**

In this method, we’ll use another combination of functions, which are: the **IF**, **ROW**, **COUNTA**, and **INDEX** functions. The **IF **function returns one value if a condition is true and another value if it’s false. The **ROW **function will give the row number of any cell and the **COUNTA **function counts the number of non-blank cells.

**Step 1:**

**➤** In **Cell D5** type the given formula-

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

**➤** Now press the **Enter **button.

**Step 2:**

**➤** Then drag the **Fill Handle** icon to copy the formula.

**👇**** 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:

**{-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:

**{#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:

**{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 position. That is:

**“Hat”**

**➥**** 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-

**“Hat”**

**Method 3: Apply INDEX, INT, COLUMNS, MOD, and ROW Functions to Merge Columns into One List in Excel**

We can use the **INDEX**, **INT**, **COLUMNS**, **MOD**, and **ROW **functions together to combine columns into one list. The **INT **function returns the numeric integer equivalent from a given expression. The **COLUMNS **function returns the number of columns in a reference or array. The **MOD **function returns the remainder of two numbers after division.

**Step 1:**

**➤** Type the formula in **Cell D5**–

`=INDEX($B$5:$C$7,1+INT((ROW(B5)-5)/COLUMNS($B$5:$C$7)),MOD(ROW(B5)-5+COLUMNS($B$5:$C$7),COLUMNS($B$5:$C$7))+1)`

**➤** Then click the **Enter **button to get the result.

**Step 2:**

**➤** To copy the formula for the other cells, just use the **AutoFill** option.

**👇**** Formula Breakdown**

**➥**** COLUMNS($B$5:$C$7)**It will give the number of columns from the array (

**B5:C7**) that will return as-

**{2}**

**➥**** ROW(B5)**The

**ROW**function will return the row number of

**Cell B5**. That is-

**{5}**

**➥**** MOD(ROW(B5)-5+COLUMNS($B$5:$C$7),COLUMNS($B$5:$C$7))**The

**MOD**function will give the remainder after the calculation of

**ROW**and

**COLUMNS**functions and it will return as-

**{0}**

**➥**** INT((ROW(B5)-5)/COLUMNS($B$5:$C$7))**The

**INT**function will then return the integer value by rounding it after the calculation. That will return as-

**{0}**

**➥****INDEX($B$5:$C$7,1+INT((ROW(B5)-5)/COLUMNS($B$5:$C$7)),MOD(ROW(B5)-5+COLUMNS($B$5:$C$7),COLUMNS($B$5:$C$7))+1)**Finally, the

**INDEX**function will return values from the range (

**B5:C7**) based on the calculated row number and column number that will return as-

**“Hat”**

**Method 4: Embed Excel VBA to Combine Columns into One List in Excel**

If you like to code then it is possible to combine columns into one list by using **VBA Macros**. Here, we’ll combine **Columns B** and** C** into **Column B**.

**Step 1:**

**➤**** Right-click** on your sheet title then select **View Code** from the **context menu.**

A **VBA **window will appear.

**Step 2:**

**➤** Later, type the given codes.

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

**➤** Now press the **Play icon** to run the codes.

A dialog box named “**Merged List**” will open up to select the data range.

**Step 3:**

**➤** Then select the data range

**➤** Finally, press **OK**

Now take a look at the image below that the products have listed in one column.

**Things to Remember**

- The
**VBA**process is irreversible. So keep a back up beforehand. - The cell references in the formulas used need to be very precise. Otherwise the formulas may not work or return unexpected values.
- Every demonstration shown in those picture was made with
**Excel 365**. So, results may vary for other versions.

**Conclusion**

I hope all of the methods described above will be good enough to combine columns into one list. Feel free to ask any questions in the comment section and please give me feedback.

In method 2, I would like the cell reference in the last row funtion to autocalculate based on the length of the first column.

So in ➥ ROW()-ROW($D$8)

I would like the “8” to be calculated (essentially using the counta value from earlier)

Possible? I have varying length pivot tables that need combining. Thanks.

Hello DANIEL,

Yes, it’s possible to do that using the COUNTA function based on the first column. For that, use the following formula-

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

➥

`ROW()-COUNTA($B$5:$B$8)-4`

Here, 4 is subtracted based on the length of the first column to return 1 as the output of this portion. So for your own dataset, modify the value according to the length of your first column.

Hello, how would method 2 look like if i want to combine 3 columns.

Hello EYAD,

Thanks for your feedback. It’s possible to combine 3 columns using the 2nd method after a little bit modification of the formula.

I added more 4 products in column D and then applied this formula in Cell E5:

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

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

Here, 11 is used based on the length of the second column.

Hi, thank you for this article. Can you modify #3 so I can have the merged-list to show all the column 1 one before starting listing on column 2 then column 3 and so on. At this time #3 does not behave like #1 and #2. Thank you in advance!

Hello MICHAEL,

Thanks for your inquiry. Actually, the formula used in

Method-1has been updated to use inMethod-3for behaving differently fromMethod-1. InMethod 1we tried to combine two columns by adding another column after the ending of one column. But inMethod-3it was the intent to join 2 columns by adding the cell contents row-wise. If you need to add the columns serially then please follow the previous 2 methods.Thank you

Tanjima Hossain

Thank you for the VBA code. Is it possible to modify the VBA code so the merged-list does not have an empty cells because the row length of the original columns vary. Say column 1 has 20 rows, column 2 has 1 and column 3 has 100 (and I have 50 columns); because the input from the data range will have to say $A$1:$BZ$100, then value of x.Columns(i).Rows.Count is always 100 and the resulting merged-list has a LOT of empty cells. Thank you in advance!

Hello Michael,

Hope you are doing well. Here, I have the following dataset containing

3columns where we have some products. After combining all of the columns into a column we will clean up all of the empty cells.• Write down the following code in a module.

• Press

F5.Then, you will get the following input box.

• Select the range of cells containing texts and press

OK.Later, another input box will appear.

• Select the whole data range containing all the blank cells.

• Press

OK.In this way, we combined all of the columns in the first column and deleted the rest of the cells.

Best Regards

Tanjima Hossain