Cleo Espiritu

Fun with Teams and Google Workspace 1: Team Shuffle / Random Team Generator

It’s been a while! Honestly, with the deprecation of App Maker and how 2020 has been (it’s a year for the books, that’s for sure!), I decided to take a break and to find a new direction for my blog series.

As I looked through my Drive, I realized that I have a number of mini Google Sheets and Forms tools and solutions that I have used in the past for working with teams – I thought it’d be a perfect series of articles to create as my first new content under the new Google Workspace branding! These will be a mix of no-code and low-code solutions that anyone can build to use with your teams!

Without further ado, let’s kick off with our first tutorial!

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

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!

Formulas we will cover

How to generate random numbers and sort a list using Google Sheets formulas (RAND(), RANDARRAY(), and SORT)

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 names in Column A
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.

Random Number Generator in Sheets

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.

Generate a list of random number with a single formula RANDARRAY
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)
Using the SORT formula produces a randomized list in column C

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.

Adding a column that'll assign a team number to each member to divide them into subteams

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.

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

Final product. Added some headers to make it more user friendly

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

Next time, I will show you the low-code version of this random team generator tool.