Summary

To list every possible permutation (arrangement) of a list of items, you can use a formula based on the SEQUENCE function, the INDEX function, and the FILTER function. In the example shown, the list of four items is in the range B5:B8, and the formula in D5 is:

=LET(
  list,B5:B8,
  items,TOCOL(list),
  n,ROWS(items),
  counter,SEQUENCE(n^n,,0),
  powers,SEQUENCE(,n,n-1,-1),
  picks,MOD(INT(counter/n^powers),n)+1,
  keep,BYROW(picks,LAMBDA(tuple,COUNT(UNIQUE(tuple,TRUE))=n)),
  FILTER(INDEX(items,picks),keep)
)

This formula returns all 24 arrangements of the four items in B5:B8. The resulting array lands as a table in cell D5 and spills into the range D5:G28.

A permutation is an arrangement where order matters and each item is used once, so the number of arrangements of n items is n factorial (n!). For 4 items that is 4! = 24, which you can confirm with FACT or PERMUT: =FACT(4) and =PERMUT(4,4) both return 24. The formula above adapts to the length of the list on its own; the reusable LAMBDA near the end scales to much longer lists.

Generic formula

=LET(
  list,range,items,TOCOL(list),n,ROWS(items),
  counter,SEQUENCE(n^n,,0),
  powers,SEQUENCE(,n,n-1,-1),
  picks,MOD(INT(counter/n^powers),n)+1,
  keep,BYROW(picks,LAMBDA(tuple,COUNT(UNIQUE(tuple,TRUE))=n)),
  FILTER(INDEX(items,picks),keep)
)

Because the number of positions comes from the list itself, this formula works for a list of any length. However, because it builds n^n "tuples" (a tuple is just an ordered list of positions) before filtering, it becomes impractical in an Excel worksheet after about 7 items. For longer lists, use the recursive LAMBDA shown below.

Explanation

Excel has functions that count permutations (PERMUT) and factorials (FACT), but these functions don't list the actual arrangements. In this example, the goal is to generate every permutation of a list, not just count them.

The overall strategy is to generate, then filter: first build every possible index tuple using an odometer counting trick, then keep only the tuples that use each position once. This is the same "odometer engine" explained in detail on the combination lock codes page.

Table of contents

What is a permutation?

A permutation is an arrangement where order matters. Listing the same items in a different order gives a different permutation, so 1,2,3,4 and 4,3,2,1 are two different arrangements. This is the opposite of a combination, where order does not matter.

When you arrange all the items in a list and each item is used exactly once, the number of arrangements is the factorial of the list size, written n!. There are n choices for the first position, n-1 left for the second, n-2 for the third, and so on:

4! = 4 × 3 × 2 × 1 = 24

In Excel, you can confirm the count with FACT or PERMUT:

=FACT(4)      // returns 24
=PERMUT(4,4)  // returns 24

Both return 24, but neither lists the 24 arrangements. That is what the formula on this page does. For a full explanation of the difference between permutations and combinations, see Combinations and permutations in Excel.

Generate every arrangement

There is no simple way to step directly through only the valid arrangements, so instead we generate every possible position and then throw away the ones that aren't valid. To generate all positions, we use the odometer idea explained on the combination lock codes page: count from the first tuple to the last, where the rightmost position turns fastest and each position rolls the one to its left forward when it completes a full cycle.

With 4 positions and 4 possible items at each position, there are 4^4 = 256 tuples to count through. We generate a counter with the SEQUENCE function, starting at zero, and assign it to counter:

counter,SEQUENCE(n^n,,0)  // n = 4, so 256 numbers, 0 to 255

Each value in counter is a single number that identifies one tuple. Turning that number back into its separate positions is a kind of decoding, just as each wheel on an odometer reading shows one digit of the whole. We decode each number using a pattern based on the INT and MOD functions.

The main idea is place value. The number 12345 can be expressed like this:

1×10^4 + 2×10^3 + 3×10^2 + 4×10^1 + 5×10^0

To extract single digits, we can divide by the matching power of 10 and get the remainder. By hand, the digits of 12345 can be extracted like this:

=INT(12345/10000)        // returns 1
=MOD(INT(12345/1000),10) // returns 2
=MOD(INT(12345/100),10)  // returns 3
=MOD(INT(12345/10),10)   // returns 4

Instead of decoding each position on its own line, we decode all of them at once. The key is a small horizontal array of powers, one per position:

powers,SEQUENCE(,n,n-1,-1)  // a row: {3, 2, 1, 0}

Dividing the counter column by the n^powers row spreads the work across a grid: Excel pairs the single column against the four powers and returns a 256-row by 4-column matrix. The INT+MOD pattern decodes every position in one step:

picks,MOD(INT(counter/n^powers),n)+1

INT shifts each position into view by dividing by a power of n, and MOD reads off that position by taking the remainder. The +1 is there because the decode returns positions from 0 to n-1, but INDEX counts rows starting at 1, so we shift the range up to 1 through n. The result, picks, is a 256-row by 4-column matrix: one row per tuple, one column per position, holding every tuple from 1,1,1,1 to 4,4,4,4. Because the positions come from powers rather than hand-written lines, the same formula adapts to a list of any length on its own.

For a more complete explanation of this place-value decode, see Split number into digits.

Keep only the valid permutations

Most of those 256 tuples are not valid permutations. A tuple like 1,1,2,3 repeats the first item and skips the fourth, so it isn't an arrangement of all four items. A tuple is a valid permutation only when every position is different, because that is the only way each item gets used exactly once.

To identify valid permutations, we test each row of picks with the BYROW function and the UNIQUE function like this:

keep,BYROW(picks,LAMBDA(tuple,COUNT(UNIQUE(tuple,TRUE))=n))

For each row (tuple), UNIQUE(tuple,TRUE) lists the distinct positions in that row, and COUNT counts the result. When the count equals n, every position is different and the row is a valid permutation. The second argument TRUE tells UNIQUE to compare by column, so it removes duplicate values across the row rather than treating the whole row as one record. Out of the 256 tuples, exactly 24 pass this test, which matches 4! = 24 as expected.

Fetch actual values with INDEX

The positions in picks are just row numbers. To turn them into the actual items, we feed the whole matrix to the INDEX function at once:

INDEX(items,picks)

INDEX accepts the entire 256-by-4 matrix of positions and returns a matching matrix of values, looking each position up in items. This is what allows this formula to preserve type: numbers stay numbers and text stays text, because we are fetching actual values from the list rather than building strings. The FILTER function then keeps only the rows where keep is 1:

FILTER(INDEX(items,picks),keep)

The complete formula

Putting it all together, we have the formula from the top of the page. The LET function names each step so the logic reads top to bottom, and TOCOL normalizes the input so the list can be a row or a column:

=LET(
  list,B5:B8,
  items,TOCOL(list),
  n,ROWS(items),
  counter,SEQUENCE(n^n,,0),
  powers,SEQUENCE(,n,n-1,-1),
  picks,MOD(INT(counter/n^powers),n)+1,
  keep,BYROW(picks,LAMBDA(tuple,COUNT(UNIQUE(tuple,TRUE))=n)),
  FILTER(INDEX(items,picks),keep)
)

The result is a table of 24 rows by 4 columns, every possible ordering of the four items:

All 24 permutations of a 4-item list

You can confirm the count by checking the number of rows in the spilled result against PERMUT and FACT, which all return 24:

=ROWS(D5#)    // returns 24
=PERMUT(4,4)  // returns 24
=FACT(4)      // returns 24

The same formula works with text

Because the list keeps its original values via INDEX, the same formula works with text. When list is a range of letters, words, or names, the formula will continue to work properly. Here the list in B5:B8 is the four letters A, B, C, D:

The same formula listing all 24 arrangements of four letters

Nothing in the formula changes. This is a key advantage of looking up values with INDEX rather than building text strings: the result doesn't mangle your data.

When the list has duplicate values

The formula assumes the list contains four different items. If the list has a repeated value, swapping the two equal items produces two rows that look identical. For example, a list of 6,7,2,7 still returns 24 rows, but only 12 of them are actually distinct because the two 7s can trade places without changing what you see on screen. To remove these duplicates, wrap the whole formula in the UNIQUE function:

=UNIQUE( LET( ... same formula as above ... ) )

With 6,7,2,7 this returns the 12 distinct arrangements (24 ÷ 2 for the repeated 7).

Recursive lambda for more items

The on-sheet formula adapts to the length of the list on its own, but the generate-and-filter approach grows quickly: it builds n^n tuples before filtering, so it becomes impractical past about 7 items. For a longer list, define a reusable LAMBDA in Name Manager.

Go to Formulas → Name Manager → New, enter the name PERMS, and paste this into Refers to:

=LAMBDA(list,LET(
  v,TOCOL(list),
  n,ROWS(v),
  IF(n=1,v,
    REDUCE("",SEQUENCE(n),LAMBDA(acc,i,LET(
      e,INDEX(v,i),
      rest,FILTER(v,SEQUENCE(n)<>i),
      sub,PERMS(rest),
      m,ROWS(sub),
      block,HSTACK(IF(SEQUENCE(m,1),e),sub),
      IF(i=1,block,VSTACK(acc,block))
    )))
  )
))

Once defined, you can list every permutation of any list with a single, short formula:

=PERMS(B5:B10)  // spills 720 rows × 6 columns for a 6-item list

Listing all permutations with the reusable PERMS LAMBDA

This formula generates permutations by using a classic recursive idea: Choose each item once as the first item, then append all permutations of the remaining items. So, if we have a list like:

{"A";"B";"C"}

the formula works like this:

  • Pick A first → append permutations of B,C
  • Pick B first → append permutations of A,C
  • Pick C first → append permutations of A,B

Which creates:

A B C
A C B
B A C
B C A
C A B
C B A

Recursive formulas are hard to understand, so let's walk through the code step by step.

First, the formula normalizes the input into a single-column array with the TOCOL function:

v,TOCOL(list)

Then it counts the items in the list:

n,ROWS(v)

A recursive formula calls itself repeatedly until a condition is met. In this formula, the stop condition is:

IF(n=1,v,...)

This is the base case for the recursion. A one-item list has only one permutation, itself. For lists with more than one item, this part loops over each item number:

REDUCE("",SEQUENCE(n),LAMBDA(acc,i,...))

SEQUENCE spins up a numeric array beginning at 1 and ending at n. Inside the lambda, acc is an "accumulator" and i is the current counter, as REDUCE loops through the array created by SEQUENCE. For each i, the formula chooses one item:

e,INDEX(v,i)

Then it creates a small list without that item:

rest,FILTER(v,SEQUENCE(n)<>i)

Then the key recursive step, which asks for all permutations of the remaining items:

sub,PERMS(rest)

These permutations are assigned to sub which stands for sub-permutations. The next line counts the rows in sub:

m,ROWS(sub)

Next, the formula puts the previously selected item e in front of every row returned by the recursive call:

block,HSTACK(IF(SEQUENCE(m,1),e),sub)

The IF(SEQUENCE(m,1),e) trick repeats e vertically m times, so it can be stacked next to sub with HSTACK. Finally, each block is stacked under the previous blocks:

IF(i=1,block,VSTACK(acc,block))

The i=1 test is only there to avoid keeping the initial empty string provided to REDUCE. Here's a quick recap, tracing through the names used in the formula:

  • v is the list as a column and n is its length. When n is 1, the list is its own only arrangement, so the formula returns v and stops.
  • REDUCE loops i over each position from 1 to n, fixing a different item at the front on each pass.
  • e is that fixed item, and rest is the list with it removed, using FILTER to drop position i.
  • sub is the recursive call PERMS(rest): every arrangement of the items that remain.
  • m is the number of rows in sub, and block puts e in front of every row of sub: IF(SEQUENCE(m,1),e) builds a column of e repeated m times, HSTACK joins that column to the left of sub, and VSTACK stacks each block under the last to build the full result.

Because PERMS generates only the n! valid arrangements (not the n^n tuples the on-sheet formula filters down), it scales much further. It is still factorial, so it grows fast, but it handles larger lists comfortably. The same UNIQUE wrap shown above can be used to remove duplicate rows when the list has repeated values.

Permutations grow fast

Factorials grow extremely quickly, so the number of permutations explodes as the list gets longer:

=FACT(5)   // returns 120
=FACT(8)   // returns 40,320
=FACT(10)  // returns 3,628,800

A list of 10 items has more than 3.6 million arrangements, and a list of 13 items already exceeds the roughly 1 million rows in a worksheet. Check the size before you generate: even though the formula spills automatically, a long list can produce far more rows than Excel can hold.

Summary

Listing every permutation of a list comes down to generate-and-filter, built on the same odometer decode used for combination lock codes:

  • Count through every position tuple with SEQUENCE, then decode every position at once with a powers row, INT, and MOD.
  • Keep only the tuples where every position differs, so each item is used exactly once.
  • Pull the real values by handing the whole position matrix to INDEX, then filter with FILTER.
  • For a list of any length, use the recursive PERMS LAMBDA, which generates only the valid arrangements.

For the difference between permutations and combinations, see Combinations and permutations in Excel.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Related formulas

Excel List all combination lock codes

List all combination lock codes

To list every possible combination lock code (every fixed-length numeric code), you can use a formula based on the SEQUENCE function and the TEXT function. In the example shown, a single formula spills all 10,000 four-digit codes from 0000 to 9999. The same place-value idea splits each code into …

Related functions

Excel SEQUENCE Function

SEQUENCE Function

The Excel SEQUENCE function generates a list of sequential numbers in an array. The array can be one dimensional, or two-dimensional, determined by rows and columns arguments.
Excel INDEX Function

INDEX Function

The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column numbers.
Excel FILTER Function

FILTER Function

The Excel FILTER function is used to extract matching values from data based on one or more conditions. The output from FILTER is dynamic. If source data or criteria change, FILTER will return a new set of results. This makes FILTER a flexible way to isolate and inspect data without altering the …
Excel LET Function

LET Function

The Excel LET function lets you define named variables in a formula. There are two primary reasons you might want to do this: (1) to improve performance by eliminating redundant calculations and (2) to make more complex formulas easier to read and write.
Excel BYROW Function

BYROW Function

The Excel BYROW function applies a function to each row of a given array and returns one result per row. BYROW can apply stock functions like SUM, COUNT, and AVERAGE or a custom LAMBDA function. All results are returned in a single array, one result per row.
Excel UNIQUE Function

UNIQUE Function

The Excel UNIQUE function extracts unique values from a range or array. The result is a dynamic array that automatically updates when source data changes. UNIQUE is not case-sensitive and works with text, numbers, dates, and other data types.
Excel COUNT Function

COUNT Function

The Excel COUNT function returns a count of values that are numbers. Numbers include negative numbers, percentages, dates, times, fractions, and formulas that return numbers. Empty cells and text values are ignored.
Excel LAMBDA Function

LAMBDA Function

The Excel LAMBDA function provides a way to create custom functions that can be reused throughout a workbook, without VBA or macros.
Excel TOCOL Function

TOCOL Function

The Excel TOCOL function transforms an array into a single column. By default, TOCOL will scan values by row, but TOCOL can also scan values by column.
Excel HSTACK Function

HSTACK Function

The Excel HSTACK function combines arrays horizontally into a single array. Each subsequent array is appended to the right of the previous array.
Excel VSTACK Function

VSTACK Function

The Excel VSTACK function combines arrays vertically into a single array. Each subsequent array is appended to the bottom of the previous array.
Excel REDUCE Function

REDUCE Function

The Excel REDUCE function applies a custom LAMBDA function to each element in a given array and accumulates results to a single value.
Excel INT Function

INT Function

The Excel INT function returns the integer part of a decimal number by rounding down to the integer. Note that negative numbers become more negative. For example, while INT(10.8) returns 10, INT(-10.8) returns -11.
Excel MOD Function

MOD Function

The Excel MOD function returns the remainder of two numbers after division. For example, MOD(10,3) = 1. The result of MOD carries the same sign as the divisor.
Excel ROWS Function

ROWS Function

The Excel ROWS function returns the count of rows in a given reference. For example, ROWS(A1:A3) returns 3, since the range A1:A3 contains 3 rows.
Excel PERMUT Function

PERMUT Function

The Excel PERMUT function returns the number of permutations (combinations where order is significant) for a given number of items. The PERMUT function does not allow repetitions. To allow permutations with repetitions, use the PERMUTATIONA function.
Excel FACT Function

FACT Function

The Excel FACT function returns the factorial of a given number. For example, =FACT(3) returns 6, equivalent to 3 x 2 x 1.

Formulas

Related functions

Articles