Quick, clean, and to the point

This video is part of our online formula course for Excel.

How to randomly assign people to teams

In this video, we look at a step by step approach to randomly assign people to groups with teams using formulas. Along the way, we use three functions (RAND, RANK, and CEILING), helper columns, and nesting. We verify results with the COUNTIF function.
Video Transcript 

How to randomly assign people to teams

In this video we'll look at a basic way to randomly assigning people teams.

Here we have a list of 36 people.

Let's say we want to randomly assign each person to a team of 4 people, so that we have a total of 9 with 4 people in each.

I'm going to solve this problem in small steps, with helper columns, then bring things together in the end. This is a great way ot solve more complicated problems in Excel.

I'll start off with an Excel Table, to make the formulas very fast to enter. 

Then, I'll add columns for Rand, rank, grouping and Team number. The purpose of each column will become clear as we go along.

Next, I'll use the RAND function to assign a random number to each person. RAND generates small numbers between zero and 1.

RAND is a volatile function, so it will recalculate with every worksheet change. We don't want that behavior, so I'll use paste special to convert the formulas to values.

Next, I'll use the RANK function to rank each person according to their random number. RANK needs the number, and a list of numbers to rank against.


The result is a list of numbers between 1 and 36, where 1 represents the largest value, and 36 represents the smallest.

We're getting close.

We just need a way to group by rank.

I'll do this by dividing rank by the team size, which is 4.


This produces some messy numbers, but, we now have what we need.

If we round these numbers up, we'll have team numbers between 1 and 9. This is a perfect job for the CEILING function, which rounds up to a given multiple.

I need to give CEILING the number, and specify a multiple of 1, and we have our teams.


Now, to make sure this is working right, I'll use the COUNTIF function to count team members.

Next, I'll replace the hard-coded team size with a reference.


Now when I change the team size, everything still work.

Finally, I'll consolidate formulas.

First I'll copy in the grouping formula.


Next I'll copy in the rank formula.


Now I can delete the two helper columns.

To generate new teams at any time, I can again use the RAND function.

Dave Bruns

Related shortcuts

Thanks a lot, this is perfectly working and saving me hours. - Sven
Excel foundational video course
Excel Pivot Table video training course
Excel conditional formatting video course
Excel formulas and functions video training course
Excel Shortcuts Video Course