How to divide data into equal intervals in Excel 

Splitting data into equal intervals creates standardized groups, enabling easy comparison between different segments. This approach facilitates direct comparisons by dividing the data into equal intervals. By establishing consistent boundaries, the data is organized into easily comparable segments. This standardized division enables efficient analysis and evaluation across various groups, facilitating straightforward comparisons. 

Let’s consider a dataset comprising the names of soldiers, and our objective is to evenly divide them into three groups: Alpha, Bravo, and Charlie. Using this scenario, we can explore the process of dividing data into equal intervals and learn how to achieve equitable distribution within these groups. 

Method 1 – By using multiple functions 

Understanding Functions and their syntax 

INDEX Function: 

The syntax is as follows: 

=INDEX(array, row_num, [column_num]) 

The INDEX function is used to retrieve a value or reference from a specific cell within an array or range. 

array: This is the range or array from which you want to retrieve a value. 

row_num: It specifies the row number within the array from which you want to retrieve the value. 

column_num (optional): It specifies the column number within the array. If omitted, the function returns the entire row specified by row_num. 

COLUMNS Function: 

The syntax of this function is below: 

=COLUMNS(array) 

The COLUMNS function is used to count the number of columns within a given array or range. 

array: This is the range or array for which you want to count the number of columns. 

ROWS Function: 

Syntax of ROWS Functions is as follows: 

=ROWS(array) 

The ROWS function is used to count the number of rows within a given array or range. 

array: This is the range or array for which you want to count the number of rows. 

IF Function: 

The syntax of the IF Function is as follows: 

=IF(logical_test, value_if_true, value_if_false) 

The IF function allows you to perform conditional evaluations and return different values based on the result of a logical test. 

logical_test: This is the condition or logical test that you want to evaluate. 

value_if_true: It specifies the value to be returned if the logical_test evaluates to TRUE. 

value_if_false: It specifies the value to be returned if the logical_test evaluates to FALSE. 

Step 1 – Write the number of columns 

• Select any empty cell and write the number of columns in that cell. 

• For example, we have written “3” in cell “E1”. 
• This will assist us in writing formula later in this tutorial. 

Step 2 – Select a cell 

• Select any empty cell which we will use as the first cell of the groups. 

• We will apply the formula containing multiple functions in this cell. 

Step 3 – Write the formula 

• Write the following formula in the cell. 

=IFERROR(IF($E$1>=COLUMNS($A$2:A2),INDEX($A$2:$A$13,(ROWS($A$2:A2))*$E$1-($E$1-COLUMNS($A$2:A2))),””),””) 

Where, 

E1 is the cell containing the number of columns in the cell. 

A2:A2 is the cell that contains the first person or first cell we want to divide into groups. 

A2:A13 is the range containing all cells that we wish to divide equally. 

• After writing the formula, press Enter and the result will appear in the cell. 

Step 4 – Divide the data into equal intervals 

• Now, use the fill handle to drag this data to the rows and columns. 
• In this way, we can divide the data into equal intervals. 

Method 2 – By using the VBA code 

Step 1 – Add a module 

• For adding a module, navigate to the Developer tab. 
• After that, click on the first option named Visual Basic. 
• You’ll see a new window on your screen and you can also open this window by using the shortcut key (Alt+F11) as well. 

• Then, click on the Insert tab in this window and click on the Module option. 
• Now, a new module would open. 

Step 2 – Write the code 

• Now, copy and paste the following code into the module. 

Sub Split_Equal() 
    Dim inputRange As Range 
    Dim outputRange As Range 
    Dim textSelection As String 
    Dim outputArray As Variant 
    Dim groupSize As Long 
    Dim numRows, numColumns As Long 
     
    On Error Resume Next 
    textSelection = ActiveWindow.RangeSelection.Address 
     
Sel: 
    ‘ Select the input range 
    Set inputRange = Nothing 
    Set inputRange = Application.InputBox(“Select input range:”, “Range”, textSelection, , , , , 8) 
    If inputRange Is Nothing Then Exit Sub 
     
    ‘ Check for multiple selections 
    If inputRange.Areas.Count > 1 Then 
        MsgBox “Multiple selections are not supported, select again”, vbInformation, “Range” 
        GoTo Sel 
    End If 
     
    ‘ Check for multiple columns 
    If inputRange.Columns.Count > 1 Then 
        MsgBox “Multiple selections are not supported, select again”, vbInformation, “Range” 
        GoTo Sel 
    End If 
     
    ‘ Select the output cell 
    Set outputRange = Application.InputBox(“Select a cell to view the output:”, “Start Range”, , , , , , 8) 
    If outputRange Is Nothing Then Exit Sub 
     
    ‘ Specify the group size 
    groupSize = Application.InputBox(“Number of cells per column:”, “Cell Number”, , , , , , 1) 
    If groupSize < 1 Then 
        MsgBox “Incorrect input, please enter a valid cell number”, vbInformation, “Cell Number” 
        Exit Sub 
    End If 
     
    ‘ Calculate the number of rows and columns in the output array 
    numRows = Int(inputRange.Rows.Count / groupSize) + 1 
    numColumns = groupSize 
    ReDim outputArray(1 To numColumns, 1 To numRows) 
     
    Dim rowIndex As Long 
    Dim columnIndex As Long 
     
    ‘ Split the input data into equal groups and store it in the output array 
    For rowIndex = 0 To inputRange.Rows.Count – 1 
        outputArray(1 + (rowIndex Mod numColumns), 1 + Int(rowIndex / numColumns)) = inputRange.Cells(rowIndex + 1) 
    Next 
     
    ‘ Output the data to the specified output range 
    outputRange.Range(“A1”).Resize(numColumns, numRows) = outputArray 
End Sub 

• After that, close the VBA Editor. 

Step 3 – Divide the data into equal intervals 

•  After closing the VBA Editor, press “Alt+F8” and the dialogue box would appear on your screen. 

• Then, click on the macro named “Split_Equal” and click on Run. 
• Now, input the cell range in the dialogue box that has just appeared. 
• As soon as you click on “OK” another dialogue box will open. 
• Input the cell address in the provided dialogue box. This cell will serve as the starting point for dividing the data into equal intervals. 
• After that another dialogue box would appear. 

• In this dialogue box, enter the number of cells that you want to have in each column. 
• Then, click on “OK” and your data will be equally divided into the groups.