How to List All Combinations of Two or More Lists in Excel
You have four sizes, three colours and two materials, and you need a row for every combination. Excel has no function that produces a cross-product, so this is usually a nested formula nobody wants to maintain or a macro somebody wrote years ago.
Last updated: October 2026
| Two lists | Every combination | |
|---|---|---|
| Size: S, M | → | S · Red |
| Colour: Red, Blue | → | S · Blue |
| → | M · Red | |
| → | M · Blue |
Method 1 — an index formula
The classic pattern uses INDEX with INT((ROW()-1)/n) and MOD to walk the combinations like an odometer. It works, and it needs rewriting every time a list changes length.
Method 2 — Power Query
Add a custom column containing the second table, then expand it. Correct, repeatable, and several steps more than the job deserves for a one-off SKU grid.
The 1-click way — Tellsheet
List All Combinations treats each selected column as one list and writes the full cross-product to a new sheet. Lists can be different lengths — blanks are ignored rather than producing empty rows. The last column varies fastest, so the output reads the way you would write it by hand. It also checks the size before building anything: four lists of a hundred items is a hundred million rows, and being told that instantly beats watching Excel freeze.
Frequently asked questions
Is there an Excel function for combinations?
No. COMBIN counts how many combinations exist; it does not list them. Producing the rows needs a formula pattern, Power Query, or a tool.
Can I combine three or more lists?
Yes — one list per column, as many as you need, subject to the total row count.
Do the lists have to be the same length?
No. Blanks are skipped, so a list of two and a list of nine combine correctly.
What is the row limit?
100,000 rows. The total is checked before anything is generated, so an oversized request fails immediately with the number instead of hanging.
Related Excel guides
Every combination, in one click
SKU matrices, variant grids and test plans — generated, not hand-built.
Get Tellsheet free See pricing