How to Create Cross Join in Excel (3 Simple Ways)

The sample dataset contains the Color and Sizes of a t-shirt. We will Cross Join these tables to get all the available variations of that t-shirt.

Dataset for How to Create Cross Join in Excel

Method 1 – Use Power Query to Create a Cross Join in Excel7

Step 1 – Create a Table

Select the data range.
Go to the Insert tab.
Select Table.

Inserting Table to Create Cross Join in Excel

The Create Table dialog box will appear.
Check the My table has headers option.
Select OK.

You will get a table.

Go to the Table Design tab.
Write the Table Name.

Create a table for the other data range and name it.

Step 2 – Import the Tables and Load as Connections

Select a table.
Go to the Data tab.
Select From Table/Range.

The table is imported to Power Query.
Select the drop-down option for Close & Load.
Select Close & Load to.

The Import Data dialog box will appear.
Select Only Create Connection.
Select OK.

You will see that the Queries & Connections task pane appears on the right side of the screen.
1 Query is added as Connection only.

Add the other table as a Connection only query in the same way.

Step 3 – Create a Reference Query and a Custom Column

Right-click on the query that you want as your first column in the cross-join.
Select Reference.

Change the name of the query.

Go to the Add Column tab.
Select Custom Column.

The Custom Column dialog box will appear.
In the Custom column formula section, write the name of the other table with which you want to create a cross-join.
Select OK.

You’ll get a new custom column in the table.
Select the Expand button.

A drop-down menu will appear.
Select the column you want from the table.

We got the Cross Join.

Step 4 – Load as Table

Go to the Home tab.
Click on the drop-down option for Close & Load.
Select Close & Load.

You will see that the Cross Join is loaded in a table in a new Excel sheet.

Step 5 – Add New Data

Add new data to the table.

Select the Refresh button in the Cross Join from Queries & Connections.

The new data will be added to the Cross Join table.

Final Output:

Here’s the final Cross Join table after formatting.

Method 2 – Insert a Pivot Table to Create a Cross Join in Excel

Steps:

Insert tables and name them by following the Step 1 of Method 1.

Select the table which you want as your first column.
Go to the Insert tab.
Select PivotTable.

The PivotTable from table or range dialog box will appear.
Select Existing Worksheet.
Select the Location where you want the table.
Check the Add this data to the Data Model option.
Select OK.

The PivotTable Fields task pane will appear on the right side of the screen.
Select the column from the table, and it will be added to the Rows area.

Select the All tab.
Select the table that you want as the second column of your Cross Join.
Check the column name to add it to the Rows area. We have only one column in the table.

You will get the Pivot Table.
Select any cell of the Pivot Table.

Go to the Design tab.
Select Report Layout.

A drop-down menu will appear.
Select Show in Tabular Form.

We got the Pivot Table in tabular form.

Select Report Layout again.
Select Repeat All Item Labels.

Select Grand Totals.
Select Off for Rows and Columns.

This completes the Cross Join.

We have added a border to the Pivot Table.

Method 3 – Use a User-Defined Function to Create a Cross Join in Excel

Steps:

Go to the Developer tab.
Select Visual Basic.

The Visual Basic editor window will open.
Select the Insert tab.
Select Module.

A Module will open.
Insert the following code in that Module.

Function cross_join(rng1 As Range, rng2 As Range) As Variant
            Dim total_count As Integer
            Dim output_arr As Variant
            Dim row_offset As Integer
            total_count = rng1.Rows.Count * rng2.Rows.Count
            ReDim output_arr(0 To total_count - 1, 0 To 1)
            row_offset = 0
            For p = 1 To rng1.Rows.Count
            For q = 1 To rng2.Rows.Count
            output_arr(row_offset, 0) = rng1.Cells(p, 1).Value2
            output_arr(row_offset, 1) = rng2.Cells(q, 1).Value2
            row_offset = row_offset + 1
            Next q
            Next p
            cross_join = output_arr
End Function

How Does the Code Work?

We created a Function named cross_join as a Variant.
We declared a variable named total_count as Integer, output_arr as Variant, and row_offset As Integer.
We used Rows.Count Property to get the total_count.
We used two For Next Loops to go through all the columns of both ranges and concatenate each value to create new ones.
We ended the Function.