How to Split Data into Equal Groups in Excel (3 Methods)

We can split data in different ways, i.e., how many groups you want or how many cells each group needs to contain. To show all the methods, we will use the following dataset.

Method 1 – Combine INDEX, COLUMNS, ROWS, and IF Functions to Split Data into Equal Groups

We will split the given data into 3 equal groups.

Steps:

• We have added the No. of Groups cell in the dataset and put 3 in the corresponding cell.

• Go to Cell D7.
• Put the following formula in that cell:

• Press Enter and drag the Fill Handle icon to the right to AutoFill the other two cells.

• Drag the Fill Handle icon downwards to complete the splitting.

The data is split into multiple columns in different groups.

Read More: How to Split Data from One Cell into Multiple Rows in Excel

Method 2 – Split a Long List into Multiple Equal Groups with Excel VBA

We have a long list containing 12 cells. We want to split them into 3 groups with 4 cells each.

Steps:

• Go to the Sheet Name section at the bottom border of each sheet and right-click on it.
• Choose the View Code option from the list.

• The VBA application window will appear. Choose the Module option from the Insert tab.

• A VBA command module window appears.

• Copy and paste the following VBA code into the command module.

Sub split_data_into_equal_groups()
    Dim input_1 As Range
    Dim output_1 As Range
    Dim text_1 As String
    Dim out_array_1 As Variant
    Dim n, m As Long
    On Error Resume Next
    text_1 = ActiveWindow.RangeSelection.Address
Sel:
    Set input_1 = Nothing
    Set input_1 = Application.InputBox("Select input range:", "Range", text_1, , , , , 8)
    If input_1 Is Nothing Then Exit Sub
    If input_1.Areas.Count > 1 Then
        MsgBox "Multiple selections are not supported, select again", vbInformation, "Range"
        GoTo Sel
    End If
    If input_1.Columns.Count > 1 Then
        MsgBox "Multiple selections are not supported, select again", vbInformation, "Range"
        GoTo Sel
    End If
    Set output_1 = Application.InputBox("Select a cell to view the output:", "Start Range", , , , , , 8)
    If output_1 Is Nothing Then Exit Sub
    n = Application.InputBox("Number of cells per column:", "Cell Number", , , , , , 1)
    If n < 1 Then
        MsgBox "incorrect enter", vbInformation, "Cell Number"
        Exit Sub
    End If
    ReDim out_array_1(1 To n, 1 To Int(input_1.Rows.Count / n) + 1)
    For m = 0 To input_1.Rows.Count - 1
      out_array_1(1 + (m Mod n), 1 + Int(m / n)) = input_1.Cells(m + 1)
    Next
    output_1.Range("A1").Resize(n, UBound(out_array_1, 2)) = out_array_1
End Sub

• Press F5 to run the code. A dialog box will appear to choose the range.
• We chose Range B5:B16 as the input.
• Press OK.

• The next dialog appears. This box requires a cell reference to start the output data. We selected Cell D5.
• Press OK.

• A third dialog box requests the number of items per group. Put 4 for the sample.

• Press OK.

• Our data is split into 3 groups equally.

Method 3 – Use a Custom VBA Function to Split Data into Even Groups

Steps:

• Press Alt+F11 to enter the VBA command module.
• Put the following VBA code on the command module and save the code.

Function GroupValues(range_1 As Range)
Dim output As Variant
n = Application.Caller.Columns.Count
m = Application.Caller.Rows.Count
ReDim output(1 To m, 1 To n)
k = 1
For row_1 = 1 To m
    For column_1 = 1 To n
        If range_1.Cells(k) <> "" Then
            output(row_1, column_1) = range_1.Cells(k)
        Else
            output(row_1, column_1) = ""
        End If
        k = k + 1
    Next column_1
Next row_1
GroupValues = output
End Function

• Go to the dataset. Choose the range you want to turn into groups, such as the range D5:F8 from the example.
• Select the first cell of the grouping table (here that’s D5).
• Go to the formula bar and insert the following formula.

• This is an array formula, so press Ctrl + Shift + Enter to apply it.

Read More: Excel Macro to Split Data into Multiple Files

How to Split Data and Tag It with Group Numbers

Method 1 – Split Data Using the Combination of MAX, ROUNDUP, and PERCENTRANK Functions

We will consider the dataset below, where we need to split the integers into buckets.

Steps:

• Go to Cell C5.
• Insert the following formula.

• Press the Enter key.

The cells are tagged with group numbers instead of splitting them.

Method 2 – Combining ROUNDUP, ROW, and COUNT Functions to Split Data

Steps:

• Go to Cell C5 and put the following formula.

• Press the Enter key to execute the formula.

• The function split the numbers into two groups based on order of appearance in row rather than value.

Download the Practice Workbook

Related Articles

• Excel Split Data into Columns by Comma
• How to Split Comma Separated Values into Rows or Columns in Excel
• Split Data into Multiple Worksheets in Excel