How to Apply All Combinations of 3 Columns in Excel

Method 1 – Use Power Query

Steps:

• Create 3 separate tables for 3 columns. Select the first column and press Ctrl+T.

• Create Table window will appear. Press Enter.

• The Number column is now turned into a table.

• Make the Color column a table. Select the Color column.

• Press Ctrl+T.
• Press Enter.

• Turn the last column into a table.

• Create a connection for each column.
• Go to the Data tab and select From Table/Range.

• Power Query Editor window will open with the data of the first column.

• Right-click Table 1 and click Rename.

• Rename the table. We renamed it Number.

• In the Home tab, click Close & Load.
• Select Close & Load To.

• A dialogue box will open. Select Only Create Connection and press OK.

• Follow the same procedure to add the Color column in Power Query Editor.

• Create a connection for this column as well.

• Follow all the steps for the Category column to add it to Power Query Editor.

• Create a connection in the Import Data dialogue box.

• Create temporary columns.
• Click Get Data in the Data, and select Launch Power Query Editor.

• Select the Number, go to Add Column, and click Custom Column.

• Create a column name for the new helper column. We named it Temporary.
• In the custom column formula, write “= 1”.
• This column name and formula must be the same for all 3 columns.

• Press OK, and you will see a temporary column beside the Number column.

• Create a temporary column beside the Color column.

• Follow the same procedure for the Category column.

• Merge the columns.
• Click Merge Queries. Select Merge Queries as New.

• A pop-up box will appear. In the pop-up, choose the Number and the Color column and select the Temporary column.
• Select Full Outer (all rows from both) in the Join Kind option.
• Click OK.

• The Number and the Color table are merged and saved as Merge 1.

• Merge the Category table with the Merge 1 table.
• Choose Merge 1 and Category and select the Temporary column.
• Select Full Outer (all rows from both) in the Join Kind option and hit Enter.

• See that all three columns are merged with the Temporary column.

• Find all the combinations of these 3 columns.
• Click the Color drop-down and select the Color option to expand the combinations.
• Uncheck the box. Use the original column name as a prefix.
• Press Enter.

• Click on the Color 1 drop-down.
• Uncheck the box of null.

• Follow the same steps for Category to expand the combinations and remove null rows.

• Unchecking null removes the empty rows in the combinations.

• Have all 24 combinations of the 3 columns.

Method 2 – Combination of IFERROR, INDEX, and COUNTA Functions

Steps:

• Create a column named All Combinations.
• Type the following formula in cell E5.

• Press Enter, and see the first combination.

• Drag the bottom right corner of cell E5 to copy the formula to all cells.

• Once all the combinations are complete, you’ll start getting empty cells. Stop dragging when you get the first empty cell.

• Find all the combinations of the 3 columns in your Excel sheet.

Method 3 – Run a VBA Code

Steps:

• Create a column named All Combinations.

• Press Alt+F11 to open the Microsoft Visual Basic window.

• Select Module in the Insert tab to open a module.

• Copy the following VBA code in the module.

Sub AllCombinations()
    i = 5
    For Each cella In ThisWorkbook.ActiveSheet.Range("B5:B8")
    .SpecialCells (xlCellTypeConstants)
    For Each cellb In ThisWorkbook.ActiveSheet.Range("C5:C7")
    .SpecialCells (xlCellTypeConstants)
    For Each cellc In ThisWorkbook.ActiveSheet.Range("D5:D6")
    .SpecialCells (xlCellTypeConstants)
    ThisWorkbook.ActiveSheet.Range("E" & i).Value = cella.Value
    ThisWorkbook.ActiveSheet.Range("F" & i).Value = cellb.Value
    ThisWorkbook.ActiveSheet.Range("G" & i).Value = cellc.Value
    i = i + 1
    Next
    Next
    Next
End Sub

• Press F5 to run the VBA code.
• Go back to the worksheet and get all the combinations of three columns.

Things to Remember

• Don’t forget to provide proper cell references, or you won’t get the desired results.
• Ensure the temporary column in the Power Query has the same name for all 3 columns.

Download Practice Workbook

Download this practice workbook while you are reading this article.

Related Articles

• How to Create All Combinations of 4 Columns in Excel
• How to Show All Combinations of 5 Columns in Excel
• How to Create All Combinations of 6 Columns in Excel
• How to Sum All Possible Combinations in Excel
• How to Find Combinations Without Repetition in Excel

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