How to Randomly Sort Names into Groups in Excel
Question details
The user needs to divide a list of names (such as 84 people from 11 departments) into a specific number of random groups (e.g., 10 groups).
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating unbiased, random team or group assignments from a large list of employees, students, or participants.
- Observed behavior
- Requires a dynamic formula-based method to generate random values, rank them, and assign corresponding group numbers that recalculate properly.
Ensure your list of names is organized in a single column without any blank rows, and determine the exact number of random groups you want to create before applying formulas.
Use RAND, RANK.AVG, and MOD Formulas to Assign Groups
Combine Excel's random number generator with ranking and modulo functions to evenly and dynamically distribute names into random groups.
This method uses three separate columns to generate a random number, rank that number within the entire list, and then sort those ranks into evenly distributed groups.
Create a new column next to your list of names. In the first cell of this column (e.g., B2), enter the formula =RAND() to generate a random decimal number.
Create a second new column. In cell C2, enter the formula =RANK.AVG(B2,$B$2:$B$85). Adjust the range $B$2:$B$85 to match the exact start and end of your data set. This ranks the random number against all others.
Create a third new column. In cell D2, enter the formula =(MOD(C2,10))+1. The number 10 represents the total number of groups you want to create. Change this number if you need more or fewer groups.
Select the three cells containing your new formulas (B2, C2, and D2). Click and drag the fill handle (the small square at the bottom-right corner) down to the bottom of your list to apply the group assignments to all names.
Randomly Group Names with WPS Spreadsheet
WPS Spreadsheet fully supports advanced mathematical and logical functions like RAND, RANK, and MOD, making it incredibly easy to randomize and organize your datasets without hassle.
- 1. Open Your Data: Launch WPS Spreadsheet and open the document containing your list of names.
- 2. Insert Random Numbers: Type =RAND() in the adjacent column to generate random values for each name.
- 3. Rank and Group: Use the =RANK.AVG() and =MOD() formulas exactly as you would in Excel to divide your list into the desired number of groups.
- 4. Lock the Results: Once satisfied with the random grouping, copy the results and use 'Paste as Values' to prevent them from recalculating.
Frequently Asked Questions
How do I stop the random groups from changing every time I edit the sheet?
Because the RAND() function automatically recalculates, you need to lock the values. Select the column containing your final group numbers, copy it, right-click the first cell of the column, and choose 'Paste as Values' (the clipboard icon with '123'). This replaces the dynamic formulas with static numbers.
Can I divide the list into 5 groups instead of 10?
Yes. To change the total number of groups, simply update the number in the MOD function. For example, changing the formula to =(MOD(C2,5))+1 will divide your list into exactly 5 random groups.
Why do I get an error when I drag the RANK.AVG formula down?
This usually happens if you forget to use absolute references for your data range. Ensure your formula uses dollar signs, like $B$2:$B$85, so the reference range stays fixed and does not shift downwards as you copy the formula.
