Random Team Generator in Excel: 2 Formula Methods

To make random teams in Excel, add a helper column of random numbers with =RAND(), then either sort your names by that column and split them into blocks, or assign a team number with =MOD(RANK(B2,$B$2:$B$13)-1,4)+1. Both give a fair shuffle. The formula method keeps team sizes even automatically.

Excel can absolutely do this, and it’s worth knowing if your names already live in a spreadsheet. Below are two methods that work in any recent version, plus the one gotcha that trips everyone up (RAND recalculating). If you’d rather skip the setup, there’s a faster route at the end.

Method 1: Sort by a random column (simplest)

This is the quickest to understand. You shuffle the list, then chop it into equal teams.

1. Put your names in column A, starting at A2. Say you have 12 names in A2:A13.
2. In B2, type =RAND() and fill it down to B13. Each cell now holds a random number between 0 and 1.
3. Select A2:B13, go to the Data tab, and click Sort. Sort by column B, smallest to largest. Your names are now in random order.
4. Split the sorted list into teams. For 4 teams of 3, the first 3 names are team 1, the next 3 are team 2, and so on. Add a team label in column C if you want it written down.

That’s it. The sort is a genuine shuffle, so every arrangement is equally likely.

The catch: =RAND() recalculates every time the sheet changes, which reshuffles column B and can re-scramble things mid-task. Before you sort, it’s safest to freeze the values: select column B, copy it, then Paste Special and choose Values. Now the numbers are fixed and won’t jump around while you work.

Method 2: Assign a team number with a formula (keeps sizes even)

This one doesn’t need re-sorting, and it balances team sizes for you. It uses RANK to turn the random numbers into a shuffled 1-to-N order, then MOD to deal teams out round-robin.

1. Names in A2:A13 as before.

2. In B2, =RAND(), filled down to B13.

3. In C2, enter this formula and fill it down to C13:

   =MOD(RANK(B2,$B$2:$B$13)-1,4)+1

   Change the 4 to however many teams you want. Change $B$2:$B$13 to match your range.

4. Read column C. Each name now has a team number from 1 to 4. All the 1s are team one, the 2s are team two, and so on.

Here’s why it stays even. RANK gives each random number its position in the shuffled order, 1 through 12. MOD(...,4) cycles those positions through 0, 1, 2, 3, repeating, and the +1 shifts it to 1 through 4. Because the ranks run in an unbroken sequence, the teams fill evenly. Twelve names into four teams gives three each. Thirteen names would give sizes of 4, 3, 3, 3, with the extra landing on team one.

Same catch applies. RAND recalculates on every edit, which reshuffles the teams. Once you’re happy, freeze it: copy column C, then Paste Special as Values. The teams are now locked.

Method 3: Team size instead of team count

If you want teams of a fixed size, say 4 people per team, rather than a fixed number of teams, tweak the formula. After freezing your random order, number each row and divide:

=INT((RANK(B2,$B$2:$B$13)-1)/4)+1

That /4 means every block of 4 ranked names shares a team number. Change the 4 to your team size. A group of 13 into teams of 4 gives three full teams and one team of one, so you’d rebalance the odd person by hand. This is where a spreadsheet starts to show its limits.

What Excel can’t do easily

The formulas above shuffle fairly and keep sizes even, which covers the basics. But the moment you want anything beyond a plain split, Excel gets fiddly:

• Keeping two people together or forcing two apart means manual edits after every reshuffle.
• Spreading captains one per team needs a second helper column and more formula work.
• An odd number leaves you rebalancing by hand.
• Proving it was fair is hard, since anyone can edit the sheet after the fact.

None of these are impossible in Excel, but each adds steps, and you’re redoing them every time you reshuffle.

Or skip the spreadsheet entirely

If the formulas feel like more than the job’s worth, the random team generator does all of this in one click. Paste your names, pick the number of teams or the size, and it shuffles and balances for you. Keep-together, keep-apart, and captain-spreading are built in, no helper columns required. There’s a share link that reopens the exact same teams, which is proof the split was random.

For splitting a class or a work group specifically, the classroom group generator and office team generator add options tailored to those settings. And if your names live in Google Sheets rather than Excel, the random teams in Google Sheets guide covers the same methods there.