Randomly assign data to groups

In this example, the goal is to return a random group ("A", "B", or "C") at each new row. The simplest way to do this is to use the RANDBETWEEN function with the CHOOSE function. In the current version of Excel, it is also possible to generate all random groups in one step with the RANDARRAY function. Both approaches are explained below.

The CHOOSE function

The CHOOSE function returns a value from a list of values using an index number. The index number is provided as the first argument, and the values to be selected follow. For example, if we have a list of three colors ("red", "blue", and "green"), we can configure CHOOSE to return each color in turn with the following formulas:

CHOOSE(1,"red","blue","green") // returns "red"
CHOOSE(2,"red","blue","green") // returns "blue"
CHOOSE(3,"red","blue","green") // returns "green"

Notice that CHOOSE uses the index number to select the "nth" value from the list of values. The values can be customized in any way you like and the only requirement is that the index number be valid for the number of values provided. Of course, in this example, we don't want to hardcode an index number into CHOOSE, we want a random index number. For this, we can use the RANDBETWEEN function.

The RANDBETWEEN function

The RANDBETWEEN function generates a random number between two integers, provided as the bottom and the top. For example, to generate a random number between 1 and 10, you can use RANDBETWEEN like this:

=RANDBETWEEN(1,10) // returns a random number between 1 and 10

When Excel's calculation engine updates a worksheet, RANDBETWEEN will generate a random number between 1 and 10.

CHOOSE with RANDBETWEEN

The behavior of RANDBETWEEN will work perfectly for this problem. We have three possible groups ("A","B","C") so we need a random number between 1 and 3, which we can get like this:

=RANDBETWEEN(1,3) // returns a random number between 1 and 3

The final step is to embed RANDBETWEEN into the CHOOSE function as the index number like this:

=CHOOSE(RANDBETWEEN(1,3),"A","B","C")

This is the formula that appears in cell F5 in the example shown. When the formula is copied down the column, RANDBETWEEN returns a random number between 1 and 3. This number is delivered directly to the CHOOSE function as the index number, and CHOOSE returns the corresponding color as a final result. You can use this approach whenever you need to assign random text values to each row in a data set. Just be sure to adjust the second argument in RANDBETWEEN, top, to match the number of values provided.

Stopping automatic recalculation

Be aware that RANDBETWEEN is a volatile function and will recalculate whenever there is any change to a workbook, or even when a workbook is opened. To force a recalculation, you can press the F9 key. Once you have a set of random assignments, you may want to stop the formula from returning new results. The classic way to do this is to use Paste Special:

1. Select all cells that contain the CHOOSE and RANDBETWEEN formula.
2. Copy to the clipboard with Control + C.
3. Open the Paste Special window with the shortcut Control + Alt + V.
4. Select "Values" and click OK:

After you press OK, all formulas will be replaced with static values.

Dynamic array formula

In the current version of Excel (Excel 2021 or later) you can use a single dynamic array formula to generate all random values at once. One option is to use the RANDARRAY function with CHOOSE like this:

=CHOOSE(RANDARRAY(ROWS(B5:B104),,1,3,TRUE),"A","B","C")

The core idea of this formula is the same as the original formula above. However, instead of RANDBETWEEN, we use RANDARRAY, which can generate an array of random numbers in one step. To figure out how many random numbers to generate, we use the ROWS function on a range corresponding to the first column of the data. This saves us the step of telling RANDARRAY how many rows we need. In this case, ROWS returns 100, because there are 100 rows in the range B5:B104. Simplifying, we now have:

=CHOOSE(RANDARRAY(100,,1,3,TRUE),"A","B","C")

Next, RANDARRAY generates an array of 100 random numbers between 1 and 3. The result is returned to CHOOSE as the index_num argument, and CHOOSE uses the random numbers to return an array that contains 100 random groups. This array lands in cell F5 and spills into the range F5:F104.

INDEX alternative

It is also possible to use the INDEX function instead of CHOOSE in a formula like this:

=INDEX({"A","B","C"},RANDBETWEEN(1,3))

Like CHOOSE, INDEX retrieves a value based on an index number. INDEX however accepts the values all at once in the first argument, called array. In the formula above, the values "A", "B", and "C" are provided as an array constant to INDEX as the array, and RANDBETWEEN is used as before to generate a random number between 1 and 3.  The RANDARRAY version of the formula with INDEX looks like this:

=INDEX({"A","B","C"},RANDARRAY(ROWS(B5:B104),,1,3,TRUE))

One advantage of INDEX is that the array constant can be replaced with a range on the worksheet. In other words, you can enter group names into a range and provide that range to INDEX. The CHOOSE function will not accept a range of values; it requires that values be provided separately.

Note: the formulas on this page will create completely random groups. One result is that the total number of rows assigned to each group will vary. If you need to assign random groups with a fixed size (i.e. randomly assign people to teams of 6), see the example on this page.