To sort a list with a formula in Microsoft Excel for Microsoft 365, use SORT: with values in A2:A6, enter =SORT(A2:A6) in an empty cell to return the list in ascending order. Use SORTBY when you want to sort rows according to another column, or use Data > Sort to reorder the original cells instead of creating a sorted copy.
What the SORT formula does
SORT returns a sorted version of a range or array. It does not rearrange the source cells: the sorted result appears separately, and changes to the source range can update that result automatically. This makes it useful for reports, dashboards, and views where the original order should remain intact.
For example, imagine A2:A6 contains Maya, Luis, Ana, Zoe, and Omar. With those values in A2:A6, enter =SORT(A2:A6) in an empty cell; the result is Ana, Luis, Maya, Omar, Zoe. Excel sorts text alphabetically in ascending order by default. For numbers, the default is smallest to largest; for dates, earliest to latest.
The formula returns a dynamic array. Excel places the first result in the formula cell and spills the remaining results into the cells below or beside it. You enter the formula once, rather than copying it down. The spill range updates when the underlying data changes, subject to the range included in the formula.
Choose ascending or descending order
The full syntax is SORT(array,[sort_index],[sort_order],[by_col]). The array is the range to sort. The optional sort index selects a column or row within that array, and sort order controls direction: 1 means ascending and -1 means descending. The final argument determines whether Excel sorts by rows or by columns; in ordinary lists, leave it out or use FALSE.
With names in A2:A6, enter =SORT(A2:A6,1,-1) in an empty cell to return them in reverse alphabetical order: Zoe, Omar, Maya, Luis, Ana. The 1 identifies the first column in the array, and -1 requests descending order. For a single-column range, the sort index is usually unnecessary, so =SORT(A2:A6,, -1) is also valid, but the explicit 1 is easier to read and avoids relying on an omitted argument.
For numbers, the same setting reverses the usual order. With scores in B2:B6, enter =SORT(B2:B6,1,-1) in an empty cell to display the scores from largest to smallest. If you sort dates, descending order puts the newest date first. The displayed appearance of a date depends on cell formatting; the underlying values are still sorted as dates.
Sort complete rows with SORT or SORTBY
If a list has multiple columns, sorting only one column can separate values from the records they belong to. Include the entire table body in the array so Excel moves each row together. Do not include the header row in a formula unless you deliberately want it included among the data.
With names in A2:A6 and sales in B2:B6, where the rows are Luis 420, Maya 315, Ana 510, Zoe 275, and Omar 290, enter =SORT(A2:B6,2,-1) in an empty cell. The result is Ana 510, Luis 420, Maya 315, Omar 290, Zoe 275. The second argument tells Excel to sort by the second column of the selected array, and -1 places the largest sales figure first.
SORTBY is often clearer when the sort key is in another range or when you need more than one sorting rule. With names in A2:A6 and sales in B2:B6, enter =SORTBY(A2:B6,B2:B6,-1) in an empty cell to return the complete rows ordered by sales from highest to lowest. The first argument is the data to return; the next range is the key to sort by.
You can add secondary sort keys. With names in A2:A6, departments in B2:B6, and scores in C2:C6, enter =SORTBY(A2:C6,B2:B6,1,C2:C6,-1) in an empty cell to sort departments alphabetically, then put the highest score first within each department. The data range and each sort-key range must have matching dimensions.
When to use SORTBY instead
Use SORT when the sort key is a column or row inside the array you are returning. Its sort index is a position within that array, not a worksheet column letter. For instance, if the selected array begins in D2, sort index 2 refers to the second column of that selected range, not column B or worksheet column 2.
Choose SORTBY when the key is outside the returned range, when you need multiple sort keys, or when the formula reads more naturally by naming each key range. A common example is returning product names and prices while sorting by a separate category or ranking column. Both functions create a sorted result without changing the source data.
Neither function provides a built-in way to specify a custom sequence such as High, Medium, Low. For a custom order, add a helper column containing numeric ranks—for example, 1 for High, 2 for Medium, and 3 for Low—and sort by that column with SORTBY. This approach makes the intended order visible and easier to maintain.
Sort a filtered list
You can combine sorting with filtering to show only records that meet a condition and then order those records. This is useful when you want a changing report rather than a one-time sort. In Excel 2021 or Microsoft 365, FILTER and SORT support this type of dynamic result.
With regions in A2:A20 and sales in B2:B20, enter =SORT(FILTER(A2:B20,A2:A20="North"),2,-1) in an empty cell to return North-region rows ordered by sales, highest first. The result includes both columns and excludes rows whose region is not North. If nothing meets the condition, FILTER can return an error unless you supply its optional third argument for an alternate result; for instance, an explanatory message.
With regions in A2:A20 and sales in B2:B20, use =SORT(FILTER(A2:B20,A2:A20="North","No matching rows"),2,-1) to display that message when there are no matches. Because the fallback text is a single value while matching results have two columns, the result shape can differ depending on whether a match exists. If another formula depends on a consistent output shape, consider designing the fallback and downstream calculations accordingly.
Formula sort or Data > Sort?
A formula is best when the sorted list should remain a separate, automatically refreshed view. It preserves the original order and is convenient for a summary sheet or a live output area. It also requires enough empty cells for the results to spill and may not be suitable when you need to edit the sorted output as the source data.
Use the built-in sort command when you want to reorder the source records themselves. Select a cell in the data, then choose Data > Sort. In the Sort dialog, select the column and order; use Add Level to define additional sort keys. If Excel asks whether to expand the selection, choose Expand the selection so related columns stay with their rows. Sorting a single column alone can mismatch names, dates, and other record details.
For a range with headers, confirm that the dialog recognizes the header row so the column titles are not sorted into the records. In a formatted Excel table, sorting from a column’s filter arrow sorts the table rows together. These commands work differently from SORT: they change the order of the source data rather than returning a second array.
Dynamic arrays, tables, and common errors
Place a dynamic-array formula outside an Excel table. A formula that needs to spill cannot spill within the table’s structured rows, so use a regular worksheet area for the result. The source itself can be a table, and structured references can make formulas easier to read; for example, a table named Sales with a column named Amount can be referenced as Sales[Amount]. Keep the result area clear of other values.
If Excel shows #SPILL!, one or more cells needed for the output are not available, or another condition is preventing the array from spilling. Clear obstructing cells, check for merged cells in the output area, and make sure the formula is not inside a table. Excel indicates the blocked spill range when you select the error cell. A formula can also return a result that is larger than expected if its source range includes extra rows or columns.
A frequent mistake is including a heading in the array, which can place the heading in the alphabetical or numerical sort. Select only the data rows, or handle the heading separately. Another is using a sort index that points to the wrong field: it counts columns within the array, not worksheet column numbers. For SORTBY, mismatched key and data-range sizes can cause an error because each key value must correspond to a row or column in the data.
Blank cells and mixed data types can also make results confusing. A column containing numbers stored as text may not sort as readers expect, and dates stored as text may behave like text rather than chronological dates. Convert inconsistent entries to a consistent data type before sorting. If equal values appear, avoid assuming a particular tie order unless you add a second sort key that makes the order explicit.
Excel version availability
SORT and SORTBY are available in Microsoft 365 and Excel 2021 or later. Dynamic-array behavior is important: in older Excel releases, these functions may not be available, and formulas that return multiple values do not behave the same way. If a workbook must work with older versions, use Data > Sort for a manual sort, or consider a PivotTable or a helper-column approach that fits the recipients’ Excel version.
Excel for the web also supports dynamic-array formulas, though some workbook features and editing details can differ from the Windows desktop app. Excel for Mac has the same core SORT and SORTBY functions in supported versions; the Data > Sort command is available there as well, although the dialog layout can vary. If a formula returns #NAME?, check the Excel version and verify the function spelling before changing the formula.
Frequently Asked Questions
How do I sort alphabetically in Excel with a formula?
With text in A2:A20, enter =SORT(A2:A20) in an empty cell to return an ascending alphabetical list. To reverse the order, use =SORT(A2:A20,1,-1). The source cells remain in their original order.
Can Excel sort automatically when I add a new item?
A formula recalculates when its referenced data changes, but it can only sort the range it refers to. For a fixed range such as A2:A20, entries beyond row 20 are not included. Use an Excel table or a suitably sized source range when you expect the list to grow, and leave room for the formula’s spilled results.
How do I sort by one column but keep the other columns together?
Include the full data body in SORT and set the sort index to the key column’s position within that range, or use SORTBY with the complete data range and the key range. Do not sort just one column of a multi-column record unless separating the values is intentional.
Can I sort a list without changing the original data?
Yes. SORT and SORTBY return a separate sorted array, leaving the source order intact. The Data > Sort command instead reorders the selected source records, so use that when changing the original arrangement is the goal.
Why does my SORT formula show #SPILL!?
Excel cannot place all the formula’s results in the required cells. Clear the cells in the spill area, check for merged cells, and move the formula outside an Excel table. Selecting the error cell usually helps identify the blocked range.
Related
- Subscript in Excel: Shortcut and Formatting Steps
- Quick Forecast in Excel: Steps, Limits, and Practical Tips
- Excel Redo Shortcut: The Quick Key and When It Works
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Nested Formula Examples and How They WorkExcel nested formulas place one function inside another, so a calculation or test can supply the input for…
- Excel Extend Number Sequence: Fill Series and FormulasTo extend a number sequence in Excel, enter its first two values, select both cells, then drag the…
- Excel Formula to Find All Tab Names in Your WorkbookExcel has no built-in worksheet formula that directly returns every tab name. In Microsoft 365 for Windows, you…
- Excel Insert Row Automatically Add Formula?Yes—Excel can automatically add a formula when you insert a row, most reliably when the data is an…
- How to Sort in Excel by Number Order Step by Step GuideTo sort numbers in Excel, select a cell in the number column, then choose Data > Sort Smallest…
- Excel Sum Multiple Cells: Formulas, Ranges, and TipsTo sum multiple cells in Excel, use the SUM function: enter =SUM(A1:A5) to add the values in cells…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.