Random Teams in Google Sheets: 2 Formula Methods

To make random teams in Google Sheets, add a helper column with =RAND(), then either sort your names by it and split them into blocks, or assign a team number with =MOD(RANK(B2,$B$2:$B$13)-1,4)+1. Both shuffle fairly. The formula method keeps team sizes even on its own.

Google Sheets handles this well, and it’s the natural choice when your names already sit in a shared sheet. Below are two reliable methods, a one-line shuffle using RANDARRAY, and the recalculation gotcha to watch for. If it turns into more effort than the split is worth, there’s a faster route at the end.

Method 1: Sort by a random column (simplest)

Shuffle the list, then cut it into equal teams.

1. Put your names in column A, from A2 down. Say 12 names in A2:A13.
2. In B2, type =RAND() and fill it down to B13. Each cell gets a random number between 0 and 1.
3. Select A2:B13, open the Data menu, choose Sort range, and sort by column B, A to Z. Your names are now shuffled.
4. Split into teams. For 4 teams of 3, the first 3 names are team 1, the next 3 are team 2, and so on. Label them in column C if you like.

The sort is a real shuffle, so every ordering is equally likely and no name is favoured.

Watch out: =RAND() recalculates whenever the sheet changes, which reshuffles column B and can re-scramble your work. Before sorting, freeze the values. Select column B, copy it, then use Edit, Paste special, Paste values only. The numbers are now fixed.

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

No re-sorting, and it balances team sizes automatically. RANK turns the random numbers into a shuffled order, and MOD deals teams out round-robin.

1. Names in A2:A13.

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

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

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

   Change the 4 to your number of teams, and $B$2:$B$13 to your range.

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

It stays even because RANK gives each random value its place in the shuffled order, 1 to 12, and MOD(...,4) cycles those through the teams in an unbroken sequence. Twelve names into four teams gives three each. Thirteen would give 4, 3, 3, 3, with the extra on team one.

Same gotcha. RAND recalculates on every edit. Once the teams look right, freeze column C with Paste values only so they don’t shuffle again.

Method 3: One-line shuffle with RANDARRAY

Google Sheets has a tidy trick Excel makes harder. RANDARRAY generates a block of random numbers you can sort by in a single formula, no helper column to freeze manually:

=SORT(A2:A13, RANDARRAY(ROWS(A2:A13)), TRUE)

Drop that into an empty cell, say D2, and it outputs your names in a fresh random order. Split the shuffled output into teams by reading down in blocks. It still recalculates on edits, so copy the result and Paste values only when you want to lock it. This is the fastest formula route if you just need a shuffled list.

Team size instead of team count

Want teams of a fixed size rather than a fixed number? After freezing your random order, use:

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

The /4 groups every block of 4 ranked names into one team. Change the 4 to your team size. A group of 13 into teams of 4 leaves one person over on their own team, so you’d rebalance that by hand. That’s the point where a spreadsheet starts fighting you.

Where Google Sheets gets clumsy

The formulas shuffle fairly and keep sizes even, which handles a plain split. Anything beyond that adds friction:

• Keeping two people together or apart means editing by hand after each reshuffle.
• Spreading captains evenly needs extra helper columns.
• Odd numbers leave you rebalancing manually.
• Fairness is hard to prove, since a shared sheet can be edited by anyone afterward.

Each is doable, but you redo the work every time you reshuffle, and in a shared sheet someone can quietly change a result.

Or skip the formulas

If this is more setup than the split deserves, the random team generator does the whole thing in one click. Paste your names, choose team count or size, and it shuffles and balances instantly. Keep-together, keep-apart, and captain-spreading are built in, and the share link reopens the exact same teams as proof the draw was random.

For specific settings, the classroom group generator and office team generator tailor the options. Using Excel instead of Sheets? The random team generator in Excel guide covers the same methods there. And to understand the fair-split logic behind all of this, see how to make random teams.