# 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.

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.

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.

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.

2. Next, we can paste our permutation formula into the formula bar. Make sure that the formula references the right columns.

3. Hit the Enter key to evaluate the formula.

4. We can now drag down the formula in C1 to fill in the rest of the column.

5. We can also change the characters in between each value. For example, we can add a comma between the color and size labels.

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.

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.

## How to Convert HTML to Text in Cells in Excel

This guide will discuss how to convert HTML to Text in Cells in Excel using two methods. In…

## How to Calculate Root Mean Square Error in Excel

This guide will discuss how to calculate the root mean square error in Excel using three simple and…

## How to Calculate Odds Ratio and Relative Risk in Excel

This guide will explain how to calculate the odds ratio and relative risk in Excel. These statistics are…

## How to Plot a CDF in Excel

This guide will explain how to plot a cumulative distribution function or CDF in Excel. The graph of…