fbpx
Search
Close this search box.

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

Share

Facebook
Twitter
LinkedIn

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.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

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
Excel formula list - 100+ examples and howto guide for you

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.

Advanced Pivot Table tricks

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.

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.

  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:

    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

  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

Leave a Reply