How to 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:

Excel generate random groups

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)

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

Excel generate random groups

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.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

How to Generate Random Number Between Range in Excel
How to Randomly Select Cells Based on Criteria in Excel
How to Select a Random Sample in Excel

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *