How to Generate Combinations from Five Separate Excel Columns
Question details
The user wants to generate all possible combinations by picking one value from each of five separate columns (A to E) while keeping the original column order.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a complete list of valid sets of five numbers by combining selections across multiple data columns.
- Observed behavior
- Needs a method to calculate every combination, define output row limits, and handle duplicate values dynamically.
Before starting, ensure your data is cleaned (remove unwanted blanks or duplicates) and determine the exact number of rows you expect, as large combinations can quickly exceed spreadsheet row limits.
Use Power Query to Generate All Combinations (Recommended)
Power Query is the most robust tool for handling large Cartesian products (combinations) without writing complex array formulas.
Power Query can easily create a Cartesian product by adding custom columns. This method is highly scalable and handles large datasets without slowing down your workbook.
Select each of your 5 columns individually and press Ctrl+T to format them as Tables. Name them TableA, TableB, TableC, TableD, and TableE.
Go to Data > Get Data > From Table/Range for the first table. Repeat this to load all five tables as connection-only queries.
In the Power Query Editor for TableA, go to Add Column > Custom Column. Enter the name of TableB in the formula box. Expand the new column. Repeat this process by adding custom columns for Tables C, D, and E, expanding each one.
Once all five columns are expanded and show all combinations, click Home > Close & Load to output the combined list into a new worksheet.

Use Dynamic Array Formulas
If you prefer using formulas, modern dynamic array functions can generate combinations directly in the grid.
Generate Data Combinations Easily with WPS Spreadsheet
WPS Spreadsheet fully supports dynamic array formulas and advanced data processing tools, making it easy to cross-join multiple columns and generate complex combinations natively.
- 1. Open your dataset in WPS: Launch WPS Spreadsheet and open the workbook containing your five columns of data.
- 2. Use array formulas: Type your combination formulas directly into a blank cell. WPS Spreadsheet will automatically spill the results into adjacent rows and columns.
- 3. Manage large datasets: Use the Data tab to filter, sort, or remove duplicates from your generated combinations instantly.

Frequently Asked Questions
Why do I get a #SPILL! error when generating combinations?
A #SPILL! error occurs when the formula generates more results than there is empty space available. Ensure there are no occupied cells directly below or next to your formula.
Is there a limit to how many combinations Excel can generate?
Yes, a single worksheet has a maximum of 1,048,576 rows. If the product of the counts of your five columns exceeds this number, you will not be able to output all combinations to a single sheet.
How can I remove duplicate values from the final output?
If you are using dynamic array formulas, you can wrap your entire combination formula inside the UNIQUE() function to automatically filter out duplicate sets.




