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

Pivot Tables & Charts - 22 comments

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.


Hello Awesome...

My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.

I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.

Thank you and see you around.

Related articles:

Written by Chandoo
Tags: , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

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

  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

    Pls advise.

  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:


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



  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.


  9. Darshan says:

    Thanx for reading/reply..
    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:


      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:



  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]

Leave a Reply

« »