How to split a long list into equal groups in Excel?
When working with large datasets in Excel, you may encounter situations where you need to split a long list of items into several equal groups. For example, you might need to distribute survey responses, create balanced assignments, or organize teams for a project. Manually dividing such lists can be time-consuming and prone to errors, especially for extensive data. Efficiently splitting lists into equal groups helps streamline workflow, enhances data organization, and reduces the likelihood of mistakes.
Excel provides several practical methods to achieve this—ranging from VBA automation, handy add-ins like Kutools for Excel, to formula-based techniques. Each approach has its unique advantages and is suitable for different skill levels and scenarios.
Split a long list into multiple equal groups with VBA code
Split a long list into multiple equal groups with Kutools for Excel
Split a long list into multiple equal groups with Excel Formula
Split a long list into multiple equal groups with VBA code
Aside from the tedious approach of copying and pasting data into separate groups one by one, VBA can help automate this task quickly and accurately. Below is a step-by-step guide to splitting your list into equal groups using VBA:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications editor window.
2. Click Insert > Module, and then paste the following VBA code into the newly created Module window.
VBA code: Split a long list into multiple equal groups
Sub SplitIntoCellsPerColumn()
'updateby Extendoffice
Dim xRg As Range
Dim xOutRg As Range
Dim xCell As Range
Dim xTxt As String
Dim xOutArr As Variant
Dim I As Long, K As Long
On Error Resume Next
xTxt = ActiveWindow.RangeSelection.Address
Sel:
Set xRg = Nothing
Set xRg = Application.InputBox("please select data range:", "Kutools for Excel", xTxt, , , , , 8)
If xRg Is Nothing Then Exit Sub
If xRg.Areas.Count > 1 Then
MsgBox "does not support multiple selections, please select again", vbInformation, "Kutools for Excel"
GoTo Sel
End If
If xRg.Columns.Count > 1 Then
MsgBox "does not support multiple columns,please select again", vbInformation, "Kutools for Excel"
GoTo Sel
End If
Set xOutRg = Application.InputBox("please select a cell to put the result:", "Kutools for Excel", , , , , , 8)
If xOutRg Is Nothing Then Exit Sub
I = Application.InputBox("the number of cell per column:", "Kutools for Excel", , , , , , 1)
If I < 1 Then
MsgBox "incorrect enter", vbInformation, "Kutools for Excel"
Exit Sub
End If
ReDim xOutArr(1 To I, 1 To Int(xRg.Rows.Count / I) + 1)
For K = 0 To xRg.Rows.Count - 1
xOutArr(1 + (K Mod I), 1 + Int(K / I)) = xRg.Cells(K + 1)
Next
xOutRg.Range("A1").Resize(I, UBound(xOutArr, 2)) = xOutArr
End Sub3. Press F5 or click the Run button to execute the code. In the input box that appears, select the column of data you wish to split into groups.
4. Click OK, then select the starting cell where you want the grouped results to be placed in the next prompt.
5. Click OK, and enter the number of items you want in each group (each column) in the prompt dialog.
6. Finally, click OK to complete the process. The code will automatically split your selected list into multiple columns, each containing the specified number of items. Note: If the list cannot be divided exactly into equal groups, the last group may contain fewer items.
The VBA solution is suitable for users comfortable with macros and automating tasks for repeated use. One advantage is flexibility; you can run the script with different group sizes without additional configuration. However, since it involves code, it may not be allowed in all professional environments, and users unfamiliar with VBA should save their work before running macros.
If the macro does not run as expected, check if macros are enabled in Excel. Also, ensure that you select a single, continuous column; otherwise, the code will prompt you to re-select the data range. If your list length isn't perfectly divisible by group size, the last group will have fewer items, so consider this in your distribution planning.
Split a long list into multiple equal groups with Kutools for Excel
If you have Kutools for Excel installed, its Transform Range feature allows you to quickly rearrange a long list into multiple groups across columns and rows with just a few clicks. This method reduces manual handling errors and makes data organization much more efficient. Kutools adds a professional layer of usability for less technical users, with intuitive dialog boxes and reliable outcomes.
After installing Kutools for Excel, please do the following:
1. Select the long list that you want to split. Then go to Kutools > Range > Transform Range.
2. In the Transform Range dialog box, choose Single column to range under Transform type, check Fixed value, and enter the desired number of items per row. (For example, if you want four groups, specify the group size accordingly.) This determines how your original list will be split.
3. Click OK, then select a cell in your worksheet where you want the grouped result to start.
4. Click OK again, and Kutools will instantly split your long list into equal-sized groups according to your settings.
Using Kutools for Excel is straightforward and minimizes manual mistakes. The method is ideal for users who prefer graphical interfaces and frequent data transformations.
Download and free trial Kutools for Excel Now!
Split a long list into multiple equal groups with Excel Formula
If you prefer not to use VBA or add-ins, Excel’s built-in formulas can also help you divide your list into equal groups efficiently. This approach is especially helpful for those who want a solution that is portable, compatible with all versions of Excel, and safe for shared workbooks or environments where macros and third-party add-ins are restricted. The method works best when your groups are meant to be arranged next to each other in columns.
Here’s how to assign group numbers to each entry, so that you can easily filter or reorganize your list by groups without any coding:
1. Suppose your long list is in column A, starting from cell A2 downward. In cell B2 (next to your first list item), enter the following formula to assign group numbers:
=MOD(ROW(A2)-ROW($A$2),4) +1In this example, "4" represents the number of groups you want. Adjust this value if you wish to split into a different number of groups. This formula cycles through group numbers 1 to 4.
2. Drag the formula down alongside your entire list to assign group numbers to all rows. The result is a helper column that labels each row according to its group.
3. To extract or display the groups:
- You can use filters: Apply an AutoFilter on your list and filter by the group number to quickly separate records.
- You can copy and paste each group to separate locations, or use advanced formulas or PivotTables to rearrange items as needed.
If you're using Excel with dynamic array support (Microsoft 365 and Excel 2021+), you can divide a list into equal-sized columns automatically using the WRAPROWS function. Assume your list is in A2:A17, and you want to split the list into4 columns (groups):
=WRAPROWS(SORTBY(A2:A13, RANDARRAY(ROWS(A2:A13))), 4)Enter this formula in a cell where you want your new grouped arrangement to begin and press Enter. It then works by randomly filling columns with equal portions of your list.
- If your list cannot be split perfectly evenly, the column(s) may have #N/A error results. Adjust the group count (here "4") as necessary for your exact scenario.
- If there are blank cells in the range, they will be treated as zero in the grouped results.
Advantages of the formula method include full compatibility with shared workbooks and the ability to quickly recalculate group numbers if data changes. However, typing or adjusting formulas requires attention to detail—mismatched ranges or incorrect group counts may lead to missing or duplicated entries. If you find errors, verify that your list range is correct and that you have dragged the formulas down for the complete list.
As a tip, always make a backup before applying formulas on original data, and use Paste Special > Values after arranging groups if you plan to delete helper columns.
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