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

A Cross Join combines each row from the first table with each row of the second table. Cross Join returns all the possible combinations of two tables. The focus of this article is to explain how to create Cross Join in Excel.

Create Cross Join in Excel: 3 Simple Ways

Here, I have taken the following dataset to explain this article. It contains the Color and Sizes of a t-shirt. I will Cross Join these tables to get all the available variations of that t-shirt in Excel in 3 different ways.

1. Use Power Query to Create Cross Join in Excel

Power Query helps to import or connect to external data and then shape the data according to preference. You can also load data from power query to Excel. In this first method, I will use Power Query to create Cross Join in Excel. Let’s explore the steps of this method.

Step-01: Create Table

In this step, I will create a table and then name the table.

First, select the data range.
Next, go to the Insert tab.
Then, select Table.

Consequently, the Create Table dialog box will appear.
Check the My table has headers option.
Then, select OK.

Now, you will see that you have inserted a table.

Next, go to the Table Design tab.
Then, write the Table Name as you want.

Afterward, create a table for the other data range and name it in the same way.

Step-02: Import Table and Load as Connection

Now, I will import tables and then load them as connections in Excel.

In the beginning, select the table.
Then, go to the Data tab.
Next, select From Table/Range.

After that, you will see the table is imported to Power Query.
Select the drop-down option for Close & Load.
Then, select Close & Load to.

Consequently, the Import Data dialog box will appear.
Select Only Create Connection.
Then, select OK.

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

Afterward, add another table as a Connection only query in the same way.

Step-03: Create Reference Query and Custom Column

Here, I will create a Reference query and then add a custom column to it.

To begin with, right-click on the query that you want as your first column in the cross join.
Then, select Reference.

Now, change the name of the query.

Afterward, go to the Add Column tab.
Then, select Custom Column.

Consequently, 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.
Then, select OK.

Now, you will see that you have added a new custom column to the table.
Next, select the Expand button.

Afterward, a drop-down menu will appear.
Then, select the column you want from the table.

Finally, you can see that I have got the Cross Join.

Step-04: Load as Table

In this step, I will load this Cross Join as a table in Excel.

Firstly, go to the Home tab.
Next, click on the drop-down option for Close & Load.
Then, select Close & Load.

Consequently, you will see that the Cross Join is loaded in a table in a new Excel sheet.

Step-05: Add New Data

Now, I will show you how you can add new data to the Cross Join in Excel.

In the beginning, add the new data to the table.

Then, select the Refresh button in the Cross Join from Queries & Connections.

Afterward, you will see that the new data is added to the Cross Join table.

Final Output:

In the following image, you can see the final Cross Join table after formatting.

2. Insert Pivot Table to Create Cross Join in Excel

You can also use the Pivot Table to create a Cross Join. Pivot Table helps to summarize and analyze data. Here, I will use the Pivot Table to create a Cross Join in Excel. Let’s see the steps.

Steps:

Firstly, insert tables and name them by following the Step-01 of Method-1.

Secondly, select the table which you want as your first column.
Thirdly, go to the Insert tab.
Then, select PivotTable.

Now, PivotTable from table or range dialog box will appear.
Select Existing Worksheet.
Then, select the Location where you want the table.
Next, check the Add this data to the Data Model option.
Afterward, select OK.

Consequently, 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.

After that, select the All tab.
Then, select the table that you want as the second column of your Cross Join.
Next, check the column name to add it to the Rows area. Here, I have only one column in the table.

Now, you will see that you have added the Pivot Table.
Select any cell of the Pivot Table.

Then, go to the Design tab.
Next, select Report Layout.

After that, a drop-down menu will appear.
Select Show in Tabular Form.

Afterward, you will see that you have got the Pivot Table in tabular form.

Then, select Report Layout again.
Next, select Repeat All Item Labels.

After that, select Grand Totals.
Then, select Off for Rows and Columns.

Finally, you can see that I have got the Cross Join.

Now, I have added a border to the Pivot Table, and this is how it looks.

3. Employ a User-Defined Function to Create Cross Join in Excel

You can use VBA to create a User-Defined function. Here, I will use Excel VBA to create a User-Defined function to create Cross Join in Excel. Let’s explore the steps.

Steps:

In the beginning, go to the Developer tab.
Then, select Visual Basic.

Consequently, the Visual Basic editor window will open.
Select Insert tab.
Next, select Module.

After that, a Module will open.
Write 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?

Here, I created a Function named cross_join as a Variant.
Then, I declared a variable named total_count as Integer, output_arr as Variant, and row_offset As Integer.
Next, I used Rows.Count Property to get the total_count.
After that, I use 2 For Next Loop to go through all the columns of both ranges.
Finally, I ended the Function.

After that, Save the code and go back to your worksheet.

Next, select the cell where you want the Cross Join. Here, I selected cell F5.
Then, in cell F5, write the following formula.

=cross_join(B5:B9,D5:D8)

After that, press Enter, and you will get the Cross Join.

In the end, I have added a border, and this is what my final Cross Join looks like.

Things to Remember

If you are working with VBA, you must save the Excel file as Excel Macro-Enabled Workbook. Otherwise, the VBA code will not work.

Practice Section

Here, I have provided a practice sheet for you to practice how to create Cross Join in Excel.

Download Practice Workbook

You can download the practice workbook from here.

Creating Cross Join.xlsm

Conclusion

So, you have reached the end of my article. Here, I tried to cover how to create Cross Join in Excel in 3 simple ways. I hope this article was helpful to you. If you have any questions, feel free to let me know in the comment section below.