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 + 14, where 4 is the number of groups to be formed). In cell G4, it will pick the rank from C10 (which is 1 + 24, 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:
Employee Leave/Vacation Tracker Template. Employee Timesheet Calculator. Excel To Do Lists Templates. A collection of FREE Excel Templates.
You may also like the following Excel tutorials:
How to Generate Unique Random Numbers in Excel. How to Run a Macro in Excel. How to Create an Excel Dashboard. How to Rank within Groups in Excel How to Shuffle a List of Items/Names in Excel? 2 Easy Formulas!
I just need 1 Team where I need to mention the no of members in cell E2 and it creates a team with Random Names. I am confident its a piece of cake for you. 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) Here is a link to a new template that will allow you to mark a student as absent: https://www.dropbox.com/s/ys1mmwmbdy7eeb5/Random-Team-Generator-Template%20Absent.xlsm?dl=0 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.
