How to generate all combinations of 3 or multiple columns in Excel?
When working with Excel, you may encounter situations where you need to produce every possible combination from data across three or more columns, just as demonstrated in the screenshot below. This requirement often arises in tasks such as product mix generation, scheduling, options analysis, or any process where the pairing of data across several categories is necessary. Manually creating all combinations would be highly time-consuming and prone to error. Therefore, using a structured approach within Excel can save you significant time and effort, ensuring accuracy when compiling comprehensive lists of options.
Generate all combinations based on 3 columns of data by using a formula
Generate all combinations based on 3 or multiple columns of data by using VBA code
Generate all combinations based on 3 or multiple columns of data by using an awesome feature
Generate all combinations based on 3 columns of data by using a formula
Excel formulas can be used to automatically list all combinations from three data columns. This approach is effective for moderate data volumes and does not require any add-ins or programming knowledge. The solution is most suitable when your data sets are not excessively large, as very extensive lists might result in heavy calculation loads.
1. Select the cell where you want your combinations to begin—for instance, D2—and enter or paste the following formula directly into it:
Parameter explanations: In this formula, A2:A4, B2:B6, C2:C5 represent the ranges of your source data for each respective column. You may adjust these references so that they match the actual position and extent of your data.
2. Press Enter to confirm the formula. Next, use the fill handle—by selecting the first cell with the formula and dragging the small square at its lower-right corner—downwards through the column. Continue until empty cells appear, which indicates that all unique combinations have been generated and no further results remain. If you accidentally drag too far, blank rows will display, and you can delete them for a cleaner result.
Tips and error reminders: This formula approach is best for up to several hundred combinations. For very large data sets, calculation speed may decrease. If you see any error messages, double-check your cell references and ensure that there are no blank cells in your source ranges. When copying the formula, ensure you reference your correct data range and adjust the formula as needed.
Generate all combinations based on 3 or multiple columns of data by using VBA code
While using formulas is convenient for three columns, editing them to support more columns can quickly become complex and laborious. For such cases, utilizing VBA code offers both flexibility and automation, especially when dealing with more than three columns or larger data sets. This solution is ideal if you need to repeatedly generate combinations or customize your output format.
1. Open the Microsoft Visual Basic for Applications window by pressing ALT + F11 on your keyboard.
2. In the VBA window, click Insert > Module, then copy and paste the following code into the blank Module:
VBA code: Generate all combinations of 3 or multiple columns
Sub ListAllCombinations()
'Updateby Extendoffice
Dim xDRg1, xDRg2, xDRg3 As Range
Dim xRg As Range
Dim xStr As String
Dim xFN1, xFN2, xFN3 As Integer
Dim xSV1, xSV2, xSV3 As String
Set xDRg1 = Range("A2:A4") 'First column data
Set xDRg2 = Range("B2:B6") 'Second column data
Set xDRg3 = Range("C2:C5") 'Third column data
xStr = "-" 'Separator
Set xRg = Range("E2") 'Output cell
For xFN1 = 1 To xDRg1.Count
xSV1 = xDRg1.Item(xFN1).Text
For xFN2 = 1 To xDRg2.Count
xSV2 = xDRg2.Item(xFN2).Text
For xFN3 = 1 To xDRg3.Count
xSV3 = xDRg3.Item(xFN3).Text
xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3
Set xRg = xRg.Offset(1, 0)
Next
Next
Next
End SubParameter notes: In the code above, A2:A4, B2:B6, and C2:C5 define the data range for each column you want to use. The separator is set as "-", but you can adjust it to any character as needed. E2 is the target cell where results will be placed—update this if you want the output to begin elsewhere.
If you need to generate combinations for more than three columns, you will need to extend both the variables and the nested loops (for example, add xDRg4, xFN4, xSV4, and corresponding loops) in the code. Always review changes carefully to avoid logical errors.
3. After entering the code, press F5 or click "Run" in the VBA window to execute. All combinations will be generated beginning at your specified cell. If your data sets are large, it may take several seconds for the results to appear.
Troubleshooting: Ensure that your macros are enabled and that your source data does not contain merged cells or formatting that may interfere with script execution. If you encounter runtime errors, double-check the data references and review for spelling mistakes or missing lines in the code.
Generate all combinations based on 3 or multiple columns of data by using an awesome feature
For users of Kutools for Excel, the List All Combinations feature provides a fast and streamlined way to generate combinations from three or more columns, even when your data sets are extensive. This tool is especially useful in business, retail, and inventory applications, where you frequently need all possible pairing results for analysis without manual formula setup or VBA scripting.
1. On the Excel ribbon, navigate to Kutools > Insert > List All Combinations. Refer to the screenshot below for guidance:
2. In the List All Combinations dialog, you can assign which columns to include and choose the separator to appear between items in each combination. Select your preferred settings as shown in the screenshot. You can add or remove columns, rearrange the order, and even switch the separating character to meet your formatting needs.
3. After confirming your choices, click Ok. A prompt will appear for you to select the cell in which you wish the results to start. Select any cell in your worksheet.
4. Finally, click OK, and all combinations will instantly be listed as shown. The results are created right away—ideal when you need to obtain combinations quickly and move on with your analysis.
Advantages and tips: Using Kutools for Excel is not only time-saving, but reduces the risk of manual errors and allows for combinations over many data columns. It is suitable for users who frequently handle combination generation tasks and value simplicity. For very large data sets, Kutools processes the results efficiently without requiring technical support or complex manual work.
Kutools for Excel - Supercharge Excel with over 300 essential tools, making your work faster and easier, and take advantage of AI features for smarter data processing and productivity. Get It Now
Summary and additional advice:
When generating all possible combinations from multiple columns in Excel, choose the method that suits your data size and workflow. Formulas are quick for a few columns and rows, VBA code allows for customization and larger data sets, and Kutools provides a user-friendly interface especially when working interactively or frequently.
Always make sure your source data is clean, without blanks or merged cells, to avoid errors in output. For any method, preview results to confirm that all possibilities have been listed and spot check for accuracy. If you encounter issues such as slow performance or unexpected outputs, review cell references and setup, and try troubleshooting with smaller sample ranges first. Regularly saving your work before running large operations can also prevent data loss.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in