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
