Create Random Groups in Excel (With Example)

---

Often you may want to create random groups in Excel.

For example, you might want to assign 12 basketball players to one of three random teams:

Fortunately this is easy to do and the following step-by-step example shows how to do so.

Step 1: Enter Original Data

First, let’s enter the names of 12 basketball players that we’d like to assign to random teams:

Step 2: Generate Random Values

Next, we will generate a random number between 0 and 1 for each player by typing the following formula into cell B2:

=RAND()

We can then click and drag this formula down to each remaining cell in column B:

Each player now has a random value associated with them between 0 and 1.

Step 3: Generate Random Groups

Next, we will assign each player to a random group.

To do so, we will type the following formula into cell C2:

=ROUNDUP(RANK(B2,$B$2:$B$13)/4,0)

Column C now assigns each player to one of three random teams.

For example:

• Andy has been assigned to Team 2.
• Bob has been assigned to Team 2.
• Chad has been assigned to Team 1.

And so on.

Note that the value after the division symbol in the formula specifies the number of players to include in each group.

For example, we could change this number from 4 to 6 to instead include 6 players on each team:

=ROUNDUP(RANK(B2,$B$2:$B$13)/6,0)

We can type this formula into cell C2 and then click and drag it down to each remaining cell in column C:

Notice that six players are now assigned to Team 1 and six players are assigned to Team 2.

Feel free to change the number after the division symbol in the formula to assign a different number of players to each team.