# How to Show All Combinations of 5 Columns in Excel

Get FREE Advanced Excel Exercises with Solutions!

Combination is a common task in our day-to-day professional life in different aspects. In Microsoft Excel, we can easily show all the combinations for several columns. In this article, we are going to demonstrate 2 simple ways to show all combinations of 5 columns in Excel. If you are also curious about it, download our practice workbook and follow us.

## How to Show All Combinations of 5 Columns in Excel: 2 Easy Ways

To demonstrate the approaches, we consider a dataset with 2 rows and 5 columns. So, we can say that our dataset is in the range of cells B5:B6.

ðŸ“š Note:

All the operations of this article are accomplished by using the Microsoft Office 365 application.

### 1. Using Power Query Table

In this method, we will use the Excel Power Query to show all the combinations of 5 columns in Excel. The steps of this method are shown below:

ðŸ“Œ Steps:

• First of all, select the range of cells B4:B6.
• Now, click on the From Table/Range option from the Get & Transform Data group in the Data tab.

• As a result, a small dialog box called Create Table will appear.
• Then, check on the My table has headers and click OK.

• Another dialog box called Power Query Editor will appear.
• After that, in the Add Column tab, click on the Custom Column option.

• The Custom Column dialog box will appear.
• In that box, input a column name. We keep it as Custom.
• Afterward, in the Custom column formula box, write down a constant number. Here, we write down 1.
• At last, click OK.

• Next, in the Home tab, click on the drop-down arrow of the Close & Load option and choose the Close & Load to command.

• As a result, the Import Data dialog box will appear.
• Now, check the Only Create Connection option and click OK.

• You will see the table will show in the Queries & Connections window.

• Similarly, follow the same procedure for the rest of the 4 columns.

• After that, in the Data tab, click on the drop-down arrow of the Get Data from the Get & Transform Data group.
• Then, choose the Combine Queries > Merge option.

• The Merge dialog box will launch on your device.
• From the first drop-down box, click on Table1.
• As a result, it will show below the empty section.
• Next, click on the Custom column.

• Similarly, follow the same procedure for the second drop-down section.
• Then, click OK.

• The Power Query Editor will show up again.
• After that, click on the expansion icon from the Table2 column.
• Now, uncheck the Custom option and click OK.

• Afterward, select the Custom column and drag it to the last.

• Finally, in the Home tab, click on the drop-down arrow of the Close & Load option and choose the Close & Load to command.

• The Import Data dialog box will appear.
• Then, check the Only Create Connection option and click OK to save the first merge table.

• Similarly, follow the same procedure to merge the previous merge with the next table. For example, in the second case, in the Merge dialog box, the first merge table will be in the first empty box, and Table3 will be in the second box. Next, the second merge table will be in the upper box, and Table4 will be in the lower box. At last, Table5 will be in the lower.
• In the very last merge, select the Custom column and click on Remove Column from the Manage Columns group, located in the Home tab.

• In the end, click on the Load & Close option to save the combination result.
• You will notice that a new sheet will appear, and all of the combinations of our 5 columns will be there.

Thus, we can say that our procedure works perfectly, and we are able to show all the combinations of 5 columns in Excel.

### 2. Embedding VBA Code

Writing a VBA code can also help us to show the combination of 5 columns in the same Excel spreadsheet. We are going to use our previous dataset and the result will be in column H. The process is explained below step by step:

ðŸ“Œ Steps:

• To start the approach, go to the Developer tab and click on Visual Basic. If you donâ€™t have that, you have to enable the Developer tab. Or You can also press â€˜Alt+F11â€™ to open the Visual Basic Editor.

• As a result, a dialog box will appear.
• Now, in the Insert tab on that box, click the Module option.

• Then, write down the following visual code in that empty editor box.

``````Sub List_All_Combinations()
Dim Column_1, Column_2, Column_3, Column_4, Column_5 As Range
Dim Result  As Range
Dim X As String
Dim Col_1, Col_2, Col_3, Col_4, Col_5 As Integer
Dim a, b, c, d, e As String
Set Column_1 = Range("B5:B6")
Set Column_2 = Range("C5:C6")
Set Column_3 = Range("D5:D6")
Set Column_4 = Range("E5:E6")
Set Column_5 = Range("F5:F6")
X = "-"
Set Result = Range("H5")
For Col_1 = 1 To Column_1.Count
a = Column_1.Item(Col_1).Text
For Col_2 = 1 To Column_2.Count
b = Column_2.Item(Col_2).Text
For Col_3 = 1 To Column_3.Count
c = Column_3.Item(Col_3).Text
For Col_4 = 1 To Column_4.Count
d = Column_4.Item(Col_4).Text
For Col_5 = 1 To Column_5.Count
e = Column_5.Item(Col_5).Text
Result.Value = a & X & b & X & c & X _
& d & X & e
Set Result = Result.Offset(1, 0)
Next
Next
Next
Next
Next
End Sub``````
• After that, press â€˜Ctrl+Sâ€™ to save the code.
• Now, close the Editor tab.
• Afterward, in the Developer tab, click on the Macros option.

• A new dialog box called Macros will appear. Select the List_All_Combinations option.
• Finally, click on the Run button to run this code.

• You will see that all the combinations will be in our desired column.

Finally, we can say that our visual code worked successfully, and we are able to show all the combinations of 5 columns in Excel.

## Conclusion

Thatâ€™s the end of this article. I hope that this article will be helpful for you and you will be able to show all the combinations of 5 columns in Excel. Please share any further queries or recommendations with us in the comments section below if you have any other questions or suggestions.

## Related Articles

<< Go Back toÂ Excel COMBIN Function | Excel Functions | Learn Excel

## What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Soumik Dutta

Soumik Dutta, having earned a BSc in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, plays a key role as an Excel & VBA Content Developer at ExcelDemy. Driven by a profound passion for research and innovation, he actively immerses himself in Excel. In his role, Soumik not only skillfully addresses complex challenges but also demonstrates enthusiasm and expertise in gracefully navigating tough situations, underscoring his unwavering commitment to consistently deliver exceptional, high-quality content that... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF