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.

**đź‘‡**** How Does the Formula Work:**

**âžĄ**** 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.

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