Search

# How to generate all combinations from two separate lists [Pivot Table Trick]

Share

Time for a quick but very useful tip. Ever wanted to create all combinations from two (or more) lists? a la Cartesian product of both lists.

Here is a ridiculously simple way to do it.

### Make Cartesian product of two tables in Excel

Note: You need Excel 2013 or above for this.

1. Convert two lists to tables, if not already done.
2. Select any cell in one of the tables and go to Insert > Pivot Table (Use ALT + NV shortcut)
3. Make sure to check “Add this data to the Data Model” option before clicking ok.
4. From your pivot table field list, switch to ALL view.
5. Add both (or all fields) to row label area.
6. Now, change the pivot table layout to “Show in tabular form” and check “Repeat all item labels” option.
7. Turn off sub totals & grand totals.
8. Viola, your cross product is ready. All combinations are generated by Excel for you. Use them as you see fit.

### More fun ways to use Pivot Tables

Pivot tables versatile. You can use them to replace formulas, analyze data, create interactive dashboards and do several cool things. Check out below tutorials to get started.

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### How to calculate WEEKNUMBER in Month / Quarter / Year with Excel?

Let’s say you have daily data and your boss wants to see the trends by week in month or week in quarter? How do you calculate the week number in a month or quarter? In this article, let me explain the logic and formulas we can use Excel for this.

## Related Tips

Keyboard Shortcuts

Learn Excel

### How to make a pivot table when you have data in multiple sheets [Tutorial]

Charts and Graphs

Learn Excel

### These Pivot Table tricks massively save your time

Cool Infographics & Data Visualizations

Excel Howtos

### 24 Responses to “How to generate all combinations from two separate lists [Pivot Table Trick]”

1. Chirayu says:

I still use Excel 2007. A work around for this for 2 lists would be to place the Products in Column A - Starting cell A2 (going down the rows). Then place the Customers in Row 1 starting cell B1 (going across the columns). Then Add a formula in B2 =\$A2&" "&B\$1. Dragging it across all columns & rows where the Customers & Products are present in column A & Row 1.

2. Andrew says:

If using Excel 2010, a similar solution is to first make a single pivot table from the two (or more) lists. This may require some helper columns if your lists are not already adjacent to each other. Add each list to the row labels and for each field go to "field settings"--> "Layout& Print" and select "repeat item labels" as well as "Show items with no data". Then go to "PivotTable Options"--> "Display" and select "Classic PivotTable layout". Voila! Note: if your lists are of different size, you will end up with some "(blank)" rows, but these are easy to filter out using the default filters in the pivot table.

• Chandoo says:

That is awesome. Never knew it. 🙂

• Arun N says:

Awesome. Further, if lists are of different size, Place the list with lower items at the top in row labels so that all the blanks will go to bottom of the report.

• JD says:

Wow thank you I am still learning excel 10 and this just saved me so much time! I needed each list in its own column so this worked great

3. Helen says:

When click to insert pivot table, tick on "Add this data to the Sata Model". Hit an error message:
We couldn't get data from the Data Model. Here's the error message we got: Invalid pointer array

4. Fred says:

Its not working, add this data to Data Model is grayed out. It not active and does not give me the option to check the box

• Paulo says:

Hey, could you explain me how to do that? I'm not getting the formula for import the column that I want from the other table/query.

5. Abhay says:

In Power Query load both tables as connection. Then in any one of the query "add custom column" and mention =[column name from first query] and then click ok.

Done. no more steps required.

• paulo says:

Hey, how to you mention the column name from the other query? Can you put exactly the syntax? I'm not being able to add the other query to then import the column needed. Cheers

6. V S VENKATRAMAN says:

Sir

Tried but couldn't succeed.... I am missing somewhere....
can you please mail me the sample sheet ...

Regards

Venkat

7. Kalpana says:

Tried. never knew it before. awesome. thanks chandoo

8. Darshan says:

thank you sir,

Sryy sir but I want to know one SHORTCUT KEY plz help me.
if there is Two sheet in excel and I formulate in such a way that if I made changes in sheet 2 than automatically same change made in sheet 1.
There is Shortcut key CTRL + [ for jump in sheet 2 directly from sheet 1. But I don't know Shortcut key for jump in sheet 1 from sheet 2.

anyone.......!?

• Hui... says:

@Darshan

Have you tried
Ctrl+Page Up
Ctrl+Page Down

9. Darshan says:

@Hui...
But my question was different...
ex. SHEET 2, 1+2=3
SHEET 1, 3 (Automatic change when I made change in sheet 2).
now if I made changes in sheet 2 like 2+2 = 4 than in sheet one it
reflect 4.
so now if I want to see how that 4 comes in sheet 1 than I use Ctrl + [ ,
but I don't know how I jump from sheet 2 to sheet 1 where I put formula.

• Hui... says:

@Darshan

Goto the Formula Tab
Click on Trace Dependents

There probably isn't a shortcut key as multiple cells could be dependent on the 2 cells.
Trace dependents brings up a dialog showing all the dependent cells

10. Jessica Yang says:

THANK YOU SO MUCH THIS WORKS LIKE MAGIC

xxxxxx

11. Gunjeet Singh says:

You are a life-saver !

12. Robert J says:

Very helpful thank you. I used it for creating a time series since I can use it to create a set of period headers for all possible combinations of control columns where for many periods there is no data. I created two tables from the data, one for the data and another for the survey period copied from the first table, then combined the two in the pivot table. A better way of creating a time series is at https://www.rwalker.info/blog/2017/4/26/how-to-quickly-create-time-series-pivotcharts, but that requires a professional version of Excel, which I don't have and don't feel inclined to pay for.

13. Preety Virdi says:

This is awesome - it really helped 🙂 thanks a million!

14. Kelly McAlister says:

Brilliant and simple, just like Darwin's Origin of the Species!

15. G.C says:

Thank you Chandoo, you work is wonderful. I have been cracking my brain on joining 2 unrelated tables and I found you solution in this posting. This is so wonderful!

Please keep posting and continue to good work!

How to generate all combinations from two separate lists [Pivot Table Trick]

16. Emilia says:

I knew it can be done in some smart way! Thanks a million!

17. M.G says:

Came across this old thread, excellent by the way, my question if can you or how do you chart Cartesian results? Thanks

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.