When you sort data using Excel's ribbon button, you permanently reorder the original rows — there is no live connection, and no automatic refresh when new data arrives. The SORT function is different. It outputs a sorted copy of your data in a new location, leaves the original completely untouched, and updates automatically every time the source changes. This guide covers the full SORT syntax, introduces its companion SORTBY function, and walks through six practical examples — from simple alphabetical lists to multi-column sorts, sorting filtered results, and randomising a list.
SORT Function Syntax
| Argument | Required? | What it means |
|---|---|---|
| array | Required | The range or array to sort. Can be a single column, a multi-column table, or the result of another function like FILTER or UNIQUE. |
| sort_index | Optional | Column number to sort by. 1 = first column (default). 2 = second column, and so on. Pass an array constant like {2,1} to sort by multiple columns. |
| sort_order | Optional | 1 = ascending A-Z, smallest to largest (default). -1 = descending Z-A, largest to smallest. Pass {1,-1} for multi-column sorts with mixed directions. |
| by_col | Optional | FALSE (default) — sort rows vertically. TRUE — sort columns horizontally. Used only when your data runs across columns rather than down rows. |
Example 1: Sort a Column A to Z (Ascending)
The simplest use — sort a list alphabetically or numerically in ascending order. Enter the formula in one empty cell; the results spill down automatically to cover all rows.
=SORT(Table1[Product]). The SORT output automatically expands when new rows are added to the table — no formula editing needed.
Example 2: Sort a Multi-Column Table by One Column
When you sort a multi-column range, SORT moves entire rows together — the data stays aligned. Specify which column to sort by using the sort_index argument.
Example 3: Sort by Multiple Columns
Pass array constants for sort_index and sort_order to apply a multi-level sort. Excel applies the sort criteria left to right — first by the primary column, then by the secondary column for rows where the primary values are equal.
{} create an array constant directly in the formula. {2,3} means "sort by column 2 first, then column 3". {1,-1} means "first sort ascending, second sort descending". The two arrays must be the same length — one sort_order value per sort_index value.
Example 4: Sort Filtered Results — SORT + FILTER
SORT and FILTER work naturally together — use FILTER to narrow your dataset first, then wrap SORT around the result. This gives you a live, filtered, sorted view of your data from a single formula.
IFERROR(..., "No results") prevents the error from showing in your dashboard.
Example 5: SORTBY — Sort by a Column Outside the Output Range
SORT can only sort by columns that are included in the array argument. SORTBY removes this restriction — it sorts one range based on values in a completely separate range or column. This is useful when you want to sort visible columns by a hidden column, or sort a subset of columns by values from the full dataset.
=SORT(A2:C100, 3, -1)
Returns all 3 cols (A+B+C)
Salary is visible in output
=SORTBY(A2:B100, C2:C100, -1)
Returns only cols A+B (Name+Dept)
Sorted by col C (Salary) — not shown
Example 6: Sorted Unique List — SORT + UNIQUE
Combining SORT and UNIQUE in one formula gives you a deduplicated, alphabetically sorted list in a single step. This is perfect for dropdown sources, report headers, and category lists that need to stay current automatically.
Troubleshooting SORT Errors
#SPILL! error
There is data in one or more cells below or beside the formula cell that SORT needs for its output. Clear all cells in the spill range and re-enter the formula. Check for spaces or formulas returning empty strings — these are invisible but still block spilling.
#VALUE! error
The sort_index value is larger than the number of columns in the array. If your array is A2:C100 (3 columns), the maximum sort_index is 3. Check that your index value does not exceed the column count of the array you provided.
#REF! error after reopening the workbook
SORT (and all dynamic array functions) require both workbooks to be open when referencing another file. If the source workbook is closed, the formula returns #REF!. Open the referenced workbook and the formula will recalculate correctly.
Sort order not working as expected on multi-column sort
The array constants in sort_index and sort_order must be the same length and correspond positionally. {2,3} and {1,-1} means column 2 ascending, column 3 descending. A mismatch in lengths causes a #VALUE! error.
Frequently Asked Questions
-
What does the SORT function do in Excel?SORT outputs a sorted copy of a range or array to a new location, leaving the original data untouched. The sorted output spills automatically into as many cells as needed and updates live whenever the source data changes. Unlike the Ribbon Sort button, SORT is non-destructive and dynamic — it creates a live sorted view, not a permanent rearrangement.
-
What is the difference between SORT and SORTBY?SORT sorts a range by one or more columns within that same range. SORTBY sorts one range based on the values in a separate range — the sort column does not have to be part of the output. Use SORT for straightforward column sorts. Use SORTBY when the column you want to sort by is not included in the range you want to output, or when you want to sort by a calculated value.
-
How do I sort by multiple columns using SORT?Pass array constants for the sort_index and sort_order arguments: =SORT(A2:C100, {2,3}, {1,-1}). This sorts by column 2 ascending first, then by column 3 descending for rows where column 2 is equal. Both arrays must have the same number of elements — one entry per sort level.
-
Which Excel versions support the SORT function?SORT and SORTBY are available in Microsoft 365 (all plans), Excel 2024, and Excel 2021. They are not available in Excel 2019, 2016, or any earlier version. These functions require the dynamic array calculation engine introduced in Excel 365 and carried over into the 2021 perpetual release.
-
How do I randomise a list using SORTBY?Use =SORTBY(A2:A20, RANDARRAY(COUNTA(A2:A20))). RANDARRAY generates a column of random numbers the same length as your list. SORTBY then sorts the list in the random order produced by those numbers. The result changes every time the worksheet recalculates — press F9 to generate a new random order.
-
Can SORT sort data in place like the Ribbon Sort button?No. The SORT function always outputs results to a new location and cannot overwrite or reorder the source data. If you want to permanently reorder the original data, use Data > Sort on the Ribbon. If you want a live sorted view that updates automatically while leaving the original intact, use the SORT function in a separate area of your sheet.