I did a recent post on using Power Query to create an all combinations list. I found another technique on chandoo.org to create that list using a PivotTable.

We have two lists and we want to create a third list that creates all the combinations.

Let’s say we want to create some dummy names. We have 4 first names and 5 last names. That’s means there are 20 combinations possible (4×5).

The Power Query solution required two separate tables. The PivotTable solution allows you to put both lists in the same table – see image below.

This table has been formatted using the Format as Table icon on the Home ribbon.

I then create a PivotTable based on this table where I add the First and Last fields to the Rows section of the PivotTable. See image below.

You then need to remove the Subtotals and Grand totals from the PivotTable. These options are both on the left to the Design ribbon tab.

Then use the Report Layout drop down and select Show in Tabular Form.

Revised report below.

We now need to change some field settings to get the PivotTable displaying as required.

Right click the First field in the PivotTable and choose Field Settings and click the Layout & Display tab. You need to make the three changes highlighted in the image below.

• Show items labels in tabular form
• Repeat item labels
• Show items with no data

The repeat those settings on the Last (second) field. You must make the changes to both fields.

That’s it. See resulting report below.

Another tip is to put the list with the least number of entries underneath the other in the Rows section of the PivotTable. This puts all the blanks at the bottom as shown above.