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 - Cover

Random Group Generator Template

Here is a demo of how this random group generator template works:

Random Group Generator Template - Demo

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 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.Random Group Generator Template - Excel Table
  • 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).Random Group Generator Template - unique
  • 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.Random Group Generator Template - rank
  • 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.Random Group Generator Template - Group Number
  • 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.Random Group Generator Template - Team Member

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.Random Group Generator Template - 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).Random Group Generator Template - VB
  • In the VB Editor right click on any of the objects for the workbook and go to Insert –> Module.Random Group Generator Template - Insert Module
  • In the module code window, copy paste the following code:
    Sub Refresh()
    Worksheets("Team Generator").Calculate
    End Sub
    • Note that name of the worksheet is in double quotes. If your worksheet name is different, change it in the code.Random Group Generator Template - Code
  • Close the VB Editor.
  • Go to Insert –> Shapes and insert any shape that you want as the button.Random Group Generator Template - Shape insert
  • Right click on the shape and click on Assign Macro.Random Group Generator Template - Assign Macro
  • In the Assign Macro dialog box, select the macro name and click on OK.Random Group Generator Template - Select Macro
  • 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:
Suggested Tutorials:
  • Rudra Mani

    Cool trick Sumit. Thanks for sharing. However there is a short cut to change calculation from ribbon itself. Just go to formula ribbon and click on Calculation Option.

    • Thanks for sharing Rudra.. That’s definitely the faster way to do this.

  • Torstein S Johnsen

    Thanks to both of you, Sumit and Rudra!
    Sumit, I enjoy your formulabased random generator. I have my ovn VBA-generator that I will continue using,but I learned a lot from your reallønn Nice formulas.

    Rudra! I’ve never notised the easy way of changing calculation mode. I still miss a warning light when ExCeL is in manual mode. Forgetting to return to aut. Mode has caised me lot f extra work.

    • Hennie Joubert

      From the Custom Quick Access Toolbaar, add “Manual” to the Quick Access Toolbaar and it will show when in Manual mode.

      • Torstein S Johnsen

        Thanks Hennie, I’ve already adferd it to QAT.

  • Fotows Nikon

    Dear Sumit,

    I really enjoy using your random group generator for my classes (I created a tab for each class. It’s swift and easy to use.

    Sometimes, however someone in a class is absent (visit to the dentist, etc.).
    If so, I have to alter the table, to remove the absent student.

    It would be nice if there could be a column next to the student names where I could mark the absent student(s) (for example with a zero) , so he/she won’t be displayed in the generated groups.

    Unfortunately I don’t have the programming skills to make that happen.

    Someone else perhaps?

    Greetings,
    Willem
    (Netherlands)

  • Michael

    Hi Sumit!
    This is amazing for my classroom thanks so much!
    Is there any way to increase the max number of groups? I really need 12 groups!

  • asri

    Cool trick, but i have one scenario. let’s say i have names(2 or more) that cannot be teamed together,is there any way to solve it?

  • Nabbetorp76

    Is there a way to set group 1 to have 10 members, and the others to have like 7 ?