How to Make Random Groups in Excel & Google Sheets

This tutorial will demonstrate how to make random groups in Excel and Google Sheets.

 

Random Groups

To randomly assign items (data, people, etc.) to groups we can use the RANDBETWEEN and CHOOSE Functions:

RANDBETWEEN Function

The RANDBETWEEN Function generates random numbers between two integers. In this example it will generate numbers 1, 2, 3, or 4.

=RANDBETWEEN(1,4)

CHOOSE Function

The CHOOSE Function selects an item from a list by it’s position.

=CHOOSE(3,"TEAM A","TEAM B","TEAM C","TEAM D")

By combining the two functions we can randomly “choose” an item from a list, thus assigning people to groups as in the first example.

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

Random Groups – Same Size

The example above will assign people to completely random groups. The resulting groups may not be of the same size.

To assign people to same sized groups, we can use the ROUND, ROUNDUP, and RANK Functions:

This calculation is a bit more complicated, so let’s walk you through it.

RAND Function

First, we use the RAND Function to generate random decimal values between 0 and 1 for each person:

=RAND()

Now that we have random numbers for each person we have a way to split them into groups.

RANK Function

Next we will use the RANK Function to identify where each random value “ranks” in the list of random values.

=RANK(A2,$A$2:$A$11)

The RANK Function will return a number between 1 and the total number of people in the group. The largest random number will receive 1, second largest will receive 2, etc.

Next we will divide the result of the RANK Function by the desired group size. (ex. 4 for a group size of 4 people per group).

=RANK(E2,$E$2:$E$11)/3

Notice that this returns decimal values between 0 and 4.

ROUNDUP Function

Last, we will apply the ROUNDUP Function to the result to round the decimal values up to integer values 1,2,3, and 4.

=ROUNDUP(RANK(E2,$E$2:$E$11)/3,0)

This gives us four groups of equal size.

Note: Group 4 only has 1 person assigned, because there are not enough names shown to fully populate group 4.

Note 2: By setting the num_digits input to 0, we tell the ROUNDUP Function to round up to the nearest integer.

Random Groups in Google Sheets

All of the above examples work exactly the same in Google Sheets as in Excel.