Excel has no built-in worksheet function named CROSSPRODUCT for pairing two lists, but you can create a VBA user-defined function that returns every possible pair. The example below creates a Cartesian product: for lists with 3 and 2 items, it returns 3 × 2 = 6 rows, with one item from each list in every row.
What “cross product” means in Excel
In spreadsheet work, “cross product” commonly means a Cartesian product, also called a cross join. Given one list of products and another list of regions, for example, the result contains every product-region combination. If the first list has m items and the second has n, the result has m × n rows and two columns.
This is different from the vector cross product used in mathematics and physics. A vector cross product takes two three-dimensional vectors and produces a third vector; it does not pair every item in two lists. The VBA function here is for creating combinations from lists. If you meant the vector operation, see the distinction later in the article.
A Cartesian product is useful for building a complete set of combinations before adding prices, availability, or other calculations. It can also help prepare test cases, survey combinations, or a table of possible settings. It does not match records by a shared key: every item on the first side is paired with every item on the second.
A VBA function that returns all pairs
Paste this function into a standard VBA module. It accepts two contiguous, one-dimensional ranges—each range must be a single row or a single column—and returns a two-column array. It includes blank cells as items, so clear unused cells out of the input ranges if you do not want blank combinations.
Public Function CartesianProduct(ByVal firstRange As Range, ByVal secondRange As Range) As Variant
Dim firstCount As Long
Dim secondCount As Long
Dim firstIndex As Long
Dim secondIndex As Long
Dim outputRow As Long
Dim result() As Variant
' Accept one contiguous row or one contiguous column per input.
If firstRange.Areas.Count <> 1 Or secondRange.Areas.Count <> 1 Then
CartesianProduct = CVErr(xlErrValue)
Exit Function
End If
If (firstRange.Rows.Count <> 1 And firstRange.Columns.Count <> 1) _
Or (secondRange.Rows.Count <> 1 And secondRange.Columns.Count <> 1) Then
CartesianProduct = CVErr(xlErrValue)
Exit Function
End If
If firstRange.Rows.Count = 1 Then
firstCount = firstRange.Columns.Count
Else
firstCount = firstRange.Rows.Count
End If
If secondRange.Rows.Count = 1 Then
secondCount = secondRange.Columns.Count
Else
secondCount = secondRange.Rows.Count
End If
' Return a worksheet error rather than exceeding Excel's row limit.
If firstCount > 1048576 \ secondCount Then
CartesianProduct = CVErr(xlErrNum)
Exit Function
End If
ReDim result(1 To firstCount * secondCount, 1 To 2)
outputRow = 0
For firstIndex = 1 To firstCount
For secondIndex = 1 To secondCount
outputRow = outputRow + 1
If firstRange.Rows.Count = 1 Then
result(outputRow, 1) = firstRange.Cells(1, firstIndex).Value2
Else
result(outputRow, 1) = firstRange.Cells(firstIndex, 1).Value2
End If
If secondRange.Rows.Count = 1 Then
result(outputRow, 2) = secondRange.Cells(1, secondIndex).Value2
Else
result(outputRow, 2) = secondRange.Cells(secondIndex, 1).Value2
End If
Next secondIndex
Next firstIndex
CartesianProduct = result
End Function
The two loops are the key to the result. The outer loop holds one item from the first range while the inner loop cycles through every item in the second range. When that inner loop finishes, the outer loop moves to the next first-range item. This ordering groups the output by the first list: all second-list values appear with the first item, then all appear with the next.
Install and use the function
- In Excel for Microsoft 365 on Windows, press Alt+F11 to open the Visual Basic Editor. On Mac, use Developer > Visual Basic; depending on the keyboard, you may need Option+F11 or Fn+Option+F11.
- In the editor, choose Insert > Module, then paste the function into the new module. Do not paste it into a worksheet code page or ThisWorkbook if you want to call it like a normal worksheet function.
- Save the workbook as an Excel Macro-Enabled Workbook (.xlsm), close or return to the worksheet, and allow macros if Excel asks. A normal .xlsx file cannot store VBA code.
- Enter the function formula in a blank worksheet area where there is room for the results to expand.
With the first list in A2:A4 and the second list in C2:C3, enter =CartesianProduct(A2:A4,C2:C3) in an empty cell. In Microsoft 365, the returned 6-by-2 array should spill into six rows and two columns. If the cells where it needs to spill are occupied, Excel shows #SPILL!; clear those cells or move the formula. The result count is 3 × 2 = 6.
On older Excel versions without dynamic arrays, select a six-row-by-two-column destination range first, type the formula, then confirm it with Ctrl+Shift+Enter. On Mac, the equivalent array-entry shortcut is Control+Shift+Return. Older versions may display braces around the array formula; do not type those braces yourself. If the selected output area is the wrong size, the result may be incomplete or show an error.
Worked example and output order
Suppose A2:A4 contains Red, Blue, and Green, while C2:C3 contains Small and Large. The function returns:
- Red — Small
- Red — Large
- Blue — Small
- Blue — Large
- Green — Small
- Green — Large
The function returns values rather than formatting. It does not copy cell colors, number formats, validation rules, or formulas as formulas; a formula in an input cell contributes its calculated value. If you need a permanent table, copy the spilled result and use Home > Paste > Paste Values, or use a separate macro designed to write results to a chosen range.
Order matters when comparing the output with another method. This function lists second-range items in their original order for each first-range item. Reversing the two arguments still creates the same conceptual combinations, but the columns and row order change. If you need a particular sort order, sort the source lists first or sort the output afterward.
Input rules, blanks, and size limits
Each argument must refer to a single rectangular row or column, such as A2:A20 or A2:F2. A multi-row, multi-column block is rejected with #VALUE! because flattening a two-dimensional range could produce different, unexpected orders. A multi-area reference such as a selection made with Ctrl-click is also rejected. Put the values into one continuous row or column if you want to use this function.
Every cell in the supplied ranges counts, including empty cells. For example, a range of five cells with only three filled still contributes five items. That behavior is deliberate: an empty cell can be a meaningful item in some data sets, and silently removing it would change the number of combinations. Trim the selected ranges to their actual data, or make a separate clean list, when blanks represent unused space.
The output grows multiplicatively, not additively. Two lists of 100 and 200 values produce 20,000 rows; lists of 1,000 and 1,000 produce 1,000,000 rows. The code returns #NUM! if the theoretical output exceeds Excel’s 1,048,576-row worksheet limit, but a result close to that limit can still be impractical because it consumes substantial memory and leaves little room below the formula cell. Estimate the product before using large ranges.
Excel errors in source cells are carried into the corresponding output cells. The function does not remove duplicates, filter combinations, or treat text and numbers as equivalent. If you need unique pairs or only combinations that meet a condition, apply that logic separately or choose a data-preparation method that supports filtering.
Common problems and how to fix them
- #NAME? Check the spelling of CartesianProduct, confirm the code is in a standard module, and make sure the workbook was saved as .xlsm with macros enabled. If a workbook is opened with macros disabled, its VBA function may not be available.
- #VALUE! One of the references may cover a two-dimensional block or multiple areas. Use one row or one column for each input.
- #SPILL! In Microsoft 365, the destination cells may contain data, merged cells, or another obstacle. Move the formula or clear the required two-column output area.
- Only one result appears in an older version. Select the full output range before entering the formula and confirm it as a legacy array formula with Ctrl+Shift+Enter on Windows, or Control+Shift+Return on Mac.
- Too many unexpected rows. Check whether the selected ranges include blank cells. Every selected cell is treated as an item.
VBA user-defined functions are intended to return a value to the cell containing the formula. This function does not write into other worksheet cells, so it is safer than a procedure that edits a sheet, but it still depends on VBA being permitted in the workbook. Save a backup before running any VBA macro that changes workbook contents; macro actions cannot be undone with Ctrl+Z.
When to use a macro or Power Query instead
A worksheet UDF is convenient when the input ranges may change and you want the combinations to recalculate. It is less suitable when the result should be a fixed table that a user edits directly, when the output is very large, or when the workbook must work in Excel for the web. Excel for the web can open macro-enabled workbooks but does not run VBA macros or VBA worksheet functions; use the desktop app to calculate this UDF.
For a one-time result, a macro can write the combinations into a new worksheet as values. That avoids keeping a formula and macro dependency in the final table, but a writing macro modifies the workbook and should be reviewed before it is run. A backup is important because Ctrl+Z does not reverse macro changes.
Power Query is often a better choice for repeatable data preparation, especially when the lists are large or come from external sources. A typical cross-join setup is to load each list into Power Query, add a constant key column with the same value to both queries, merge the queries on that key, expand the second list’s column, and remove the helper key. Refreshing the query rebuilds the combinations from the current source data. Power Query steps and labels can vary slightly by Excel build, but it is accessed from the Data tab in desktop Excel.
For a small set of combinations, formulas can also be filled down, but the indexing logic becomes harder to maintain as list sizes change. The VBA UDF centralizes that logic in one reusable function; Power Query provides a more transparent refreshable pipeline. Choose based on whether you need a live worksheet array, a fixed output, or a repeatable import-and-transform process.
Cartesian product versus vector cross product
If “cross product” means the vector operation, do not use the list-pairing function above. For vectors (a1, a2, a3) and (b1, b2, b3), the mathematical cross product is (a2b3 − a3b2, a3b1 − a1b3, a1b2 − a2b1). Its result has three components and depends on the order of the two vectors; reversing them changes the signs. In Excel, you can calculate those three component expressions in cells using ordinary multiplication and subtraction, or write a separate VBA function that explicitly accepts six components.
That distinction prevents a common mistake: pairing two lists does not perform vector algebra, and multiplying values in matching positions is not a Cartesian product either. The right method depends on the desired output. Use CartesianProduct for every combination of two lists; use vector formulas for the three-component mathematical operation.
Frequently Asked Questions
Does Excel have a built-in cross product function?
No worksheet function named CROSSPRODUCT creates all combinations of two lists. The VBA user-defined function above supplies that behavior. Excel’s built-in functions for other kinds of products do not automatically create a Cartesian join.
Can the function use horizontal ranges?
Yes. Each input can be a single row or a single column, so A2:A5 and B1:E1 are both valid shapes. The output always has two columns, regardless of the orientation of the inputs.
Will it include duplicate values?
Yes. It uses every cell as supplied and does not remove duplicates. If a list contains the same value twice, that value contributes twice and can create repeated-looking pairs.
Can I use this VBA function in Excel for the web?
No. Excel for the web does not execute VBA code. Open the macro-enabled workbook in the desktop version of Excel, or use Power Query or another non-VBA approach for a web-compatible workflow.
Why does my result show #SPILL! instead of the pairs?
In Microsoft 365, the formula needs an unobstructed rectangular area two columns wide and enough rows for every pair. Clear cells in the spill area, remove merged cells that block it, or place the formula somewhere with enough free space.
Related
- Excel Text to Columns: Split Alt+Enter Line Breaks
- Excel Text to Columns and Rows: How to Split Data
- Excel Text to Columns: How to Make Results Vertical
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA MsgBox: Syntax, Buttons, and ExamplesExcel VBA’s MsgBox function displays a dialog box with a message, optional buttons and icon, and an optional…
- VBA MsgBox Scroll: Limits and Scrollable AlternativesNo: VBA’s built-in MsgBox has no setting or argument that adds a scrollbar. For long text, use a…
- Excel VBA Subscript Out of Range: Causes and FixesExcel VBA “Run-time error 9: Subscript out of range” means your code asked for an item that does…
- VBA Range Column: How to Reference ColumnsIn VBA, reference a whole worksheet column with Columns("C") or Range("C:C"); to get the column containing a particular…
- Excel VBA MsgBox Custom Buttons and ChoicesExcel VBA’s built-in MsgBox cannot create arbitrary button captions such as “Continue” or “Skip”; it offers fixed button…
- VBA Loop Selected Cells in Excel: ExamplesTo loop through the cells a user has selected in Excel, assign Selection to a Range variable and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.