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

Posted on October 4th, 2016 in Pivot Tables & Charts - 13 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.
    add-pivot-to-data-model
  4. From your pivot table field list, switch to ALL view.
    see-all-tables-pivot-table-field-list
  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.
    pivot-table-layout-settings
  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.

join-combinations-of-two-tables-excel

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.

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

13 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

  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.

  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.......!?

  9. Darshan says:

    @Hui...
    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:

      @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

Leave a Reply