To randomize people into groups in Excel, add a column with =RAND() beside each name, fill it down, sort the entire list by that column, and assign group labels down the shuffled list. This creates a random order; assigning labels in sequence keeps group sizes as even as possible.
What Randomizing Groups Means
People often use “randomize groups” to mean one of two things: shuffle a list, or divide its entries into separate teams. Shuffling changes the order but does not itself create teams. For group allocation, the useful approach is to shuffle the entries first and then distribute them across the requested number of groups.
That distinction matters because putting an independent random group number beside every person is not guaranteed to produce equal-sized teams. If each person is assigned a number from 1 to 4 independently, one group might get more people than another, even though each assignment is random. Shuffling first and dealing out group labels in turn avoids that size imbalance while keeping membership randomized.
This method is appropriate for informal activities, classroom groups, workshop tables, and similar tasks where each person should have an equal chance of landing in any group. It does not account for special requirements such as balancing experience, separating particular people, or assigning participants with specific skills. Those constraints need an additional step.
Use RAND and Sort to Shuffle the List
Suppose names are in A2:A21, with a header in A1. Use column B as a temporary random key. In B2, enter =RAND(), then fill the formula down through B21. Each row now has a random decimal value between 0 and 1. The values are keys for sorting, not group numbers.
- Keep each name and its random key on the same row. If you have other information to retain, such as an ID or department, include those columns in the sort selection too.
- Select the full data range, including its headers—for example, A1:B21—and choose Data > Sort.
- In the Sort dialog, select the random-key column as the column to sort by and choose Smallest to Largest. Confirm that the data has headers if the range includes them.
- Check that names and their associated data moved together. The list is now in a randomized order.
Sorting only column B would separate the random keys from their names and spoil the result. When Excel asks whether to expand the selection, choose to expand it so complete rows move together. If the data is an Excel table, sort the table by the random-key column so its rows remain intact.
Assign Group Labels and Keep Sizes Balanced
After shuffling, label the rows in order. For a simple setup, put the number of groups in E1. With names still in A2:A21 after the sort and the group count in E1, enter this formula in C2:
="Group " & (MOD(ROWS($A$2:A2)-1,$E$1)+1)
Fill C2 down alongside all the names. The first row is Group 1, the next is Group 2, and the labels repeat until every person has a group. The formula uses the number of data rows counted from the first name and MOD to cycle through the group numbers. If the group count is four, the labels repeat as Group 1, Group 2, Group 3, Group 4, then start again.
For 20 names in four groups, this gives five people in each group. For 22 names in four groups, it gives six each in Groups 1 and 2 and five each in Groups 3 and 4. In general, the group sizes differ by no more than one. The groups with an extra person are determined by the order in which the labels begin, so if that detail matters, you can change the starting group before filling the formula.
Once assigned, you can filter column C to view a particular group, or sort by the group label to gather each team together. Sorting by group changes the display order, not the membership. Keep a copy of the randomized assignment if you need to refer back to the original shuffled sequence.
Use a Dynamic-Array Formula in Microsoft 365
In Microsoft 365 for Windows, you can produce a shuffled list without a helper column. With names in A2:A21, enter this in an empty cell:
=SORTBY(A2:A21,RANDARRAY(ROWS(A2:A21)))
The formula spills a randomized ordering into the cells below it. SORTBY orders the names using the random values generated by RANDARRAY, and ROWS counts how many random values to create. Leave enough blank cells below the formula for the results to spill; an occupied cell in the spill area causes a #SPILL! error.
If the shuffled list begins in D2 and E1 contains the number of groups, this formula in E2 creates the matching group labels as a spilled range:
="Group " & (MOD(SEQUENCE(ROWS(D2#))-1,$E$1)+1)
D2# refers to the full spilled list starting at D2, while SEQUENCE generates one position for each name. This compact option uses dynamic-array functions available in Microsoft 365 and Excel 2021 or later. The classic RAND-and-sort process works in older desktop versions too. In Excel for the web, these formulas are available when supported by the workbook’s Excel version; the ribbon layout may differ slightly from the Windows desktop app.
Freeze the Assignment So It Does Not Change
RAND is volatile: Excel can generate new random values when the workbook recalculates. That means a later calculation or edit can change the sort keys, and a fresh sort can produce a different order. Dynamic-array results that depend on RANDARRAY can also recalculate. If you need stable assignments, treat randomization as a one-time step rather than leaving live random formulas in the final record.
After deciding that the groups are final, copy the completed names and group labels, then use Home > Paste > Values to replace formulas with their displayed results. You can also keep the original randomized version on a separate worksheet before freezing it. Save the workbook after checking that the pasted values show the intended membership.
In the helper-column method, you can paste the random keys as values before sorting if you want the sort order to remain reproducible while you work. If the random keys are left as formulas, a recalculation may change them. In the dynamic-array method, copy the spilled results and paste values into another area before doing further work that could recalculate the formula.
Make the Randomization Fair for Your Situation
A random shuffle gives each row an equal opportunity to appear in any position, assuming the random keys are generated without a meaningful pattern. Distributing positions round-robin then gives the groups balanced sizes. It does not guarantee that every group has a balanced mix of characteristics. For instance, a random allocation could put several experienced participants in one team or place people from the same department together.
If you have relevant attributes, decide which kind of fairness you need before assigning groups. You might stratify the list by experience level, then randomize within each level and deal participants among the teams. Alternatively, make separate lists for categories you want represented and assign those lists using a controlled process. These approaches add rules to the allocation, so the result is not the same as an unrestricted random draw; record the method if the assignment needs to be explained later.
Also decide how to handle absent participants, late additions, and odd numbers. Adding one person after groups have been made can create an uneven team unless you deliberately place that person in a smaller group. If the allocation has consequences beyond a casual activity, document the input list, group count, method, and final values so that someone can review what happened.
Common Problems and Misconceptions
- Groups are not equal: Assigning each row a separate random integer with RANDBETWEEN can create uneven counts. Shuffle once, then assign labels in rotation when group size is the priority.
- Names no longer match details: Sort the entire row or table, not just the random-key column. Include all columns that belong to each participant.
- The assignment changes unexpectedly: RAND and RANDARRAY may recalculate. Paste the final results as values to preserve them.
- There is a #SPILL! error: In a dynamic-array setup, clear any content or merged cells blocking the formula’s output area.
- Some names appear more than once: Check the source list for duplicate entries. Randomization does not remove duplicates or identify people who share a name; a unique ID can help distinguish them.
A random result can look uneven by chance, even when the process is working correctly. Balanced team sizes and balanced team composition are separate goals. The round-robin labeling method guarantees the first, but not the second.
Which Method Should You Choose?
For a one-time allocation, the helper-column method is usually easiest to inspect: each name has a visible random key, the sort can be reviewed, and the resulting groups can be pasted as values. It also works in older Excel versions. The dynamic-array method is concise and updates automatically, which is useful when you want a live shuffled view, but its output changes when recalculated and requires a version that supports the functions.
Choose based on the result you need:
- One stable set of groups: Use RAND, sort all rows, assign labels, and paste the final assignments as values.
- A refreshed random order: Use SORTBY with RANDARRAY, understanding that recalculation can reshuffle the names.
- Equal group sizes: Use sequential, repeating group labels after shuffling.
- Controlled membership: Add a separate, explicit balancing process for skills, availability, or other requirements.
Frequently Asked Questions
Can Excel randomly assign each person to a group?
Yes. A random sort followed by repeating group labels assigns everyone to a group and keeps group sizes as even as possible. Independent random group numbers can also be generated, but their group counts may be uneven.
How do I make random groups of exactly the same size?
The number of participants must divide evenly by the number of groups. Shuffle the list, then distribute labels in sequence. If it does not divide evenly, the most balanced result has group sizes that differ by one.
Why did my Excel groups change?
If the assignment still depends on RAND or RANDARRAY, recalculation can produce new random values. Copy the final names and labels and paste them as values to lock the assignments.
Can I randomize groups without changing the original list?
Yes. Copy the names and any needed details to a separate worksheet or empty range, then randomize the copy. Check that the copied rows include all information you need to keep with each person.
Does Excel have a button that randomly makes teams?
Excel has sorting and random-number functions, but the standard workflow is to combine them: create random keys, sort the records, and label the shuffled rows. That also lets you choose the number and size of groups.
Related
- Open CSV in Excel Without Losing Leading Zeros
- Office Contact List Template Excel: Build a Useful Directory
- Excel Number Format: How It Works and When to Use It
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Create a Local Distribution List in Outlook for EmailTo create a local distribution list in classic Outlook for Microsoft 365 on Windows, open People, select Home…
- Excel Scatter Plot Bubble Size: Set It Up and Read ItIn Excel, a standard XY scatter plot uses markers of a fixed size; to make marker size represent…
- Excel Import Multiple CSV Files: A Practical GuideTo import multiple CSV files into Excel for Microsoft 365 on Windows, put the files in one folder,…
- Excel Print Area Shortcut: Set, Add, and Clear ItIn Excel for Microsoft 365 on Windows, select the cells you want to print, then press Alt, P,…
- Excel Shortcut to Copy the Cell Above Into a CellIn Excel for Microsoft 365 on Windows, select the cell below the one you want to copy, then…
- Google Sheets Shortcut Keys: A Practical Guide for WorkGoogle Sheets shortcut keys help you edit cells, move around a spreadsheet, format data, and work faster in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.