This was weird. I stumbled across my video and I thought “WHAT? Get all text combinations using just a pivot? How did I do that?” It seemed like ‘Back To The Future’ as I re-watched my own video.


Now it’s all coming back…Why do I like this solution? Because it’s so easy!


All Combinations of What?

00127_fruitXsizeXcompanyWe want all combinations of ‘Fruit’, ‘Size’, and ‘Company’.

If we get more Fruit, Sizes, or Companies we need an easy way to update our solution without re-doing it from scratch.




What Should the Result Look Like?

00127 Solution SampleYou should end up with a data set that looks like this (this is a partial list) that shows all the combinations of the items from ‘Fruit’, ‘Size’, and ‘Company’.

How many rows should we have? 

6 Fruits  X  2 Sizes  X  6 Companies = 48






All Combinations: Different Methods

Let’s review various methods to get all combinations from various fields:

  • Excel formulas (possible but takes time to set-up)
  • Excel VBA (relatively easy for a programmer but still takes time)
  • SQL Cartesian product (easy to create if you have a database)
  • Pivot Table (‘Show Items with no data’)


So why is the Pivot Table method so easy?

Because you don’t have to be very technical. Yes, I’ve solved this many times with formulas but sometimes I get tired and think of easier ways to solve Excel puzzles.


Here are the steps:

  • 1) Convert data into a table (Highlight data: ‘Insert’  ‘Table’)
  • 2) Pivot: ‘Insert’  ‘Pivot Table’ and then drop all fields into row label area
  • 3) Pivot: ‘Show in Tabular Form’ & ‘Repeat All Item Labels’
  • 4) Pivot: Turn off ‘Subtotals’ and ‘Grand Totals’
  • 5) Pivot: Right click in Pivot, ‘Field Settings’, ‘Show Items with no data’
  • 6) Pivot: Filter out blanks in each field
  • 7) Double check results using formulas


Watch My YouTube Video


Download my Excel file

Download here or via my OneDrive (file 00127)


Power Query Academy!

Get & Transform (Power Query) is an incredible tool in Excel that does so many amazing things: consolidates, normalizes, cleans, joins, etc!

Excel’s newest amazing data tool!

I have enrolled in Power Query Academy  !  Why not join me?

See my recommended Excel Training page.






About Me

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.

I’m a Data Analyst. Check out my YouTube videos and my blog posts.

Post a comment