Random Group Generator Template [FREE Download]

If you’re a teacher or a trainer, creating groups of students/participants is a common task. For example, you may want to create groups to conduct a quiz or a team building activity.

And in most of the cases, you need these groups to be random.

Today, I am sharing a random group generator template that will make it super easy for you to create a group of students/participants.

All you need is the list of students or participants and specify how many groups you want to create.

Random Group Generator Template

Here is a demo of how this random group generator (or random team generator) template works:

The list of students/participants is in A2:A17. If you have a longer list, simply add the names in it.

Cell E2 has the number of groups that you want to create. Based on the number you enter, you would get the groups and the names in each group in columns G to P. As of now, I have created it this template for a maximum of 10 groups.

Once you have entered the number of groups you want in cell E2, click on the ‘Create Teams’ button to randomly generate the groups of names.

Download the Random Group Generator Template

How this Excel Template Works

There are a couple of cool Excel features and a few helper columns that make this random group generator template in Excel.

Here is how it is made:

• A2:A17 contains the list of names that are to be grouped randomly.
  • A1:C17 has been converted into an Excel Table. This helps to keep the formulas intact when you add/remove names from the list.
• Column B has the formula: RANDBETWEEN (1, COUNTA ([Names])) + ROW ()/100
  • The function returns a random number between 1 and the total number of names in the list (using the COUNTA function). To this, ROW()/100 is added to make it unique (as the RANDBETWEEN function can spit out duplicates as well).
• Cell C2 has the formula: RANK ([@Unique],[Unique])
  • This function gives the rank for each value in Column B. Since all the values in column B are unique, this formula gives a unique list of integers that vary from 1 to the total number of names in the list.
• Cell G1 has the formula: IF(COLUMNS ($G$1:G1)>$E$2,””,COLUMNS($G$1:G1))
  • The same formula is copied in cells H1 to P1. It returns the number of columns between column G and the current column. Hence, G1 gets 1, H1 gets 2 and so on. It also checks whether the number is greater than the value in cell E2. If yes, then it returns a blank.
• Cell G2 has the formula: 

  =IFERROR(IF(G$1<>””, INDEX (Table1[Names],INDEX(Table1[Rank],G$1+$E$2*(ROWS($F$2:F2)-1))),””),””)

  • It is copied to all the cells in G2:P17.
    • In cell G2, this formula will pick up the rank from C2 and return the name at that position in the list.
    • In cell G3, it will pick the rank from C6 (which is 1 + 1*4, where 4 is the number of groups to be formed).
    • In cell G4, it will pick the rank from C10 (which is 1 + 2*4, where 4 is the number of groups to be formed).
  • If the cell in the first row is empty or the result of the formula is an error, it returns a blank.

Since RANDBETWEEN function is volatile, it will automatically refresh every time you make a change in the worksheet. This may be undesirable as it will change the grouping every time.

To avoid this:

• Go to File Options.
• In the Excel Options dialog box, select formulas in the pane on the left.
• In the Calculation options, make Workbook Calculation Manual.
• Click OK.

Now the worksheet would not refresh until you force a refresh by hitting the F9 key.

But to make it look better, there is an orange button that does the refresh when you click it. There is a one-line VBA code at play here that gets executed whenever you click the button.

Here is how to insert this button:

• Go to Developer –> Code –> Visual Basic. (You can also use the keyboard shortcut Alt + F11).
• In the VB Editor right-click on any of the objects for the workbook and go to Insert –> Module.
• In the module code window, copy-paste the following code:

  Sub Refresh()
  Worksheets("Team Generator").Calculate
  End Sub

  • Note that the name of the worksheet is in double-quotes. If your worksheet name is different, change it in the code.
• Close the VB Editor.
• Go to Insert –> Shapes and insert any shape that you want as the button.
• Right-click on the shape and click on Assign Macro.
• In the Assign Macro dialog box, select the macro name and click on OK.
• Format the button the way you want.

Now when you click on the button, the worksheet would recalculate and you would get a new grouping based on the number of groups you have specified.

Download the Random Group Generator Template

Other Excel Templates You May Like:

You may also like the following Excel tutorials: