Quickly generate random groups for list of data in Excel
In daily work or while preparing study or training sessions, you might find yourself needing to split a list of data—like names, student IDs, or project tasks—into groups at random. For example, as shown in screenshot1, data items are randomly assigned to groups, or as demonstrated in screenshot2, names are split among random teams for an activity or group task. Efficiently completing this task ensures fairness and saves significant time compared to manual assignment. Fortunately, Excel provides several reliable methods, including formulas, VBA automation, and Pivot Table analysis, to help handle random group assignment quickly and flexibly.
| Screenshot1 | Screenshot2 |
 |  |
Randomly assign data to groups
Generate random groups in a specified data size
Automate random group assignment with VBA (advanced options)
When you need to randomly allocate a list of data to a specified number of groups, and it is acceptable that each group's size may differ, you can quickly achieve this through Excel’s CHOOSE and RANDBETWEEN functions. Typical scenarios include organizing participants for games or forming ad hoc teams for meetings.
Start by selecting a blank cell next to your list (for example, if your list is in column A, select cell B2). Then input the following formula:
=CHOOSE(RANDBETWEEN(1,3),"Group A","Group B","Group C ")
In this formula:
- RANDBETWEEN(1,3) means random numbers 1 to 3 are generated, representing 3 groups.
- Group A, Group B, and Group C are the group names that will appear next to your data.
Then, drag the fill handle down to apply this formula to other rows and randomly assign all entries to groups.
After this step, each data entry is randomly assigned to a group. Please note that results may change whenever the worksheet recalculates (such as after editing a cell or reopening the file), as RANDBETWEEN is a volatile function.
Practical tip: If you don’t want these assignments to change, copy the formula results and use Paste Special > Values to fix the assignments.
Applicable scenarios: This method provides flexibility and is ideal for quick, general grouping needs. However, the sizes of each group may vary, which may not suit situations where strict even distribution is required.
Caution: Be careful if you have a large dataset or need precise group sizes—consider other methods for more control.
Unlock Excel Magic with Kutools AI
- Smart Execution: Perform cell operations, analyze data, and create charts—all driven by simple commands.
- Custom Formulas: Generate tailored formulas to streamline your workflows.
- VBA Coding: Write and implement VBA code effortlessly.
- Formula Interpretation: Understand complex formulas with ease.
- Text Translation: Break language barriers within your spreadsheets.
To distribute data into randomly assigned groups with a fixed number of entries in each group (for example, every group contains 4 members), Excel’s ROUNDUP and RANK combination offers an effective solution. This is especially helpful for classroom assignments, balanced team formation, or any scenario where uniform group sizes are required.
To perform this method:
1. First, add a helper column next to your data (e.g., column E) and enter in cell E2:
=RAND()
Drag this formula down to fill all required cells in your list. The RAND function assigns a random value to each entry.
2. In the next column (e.g., F2), input the following formula:
=ROUNDUP(RANK(E2,$E$2:$E$13)/4,0)
Here:
- E2:E13: Change this range to match your data. It should cover all rows containing the =RAND() function.
- 4: The size of each group. Change this number if you need different group sizes.
Drag the fill handle down to apply this formula to all rows. Your data will now be sorted into groups of approximately4 entries each.
Additional guidance:
- If the total number of items isn’t divisible by the group size, the last group may have fewer entries.
- Use Paste Special > Values after randomization to lock groups and prevent change on recalculation.
- Refreshing RAND() re-randomizes the groups—useful if an initial assignment needs adjustment.
Limitations: The method does not let you specify group names directly (it generates group numbers), and for large datasets, the formula may slow down recalculations.
For users who have more advanced requirements—such as making sure no member exists in more than one group, precisely balancing group sizes, or reassigning based on specific constraints—VBA can provide greater flexibility. This approach is helpful for large lists or when performing repeated assignments. Before proceeding, remember to save your file, as VBA code will change your data directly in most cases.
1. Click Developer Tools > Visual Basic to open the VBA editor, then click Insert > Module. Copy and paste the following code into the new module:
Sub AssignRandomGroups()
Dim GroupCount As Integer
Dim GroupSize As Integer
Dim rng As Range, cell As Range
Dim i As Long, j As Long, idx As Long
Dim arr() As Variant, groupArr() As Variant, grpNum As Integer
Dim ws As Worksheet
Dim totalRows As Integer, remaining As Integer
On Error Resume Next
Set ws = Application.ActiveSheet
Set rng = Application.InputBox("Select the range of data to group", "KutoolsforExcel", Type:=8)
GroupCount = Application.InputBox("Enter the number of groups:", "KutoolsforExcel", 3, Type:=1)
If rng Is Nothing Or GroupCount <= 0 Then Exit Sub
arr = rng.Value
totalRows = UBound(arr, 1)
GroupSize = Int(totalRows / GroupCount)
remaining = totalRows - GroupSize * GroupCount
ReDim groupArr(1 To totalRows)
Dim used() As Boolean
ReDim used(1 To totalRows)
Randomize
For i = 1 To totalRows
Do
idx = Int(Rnd() * totalRows) + 1
Loop While used(idx)
used(idx) = True
groupArr(i) = idx
Next i
For i = 1 To totalRows
grpNum = Int((i - 1) / GroupSize) + 1
If grpNum > GroupCount Then grpNum = GroupCount
rng.Cells(groupArr(i), 1).Offset(0, 1).Value = "Group " & grpNum
Next i
MsgBox "Groups assigned randomly and as evenly as possible.", vbInformation
End Sub2. Next, click the 
button to run the code. You will be prompted to select the range of your data (such as a column with names or IDs) and then input the number of groups you wish to generate. The VBA will then assign each data row to one group, placing the group number in the column immediately to the right.
- Prevents duplicate assignments: Each member is only placed in one group.
- Balanced groups: Allocates as evenly as possible. If the data cannot be split exactly evenly, the last group(s) may have one more or fewer member.
- Customization: You may adjust the VBA code to change output text, start column, or add additional assignment rules.
Cautions: VBA changes your worksheet data directly—be sure to save a backup before running. If you encounter errors (such as selecting more columns than expected), double-check your selection and group count inputs.
Troubleshooting: If the developer tab isn't visible, enable it via File > Options > Customize Ribbon > Developer. On error, verify your macro settings are enabled (File > Options > Trust Center).
Use case advantages: VBA is suitable for advanced users needing strict control, large data sets, or repeated task automation. It is less ideal for quick, basic tasks as it requires enabling macros and writing/modifying code.
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in