How to Generate a List of all Possible Permutations in Excel

This guide will explain how you can use Excel formulas to generate a list of all possible permutations from multiple columns in Excel.

We can generate permutations by using a counter that cycles through the given values list.

Permutations are an arrangement of objects in a definite order. When looking for permutations of multiple lists, we would want to know how many possible unique ways we can choose a single value from each list.

Let’s take a look at a scenario where we may need to find all permutations of multiple lists.

Suppose you are handling a T-shirt shop. You have multiple colors of T-shirts, with each shirt having sizes from XS to XL. For inventory purposes, you would like to generate an item name for each possible permutation of t-shirt color and size.

If your shop has dozens of colors, it may be tedious to label each permutation manually. Instead, we can use Excel formulas to generate the item names for your inventory.

With the ROW and COUNTA functions, we can create our own counter that cycles through all possible combinations in a list.

Now that we know when to find permutations, let’s take a look at an actual sample spreadsheet that implements a counter to generate a list of permutations.

 

 

A Real Example of Generating a List of all Possible Permutations in Excel

Let’s take a look at a sample spreadsheet that generates a list of all possible permutations given two lists.

Columns A and B in the table below contain values that we can combine to indicate a specific product for sale. For example, we can specify a size XS red shirt with ‘Red | XS’. In column C, we generated all possible permutations of the given values of size and color.

list all possible permutations in Excel

 

To get the values in Column C, we just need to use the following formula:

=IF(ROW()-ROW($C$1)+1>COUNTA(A:A)*COUNTA(B:B),"",INDEX(A:A,INT((ROW()-ROW($C$1))/COUNTA(B:B)+1))&","&INDEX(B:B,MOD(ROW()-ROW($C$1),COUNTA(B:B))+1))

To understand how this formula works, we’ve added a few rows in the table below.

using COUNTA and ROW functions

 

The IF statement first checks if the current row is greater than the number of possible permutations. When given two lists, the number of total permutations is simply the product of the length of both lists. In this example, there should be a total of 15 permutations (3 colors multiplied by 5 shirt sizes)

If the IF function detects that the row is greater than the number of permutations, the function returns an empty string. 

If this is not the case, then our formula concatenates two INDEX results. The first INDEX function should return either ‘Red’, ‘Yellow’, or ‘Green’. 

The INT((ROW()-ROW($C$1))/COUNTA(B:B)+1) allows each color to repeat exactly n times where n is equal to the number of shirt sizes. You can see this happen in column E.

Lastly, the MOD(ROW()-ROW($C$1),COUNTA(B:B))+1) allows each shirt size to increment by 1 until it reaches 5 and starts again from 1. 

The concatenation character ‘&’ is used to separate the two INDEX results from each other.

You can make your own copy of the spreadsheet above using the link attached below. 

If you’re ready to try out this formula in Excel, let’s start writing it ourselves!

 

 

How to Generate a List of all Possible Permutations in Excel

This section will guide you through each step needed to start using the permutation formula in Excel. You’ll learn how we can use the INDEX and COUNTA functions to loop through each value in the list, so each value is paired exactly once.

Follow these steps to start using Excel to generate a list of all possible permutations:

  1. First, select the cell that will contain our first permutation. In this example, we’ll start with cell C1. To simplify our ROW function later, we will not use any headers in this spreadsheet.
    select first cell
  2. Next, we can paste our permutation formula into the formula bar. Make sure that the formula references the right columns.
    add permutation formula to list permutations in Excel
  3. Hit the Enter key to evaluate the formula.
    Evaluate formula
  4. We can now drag down the formula in C1 to fill in the rest of the column.
    listing all permutations
  5. We can also change the characters in between each value. For example, we can add a comma between the color and size labels.
    change character separating values when listing permutations in Excel

 

 

Frequently Asked Questions (FAQ)

  1. Will this formula work for more than two columns?
    The formula provided can only be used for two columns. However, once you’ve found permutations for the first two columns, you can use the result as input. Using this formula again with the first result as the “first column” and the additional column as the formula’s second column.
    using permutation formula multiple times for more than 2 columns

 

 

That’s all you need to remember to start using Excel to generate a list of all possible permutations given two lists. This guide breaks down a clever use of the INDEX, COUNTA, and ROW functions to iterate through a list.

Finding permutations is made possible because of the wide variety of functions supported in Excel. With so many other Excel functions out there, you can surely find one that suits your use case. 

Are you interested in learning more about what Excel can do? Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Excel.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive ✨ early access ✨ to new content.

 

0 Shares:
Leave a Reply

Your email address will not be published. Required fields are marked *

You May Also Like