Function Problems

How to Generate Combinations from Five Separate Excel Columns

Camila MilosovichCamila Milosovich Nov 11, 2026 869 views

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.

How to Generate Combinations from Five Separate Excel Columns
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 you start

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.

Solution 1Recommended

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.

1
Convert data to tables

Select each of your 5 columns individually and press Ctrl+T to format them as Tables. Name them TableA, TableB, TableC, TableD, and TableE.

2
Load tables into Power Query

Go to Data > Get Data > From Table/Range for the first table. Repeat this to load all five tables as connection-only queries.

3
Perform a Cross Join

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.

4
Load the result back to Excel

Once all five columns are expanded and show all combinations, click Home > Close & Load to output the combined list into a new worksheet.

Use Power Query to Generate All Combinations (Recommended)
Row Limits: Excel supports up to 1,048,576 rows. If each of the 5 columns has 20 items, the combinations (20^5) will total 3,200,000 rows, which exceeds Excel's limit.
Powerful Spreadsheet Data Processing

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. 1. Open your dataset in WPS: Launch WPS Spreadsheet and open the workbook containing your five columns of data.
  2. 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. 3. Manage large datasets: Use the Data tab to filter, sort, or remove duplicates from your generated combinations instantly.
Full compatibility with Microsoft Excel formulas (.xlsx)Supports modern dynamic array functions (UNIQUE, FILTER, etc.)Lightweight and fast, even when calculating large data setsFree to use with a clean, familiar interface
microsoft office alternative - wps office

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.