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.

launching power query table to show all combinations of 5 columns in Excel

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

Add a column in power query dialog to make combinations of all 5 columns

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

Write down suitable properties to make the 5 columns combinations

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

Use Close & Load to command to save the query table

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

Saved Power Query Data table in Excel

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

Use the Merge option to combine two power query table to create combinations of all 5 columns

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

Adding first table in Merge dialog box

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

Make combinations for both columns

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

Save the first combination result for getting all the combinations of all 5 columns

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

Remove the Custom column after completing the combination operation for all 5 columns

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

Excel all combinations of 5 columns

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

Read More: How to Generate All Possible Combinations of a Set of Numbers 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.

Launching the VBA developer dialog box

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

Inserting a new module to show all the combinations of all 5 columns in Excel

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

Opening Macros to run the visual basic code to get the combinations of all the 5 columns in Excel

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

Embedding VBA Code to Show the Combinations of All 5 Columns in Excel

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


Download Practice Workbook

Download this practice workbook for practice while you are reading this article.


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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo