SORT

Array Functions

(4.9/5)

The SORT function sorts the contents of a range or array dynamically. Perfect for organizing data automatically, creating sorted reports, and maintaining clean data presentation without manual intervention.

Interactive Formula Tester

Test Input:

Formula:

=SORT("")

Complete Theory & Understanding

Master the fundamentals of Excel SORT function

Core Concept

SORT is a dynamic array function that organizes data automatically based on specified criteria. It provides formula-based sorting that updates automatically when source data changes, eliminating the need for manual sorting operations.

Why Use SORT?

Automatically organize large datasets
Create self-sorting reports
Generate sorted reports automatically
Present data in organized format

Key Characteristics

Dynamic Sorting

Automatically updates when source data changes

=SORT(A1:A10) updates with data

Full Row Sort

Maintains row integrity when sorting multi-column arrays

=SORT(A1:C10, 2) keeps rows together

Flexible Order

Ascending or descending with simple parameter

1 or -1 for order

Non-Destructive

Does not modify original data

Returns sorted copy

Function Anatomy

=SORT(parameters...)

Required

Parameters:

Function-specific parameters

Returns

Return Value:

Function-specific return type

Primary Use Cases

Data Organization

Automatically organize large datasets

Dynamic Dashboards

Create self-sorting reports

Report Generation

Generate sorted reports automatically

Data Presentation

Present data in organized format

Theory Summary

Precise

Exact matching required

Position-Based

Returns numeric position

Error-Safe

Handles missing text gracefully

Syntax & Parameters

=SORT(array, sort_index, sort_order, by_col)

Required

array:

The array or range to sort (required)

Optional

sort_index:

Column index to sort by (default is 1)

Optional

sort_order:

1 for ascending (default), -1 for descending

Optional

by_col:

FALSE to sort by rows (default), TRUE to sort by columns

Returns

Return Value:

Sorted dynamic array

Description: Sorts and returns an array based on specified criteria

Interactive Examples

Basic Sort

Sort values in ascending order

Input Text:

"A1:A10 values"

Formula:

=SORT(A1:A10)

Result:

Sorted ascending

Sorts values from smallest to largest (A-Z)

VBA Implementation & Automation

Manual SORT Implementation

Sort arrays in VBA using built-in methods

' Sort range in VBA
Sub SortRangeVBA()
    Dim ws As Worksheet
    Dim sortRange As Range
    
    Set ws = ActiveSheet
    Set sortRange = ws.Range("A1:A10")
    
    ' Sort ascending
    sortRange.Sort Key1:=sortRange.Cells(1), _
                   Order1:=xlAscending, _
                   Header:=xlNo
                   
    MsgBox "Range sorted ascending"
End Sub

' Advanced sort with multiple columns
Sub SortMultipleColumns()
    Dim ws As Worksheet
    Dim dataRange As Range
    
    Set ws = ActiveSheet
    Set dataRange = ws.Range("A1:C100")
    
    ' Sort by column B, then by column C
    With ws.Sort
        .SortFields.Clear
        .SortFields.Add Key:=dataRange.Columns(2), _
                       SortOn:=xlSortOnValues, _
                       Order:=xlAscending
        .SortFields.Add Key:=dataRange.Columns(3), _
                       SortOn:=xlSortOnValues, _
                       Order:=xlDescending
        .SetRange dataRange
        .Header = xlYes
        .Apply
    End With
    
    MsgBox "Multi-column sort complete"
End Sub

Custom Sort Function

Create array sorting function in VBA

' Bubble sort implementation for small arrays
Function SortArray(inputArray As Variant, ascending As Boolean) As Variant
    Dim outputArray As Variant
    Dim i As Long, j As Long
    Dim temp As Variant
    
    outputArray = inputArray
    
    ' Bubble sort algorithm
    For i = LBound(outputArray) To UBound(outputArray) - 1
        For j = LBound(outputArray) To UBound(outputArray) - i - 1
            If ascending Then
                If outputArray(j) > outputArray(j + 1) Then
                    temp = outputArray(j)
                    outputArray(j) = outputArray(j + 1)
                    outputArray(j + 1) = temp
                End If
            Else
                If outputArray(j) < outputArray(j + 1) Then
                    temp = outputArray(j)
                    outputArray(j) = outputArray(j + 1)
                    outputArray(j + 1) = temp
                End If
            End If
        Next j
    Next i
    
    SortArray = outputArray
End Function

' Usage
Sub Example_SortArray()
    Dim myArray As Variant
    Dim sorted As Variant
    
    myArray = Array(5, 2, 8, 1, 9, 3)
    sorted = SortArray(myArray, True)
    
    MsgBox "Sorted: " & Join(sorted, ", ")
End Sub