Excel SORT Function Examples: Data, Names, and Dates

Excel’s SORT function creates a sorted copy of a range with a formula: for example, with names in A2:A6, enter =SORT(A2:A6) to return them in ascending order. Use SORTBY when you need to sort by another column or specify multiple sorting keys; use the ribbon’s Sort command when you want to rearrange the original data.

What the SORT function does

SORT is a dynamic array function in Microsoft 365 for Windows. It returns the values from a range in a chosen order and spills the results into neighboring cells. The source cells stay where they are: the formula produces a sorted view rather than changing the original list. SORT is also available in Excel 2021 and later; older versions may not recognize it.

The function’s syntax is =SORT(array,[sort_index],[sort_order],[by_col]). The array is the range to sort. The optional sort_index identifies which column or row within that array determines the order. Use 1 for ascending order and -1 for descending order. The optional by_col argument is FALSE by default, so Excel sorts rows; TRUE sorts columns instead. Brackets in the syntax indicate optional arguments and are not typed into a formula.

When you omit the optional arguments, Excel sorts the rows by the first column in ascending order. SORT can sort text, numbers, and dates, provided the values are stored in usable, consistent formats. It does not supply a separate header row, so select the data rows rather than including column headings in the array.

Basic examples: names and numbers

Suppose A2:A6 contains the names Maya, Luis, Aisha, Ben, and Zoe. With those names in A2:A6, enter =SORT(A2:A6). The spilled result is Aisha, Ben, Luis, Maya, Zoe, one name per row. Because the range has just one column, there is no need to specify a sort_index.

To reverse that order, with the same names in A2:A6, enter =SORT(A2:A6,1,-1). The result is Zoe, Maya, Luis, Ben, Aisha. The 1 selects the first column in the range, while -1 requests descending order. For a numeric list, the same setting puts the largest number first.

For a wider example, assume A2:C6 contains these records, with Name in column A, Region in B, and Sales in C: Ann / East / 420; Bo / West / 310; Cam / East / 560; Dev / North / 420; and Eli / West / 275. With these records in A2:C6, enter =SORT(A2:C6). Excel returns all three fields for each record, sorting rows alphabetically by the first column: Ann, Bo, Cam, Dev, Eli. Sorting the whole record range is important; sorting names separately from sales could disconnect people from their values.

Sort records by another column

To order the sample records by sales instead of by name, set sort_index to 3 because Sales is the third column inside A2:C6. With the records in A2:C6, enter =SORT(A2:C6,3,-1). The output starts Cam / East / 560, followed by the two records with sales of 420, then Bo / West / 310 and Eli / West / 275. The two 420 records have the same sort value, so specify another key if their order matters.

You can sort by a column that is not part of the returned range with SORTBY. SORTBY’s syntax lets you provide a range of sort keys separately, which can be easier to read when the sort key is far from the data or when you need several keys. The sort-key ranges should have the same number of rows as the array.

With the sample records in A2:C6, enter =SORTBY(A2:C6,C2:C6,-1,A2:A6,1). The result sorts Sales from highest to lowest and then sorts tied sales by Name from A to Z. Thus the 420 records appear as Ann, then Dev. SORTBY can take additional key-and-order pairs when you need more levels of sorting.

Use a custom order

Alphabetical order is not always the order a report needs. For instance, a priority list may need High before Medium before Low. One formula-based approach is to assign each category a position with XMATCH, then sort by those positions. XMATCH, like SORT, is available in Microsoft 365 and Excel 2021 or later.

Assume A2:C6 contains task names, priorities in B2:B6, and details in C2:C6, with each priority exactly matching High, Medium, or Low. With that layout, enter =SORTBY(A2:C6,XMATCH(B2:B6,{"High","Medium","Low"}),1). XMATCH assigns High the position 1, Medium 2, and Low 3, so SORTBY returns the High-priority records first. The listed priority values must match the cell values; unexpected text such as Urgent will produce a lookup error.

If the custom order is a one-time manual task, the ribbon’s Sort dialog may be simpler: its Order options include Excel’s built-in custom lists, such as weekdays and months. A formula is useful when you want a repeatable view that updates as source values change. The formula above defines its own order explicitly rather than relying on a custom list saved in Excel.

Sort dates and other data types

Excel stores genuine dates as serial numbers, so ascending date order runs from earliest to latest and descending order runs from latest to earliest. Suppose A2:A6 contains five valid Excel dates. With those dates in A2:A6, enter =SORT(A2:A6,1,-1) to show the most recent date first. Format the result cells as dates if Excel displays serial numbers rather than calendar dates.

Dates that look alike on screen can behave differently if some are text and others are real date values. If sorting gives an unexpected order, check whether the entries are stored consistently; changing a cell’s number format does not always convert text into a date. The same warning applies to numbers stored as text, which may sort differently from numeric values. Clean or convert the source data before relying on the output.

For sorting across columns rather than down rows, set the fourth argument to TRUE. With a horizontal list of values in A1:E1, enter =SORT(A1:E1,1,1,TRUE) to return the values in ascending order across the row. This is a less common layout; ordinary tables are typically sorted by rows.

Formula sorting versus the Sort command

Choose SORT or SORTBY when you want a separate, automatically updated sorted view and need to preserve the source order. Choose the ribbon command when you want to rearrange the original data, including when you are preparing a table for editing or sharing. A formula is not a replacement for changing the order of the source cells: changing the source requires using the Sort command or another data operation.

To sort a dataset manually in Excel for Microsoft 365 on Windows, select a cell in the data, then choose Data > Sort & Filter > Sort. In the dialog, select the column under Sort by, then choose the sort order. Use Add Level to set a secondary key, such as Sales descending and Name ascending. If the data has headings, confirm that My data has headers is selected so Excel treats the top row as labels rather than as a record.

For a quick single-column sort, select a cell in that column and choose Data > Sort & Filter > Sort A to Z or Data > Sort & Filter > Sort Z to A. When Excel asks whether to expand the selection, choose the option that keeps related columns together if the rows represent complete records. Sorting just one column can scramble the relationships between names, dates, and values.

On Excel for Mac, the equivalent sorting controls are on the Data tab, though their placement can vary with the ribbon layout. Excel for the web also has sorting commands on its Data tab. Menu sorting changes the source order, whereas a SORT formula spills a separate result; that distinction is the most useful way to choose between them.

Spill behavior and common problems

A SORT formula needs a clear area for its results. If a value, another formula, or a merged cell blocks the expected output range, Excel displays a #SPILL! error. Clear the obstructing cells or move the formula to a location with enough empty rows and columns. A SORT formula entered inside an Excel table also cannot spill as a dynamic array there; place it in the worksheet grid outside the table.

Unexpected headers: Use the data rows in the array, not the heading row. For example, A2:C6 excludes headings in row 1.
Rows no longer match: Sort the entire record range, not just the column you want to order by. Use SORTBY when the key is in a separate range.
Wrong direction: In SORT and SORTBY, 1 means ascending and -1 means descending.
Unexpected order: Check for leading or trailing spaces, mixed data types, and numbers or dates stored as text.
Unwanted blanks: If your selected range includes blank cells, they are part of the array. Use a range that fits the records you intend to display.

To make a formula-based view grow with new records, you can use an Excel table as the source. For example, if the table is named SalesData, a formula such as =SORT(SalesData) entered outside the table sorts its data rows and can include newly added table rows. The table’s headings are not part of its data body. Keep the spill area clear, and confirm that the first column is the one you intend to sort by.

Frequently Asked Questions

How do I sort alphabetically in Excel with a formula? If the names are in A2:A100, enter =SORT(A2:A100) in an empty cell to return them from A to Z. Use =SORT(A2:A100,1,-1) for Z to A. These formulas create a sorted copy rather than rearranging the original cells.

Can Excel SORT sort by a different column? Yes. If the range A2:C20 should be sorted by its third column, use =SORT(A2:C20,3,1) for ascending order. Use SORTBY when the sort key is outside the returned range or when you need multiple sort keys.

Why does my SORT formula show #SPILL!? Excel cannot place the full result because one or more cells in its spill area are occupied or merged, or the formula is inside a table. Clear the blocked area or put the formula in an empty worksheet area outside the table.

Does SORT change the original data? No. SORT returns a formula-driven sorted result in another location. To change the actual row order, use the Data tab’s Sort command and sort the complete dataset so each record stays together.

Can I sort by two columns with SORT? Use SORTBY for that job. Give it the data array, then a key range and order for each level—for example, sales descending followed by names ascending. The key ranges must align row-for-row with the records.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.