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.

Using Create Table Dialog Box for Creating Table

  • You will get a table.

Getting Table with Selected Data Range in Excel

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

Setting Table Name from Table Design Tab in Excel

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

Creating and Naming Table from Another Data Range in Excel

Step 2 – Import the Tables and Load as Connections

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

Using From Table/Range Command to Import Table Using Data Tab

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

Selecting Close & Load for Making Cross Join in Excel

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

Using Import Data Dialog Box to Only Create Connections for Cross Join in Excel

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

Adding Color_chart as Connection Only Query

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

Adding Size as Connection Only Query

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.

Selecting Reference to Create Reference Query in Excel

  • Change the name of the query.

Changing Name of Query to Cross Join in Excel

  • Go to the Add Column tab.
  • Select Custom Column.

Adding Column to Cross Join in Excel

  • 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.

Custom Column Dialog Box for Creating Cross Join in Excel

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

Adding a New Custom Column And Expanding It for Cross Join

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

Selecting Preferred Column for Cross Join in Excel

  • We got the Cross Join.

Cross Join Chart in Excel Power Query

Step 4 – Load as Table

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

Selecting Close & Load to Load the Cross Join Chart

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

Final Cross Join Table That is Loaded in Excel Sheet

Step 5 – Add New Data

  • Add new data to the table.

Add New Data in Excel Table

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

Refresh to Add New Data in It

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

New Data Are Loaded in Cross Join Table

Final Output:

Here’s the final Cross Join table after formatting.

Final Cross Join Table After Formatting in Excel

Read More: How to Combine Two Tables Using Power Query in Excel

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


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

Creating Tables with The Data Ranges And Use Them for Cross Join in Excel

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

Inserting Pivot Table in Excel

  • 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.

Using PivotTable from table or range Dialog Box in Excel

  • 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.

Adding Column to The Rows Area to Create Cross Join in Excel

  • 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.

Adding Second Column to The Rows Area of Pivot Table

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

Selection Any Cell of The Pivot Table to Change Table Design

  • Go to the Design tab.
  • Select Report Layout.

Selecting Report Layout from Design Tab

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

Choosing Show in Tabular Form

  • We got the Pivot Table in tabular form.

Pivot Table in Tabular Form to Get Cross Join in Excel

  • Select Report Layout again.
  • Select Repeat All Item Labels.

Selecting Repeat All Item Labels

  • Select Grand Totals.
  • Select Off for Rows and Columns.

Removing Grand Totals in Excel

  • This completes the Cross Join.

Getting Cross Join in Form of Pivot Table in Excel

  • We have added a border to the Pivot Table.

Final Cross Join Pivot Table in Excel

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


  • Go to the Developer tab.
  • Select Visual Basic.

Opening Visual Basic Editor to Create User-Defined Function in Excel

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

Opening a Module to Write VBA Code

  • 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

VBA Code for User-Defined Function for Cross Join in Excel

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.
  • Save the code and go back to your worksheet.

Saving VBA Code for Used-Defined Function

  • Select the cell where you want the Cross Join. We selected cell F5.
  • In cell F5, insert the following formula.

Using the User-Defined Function for Cross Join in Excel

  • Press Enter and you will get the Cross Join.

Getting Output from the cross_join Function

  • We have added a border.

Final Output of Cross Join From the User-Defined Function

Things to Remember

If you are working with VBA, you must save the Excel file as Excel Macro-Enabled Workbook.

Practice Section

We have provided a practice sheet for you to test these methods.

Practice Sheet for How to Create Cross Join in Excel

Download the Practice Workbook

Related Articles

<< Go Back to Power Query Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Mashhura Jahan
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

  1. Wonderfully Explained, was very helpful !!!!!

Leave a reply

Advanced Excel Exercises with Solutions PDF