Random Team Shuffling & Team Generator: The No-Code Method on Google Sheets

Image for post
Image for post

This is Part 1 of my Teams and Google Workspace series, where I cover tools and solutions built in Google Workspace for teams.

Are you in a meeting where everyone has to take turns doing something, and you want to randomly create that order? A couple simple formulas in Sheets will let you quickly do that!

What you’ll need

  • Just a Google Account to create a Sheet — a free Gmail one will do!

The key formulas we will be using

Learn how to create random numbers withRAND(), RANDARRAY(), and sort lists withSORT() .

Sample Sheet

TL:DR version: Copy this Sample Sheet and put your team member list in Column A to get your randomized list!

Steps

1. List your team members

Create a new Google Sheets (shortcut: sheets.new), and list your team members in column A.

List everyone in the team
List everyone in the team

2. Generate random numbers

Random number functions in Sheets are incredibly useful in cases when you have to, well, randomize things 🙂 The =RAND() formula returns a decimal number between 0 and 1. We will use this formula in Column B next to each name to give a random number to each team member in Column A.

Level up! To make this Sheets reusable for teams of different sizes, instead of copying the =RAND() formula down for each team member, we can use the =RANDARRAY(row,col) function, which will give you a table/two dimensional array of random numbers, with the number of rows and columns you specified in the formula. In this case, we need 1 column, and number of rows will equal the number of team members. To determine the number of team members, we can use the =COUNTA(A:A) formula, which will give me the number of non-blank values in column A (aka range A:A).
Thus our formula becomes =RANDARRAY(COUNTA(A:A),1). You can put that in Cell B1 and now whenever you change the team member list, you can see the number of random numbers generated will reflect how many team members are listed.

3. Sort to get your randomized shuffled list!

Finally, you just need to sort your name list (Column A) by the random number (Column B), which can be done with the SORT function – adding this to Cell C1 will do the trick:

=SORT(A:A,B:B,true)

And that’s it! The random numbers are regenerated everytime you reload the file or edit the sheet, so you can easily generate new ordering anytime.

Extra: Changing this into a random team generator

Suppose you have to randomly divide your team into smaller teams or breakout groups. Now that we have a shuffled list, you can simply go top-down and assign people into team (e.g. if you need teams of 3, first 3 on the list is team #1, then the next 3 is team #2, and so forth) — you can easily do that by adding this formula to cell D1 (replace {team size} with the number of people each smaller team should have:

=floor((row(A1) - 1) /{team size})+1

Next, drag the formula down to give each person a team number.

Image for post
Image for post

Level Up! Again, to make this work for flexible team sizes, modify the formulas in Column D to use a single arrayformula function so you don’t need to drag the formulas down. I can also designate a cell on the sheet for entering team sizes so I don’t need to type it into the formula.

Finally, pretty up the sheet with a bit of colour and labels (you may need to adjust formulas based on changes to the column positions). The final product can look something like this (See the Full Sheet here):

Image for post
Image for post

And there you go! A re-usable random team ordering and team generator tool! Use it for:

  • Determining daily standup / scrum speaking / presentation order
  • Breakout session assignments
  • Team building activities to break teams into smaller groups

Visit my blog or follow me on twitter for more Google Workspace content!

Cleo is a Google Developer Expert for Google Workspace based out of Vancouver, BC, Canada.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store