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.

##### Table of Contents

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

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.**INT((ROW()-ROW($C$1))/COUNTA(B:B)+1)**

Lastly, the

allows each shirt size to increment by 1 until it reaches 5 and starts again from 1. **MOD(ROW()-ROW($C$1),COUNTA(B:B))+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:

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

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

- Hit the
**Enter key**to evaluate the formula.

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

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

**Frequently Asked Questions (FAQ)**

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

## 1 comment

The formula you used for generating permutations works perfectly! Thank you.

I would like to incorporate a header row into the spreadsheet. Is there a way to make the formula work if the starting cell for the two lists was not A1, but A2?

Thanks.