Cartesians product,(sometimes known as a Cross Join) can be very useful in data applications. This is how you do a cartesian product in excel. Working in a data science environment, I often come across situations where I need to calculate all of the possible permutations between two data sets. Typically I’d use SQL to achieve this by joining the data with a cross join. But sometimes I find it could be faster or easier to just carry it out in Excel.
It seems odd, but as a programmer working at a FANG you’d be surprised at how useful simple tools like spreadsheets can often be. Whilst you could often accomplish the same result by taking data, sanitising it, and loading it into a database, sometimes it’s best to just use the tools you have available to you (despite the low-tech nature ). I find that I’m often tempted to program a solution to issues like this when the tools are available to work out the desired output, whilst leaving it in a simple and shareable format.
Across this series, I’ll be looking to try and share some of the more complex tips that I’ve come across in my day-to-day job. This post will help you excel cross join two columns from excel.
Step1: Preparation of cartesian product in excel
The first step is to get your datasets in place. This will help with carrying out an excel cross join on two columns In this example I’ll be using the following:
The aim is to have the cartesian product of these two data sets, so that we’d have every size of Animal (Small Badger, Large Badger etc).
Once we have this data in Excel, we want to add both sets to tables. You can do this by selecting the data before navigating to the “Insert” tab in Excel. If you then click “Table” it will insert a table with nice formatting.
Step 2: Pivot
Next, select one of the tables, go to “insert” and select “Insert pivot table”.
Make sure to select “Add this data to the Data Model” when adding a pivot table.
Adding the pivot table will create a new tab in your excel workbook. Select “All” in the “PivotTable Fields” section of Excel. This will allow you to see all of the tables in your workbook.
Select the fields you want to join on to add them to the pivot table.
Step 3: Cleanup & creation of Excel cartesian product
When adding the fields we want, we will effectively have created the cartesian product of two lists, but it’s likely not the format you want it in.
To normalise this, go to the “Design” section of the PivotTable tools. Then select “Show in Tabular Form” and “Repeat All Item Labels” from the “Report Layout” section.
And there we have it, a quick and easy way of creating a excel cross product of two columns.
Creating an Excel cartesian product like this can be fast and effective. Give it a go and let me know what you think below.
Admiring the persistence you put into your site and detailed information you present. It’s awesome to come across a blog every once in a while that isn’t the same out of date rehashed information. Fantastic read! I’ve bookmarked your site and I’m adding your RSS feeds to my Google account.
Thank you, I really appreciate that! Looks like you’ve put together some great content too.
Great advice, saved a lot of time, thanks!