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)

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

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