Create Cartesian product with Excel Query

This Excel tutorial explains how to create Cartesian product with Excel Query.

You may also want to read:

Microsoft Access create Cartesian product with Cross Join

Create Excel Query and update Query

Create Cartesian product with Excel Query

In SQL, there are several types of Table Join: Left Join, Right Join, Inner Join and Cross Join.

Cross Join is to produce all possible combination of records in joined Tables which are unrelated (without common key), the result is known as Cartesian product. Most of the time we hate Cartesian product because it is usually a result of duplicated keys. Sometimes we need it because it helps us join unrelated tables together to form thousands of possible combination very quickly.

Example – Create Cartesian product with Excel Query

To Illustrate, let’s say we have a product list in worksheet Sheet1

 
Product
Hat
Shirt
Gloves
Pants
Shoes

and a color list in worksheet Sheet2

 
Color
Red
Orange
Yellow
Green
Blue
Purple

Our goal is to join the two Tables so that each Product has all 6 different colors.

To begin, navigate to Data > From Other Sources > From Microsoft Query

 

Select Excel Files > OK

 

Select the workbook that contains the data. You can select Workbook that you are currently opening > OK

 

Select the worksheet, and then add the fields you need to the right panel (click on the arrow in the middle)

If you cannot see the Worksheet names, click on Options button and check System Tables check box.

Click on Next.

 

The below message box pops up saying you have to join the Table by yourself. It doesn’t matter because I don’t want Excel to guess what I want to join.

 

The result is immediately displayed in the lower table. Now each product has all 6 colors. It is also possible to add additional Tables to produce more Cartesian product.

 

To return Query result, navigate to File > Return Data to Microsoft Excel

 

Select a Cell you want to return data > OK

 

Now data is returned. When the data source is updated, you can refresh this table by clicking Data tab > Refresh All

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *