Excel A to Z Formula: How to Sort Data

With the values to sort in A2:A20 and an empty output area beginning at C2, enter =SORT(A2:A20) in C2 to return them in A-to-Z order. SORT is available in Microsoft 365 and Excel 2021 or later; if your Excel version does not support it, use the Data tab’s built-in sort command instead.

The basic A-to-Z formula

SORT arranges a range or array and returns the result in a new location. For a single column of names, the formula in the opening example reads the entries in A2:A20 and spills the sorted list down from C2. The original names stay where they are. Because the formula’s optional settings are omitted, Excel sorts in ascending order: text alphabetically, numbers from smallest to largest, and dates from earliest to latest.

The formula cell must not be inside the source range. If the source is A2:A20, placing the formula in C2 keeps the input and output separate and avoids a circular reference. Leave enough empty cells below the formula for the result. If other content blocks the area where Excel needs to return results, Excel displays a #SPILL! error.

Sorting A to Z does not remove repeated entries. If a name occurs three times in the source range, it appears three times in the sorted result. SORT rearranges values; it does not deduplicate, clean, or otherwise change the source data.

How SORT’s arguments work

SORT can be used with just the range to sort, as above, or with optional arguments that specify a sort column and direction. The first optional argument, sort_index, identifies which column or row to use as the sort key. The next, sort_order, uses 1 for ascending order and -1 for descending order. The final argument, by_col, determines whether Excel sorts rows or columns; its default is FALSE, meaning it sorts rows.

For a more explicit single-column formula, assume names are in A2:A6 and the output begins in C2. Enter =SORT(A2:A6,1,1) in C2. The first 1 says to sort by the first column of the selected range, and the second 1 requests ascending order. If A2:A6 contains Ava, Ben, Eli, Maya, and Zoe in a mixed order, the result is Ava, Ben, Eli, Maya, Zoe.

To reverse that order, keep the same layout and enter =SORT(A2:A6,1,-1) in C2. That returns the list from Z to A. The sort index is still 1 because the source contains one column; only the sort-order setting changes.

Sort complete rows by a name or other key

When a list has related fields, such as a person’s name, department, and start date, sort the full range rather than sorting the name column on its own. Otherwise, the names can become separated from their corresponding departments and dates. Assume names are in A2:A6, departments are in B2:B6, and dates are in C2:C6, with the output area starting at E2. Enter =SORT(A2:C6,1,1) in E2 to sort complete rows by the first column, the names, while keeping each row’s other values together.

To sort those same rows by the department in the second column, use the same source and output layout and enter =SORT(A2:C6,2,1) in E2. The sort index is 2 because department is the second column inside A2:C6. The formula rearranges the rows according to that field and preserves the relationship between each row’s entries.

A header should normally be left out of the source range. If the headings are in row 1 and the records are in rows 2 through 6, sorting A2:C6 keeps the headings out of the result. To show headings above a formula-generated result, type or copy the headings into the output area separately.

Sorting across columns instead of down rows

SORT normally organizes rows from top to bottom. Its by_col setting changes the operation so Excel sorts columns from left to right instead. This is useful for a layout where each column represents a period or category and the items to sort appear in a single header row, though it is less common than sorting records down a table.

For example, assume months are in B1:F1, their associated figures are in B2:F2, and the output starts at H1. Enter =SORT(B1:F2,1,1,TRUE) in H1 to sort the columns according to the first row of the selected range. The final TRUE tells SORT to arrange columns rather than rows; the month labels and their figures move together. Make sure the output area has room for the returned two-row array.

Dynamic results and where to put the formula

A SORT formula is a dynamic array formula. You enter it once, in the top-left cell of the intended result, and Excel fills the neighboring cells with the sorted values. Those returned cells are called the spill range. Do not type into cells in that range: the formula controls them, and Excel reports a spill error if an existing value, merged cell, or other obstruction prevents the result from appearing.

When the source values change, the returned order updates automatically. If a value in the source is edited, the result reflects the new order; if the range is a fixed reference such as A2:A20, however, adding a new record in A21 does not automatically extend that reference. You can update the range in the formula or use an Excel Table as the data source so that new table rows are included more naturally in structured references.

Dynamic array formulas work in current Microsoft 365 Excel on Windows, Mac, and the web, though the interface can differ slightly. In Excel for the web, the formula is entered in a worksheet cell in the same way. Excel 2021 also supports SORT. Excel 2019 and earlier do not include this function, so a formula using it may return #NAME? rather than a sorted list.

Formula sorting versus the Data tab

Use a formula when you want a separate, automatically updated sorted view while keeping the original data in its current order. Use the ribbon sort when you want to reorder the original records. In Microsoft 365 for Windows, select a cell in the list and choose Data > Sort A to Z for an ascending sort. If the data has adjacent columns, Excel may ask whether to expand the selection; choose to expand it so the row information stays together.

The ribbon command changes the position of the selected records in the source data. A SORT formula instead displays a calculated result elsewhere and leaves the source alone. The ribbon option is often a better fit for a one-time cleanup or for older Excel versions; a formula is useful for a dashboard, report, or view that should stay sorted as source entries change.

For data with headers or several sort criteria, use the full Sort dialog from Data > Sort rather than assuming a one-click A-to-Z sort will meet every requirement. The dialog lets you identify the column to sort by and add levels, such as sorting first by department and then by name. Keep each record in one row when applying a sort to a table.

Common problems and how to fix them

#SPILL!: Check the cells where the sorted results should appear. Clear any contents or merged cells in the way, and confirm the formula is not being placed inside an Excel Table, where spilled array results are not supported in the usual way.
#NAME?: The Excel version may not support SORT, or the function name may have been mistyped. Use the Data tab’s sorting tools in an older version.
Rows no longer match: The sort may have been applied to only one column. Sort the entire data range or use a formula that includes all related columns.
Unexpected order: Leading or trailing spaces, inconsistent text, and values stored in different formats can affect how entries are arranged. For instance, a number stored as text may not behave like a numeric value. Clean or standardize the source data if the displayed order does not match the intended one.
New records are missing: A fixed range stops where its reference stops. Extend the source reference or organize the data as an Excel Table and use a table-based reference that grows with new records.

Blank cells, duplicate values, punctuation, and mixed data types can also make an otherwise valid sort look surprising. If two records share the same sort key, SORT does not give you a way in that basic formula to specify a second tie-breaker. For a multi-level order, sort the source with the Data tab’s Sort dialog, or build a formula that explicitly handles the additional criteria.

Choosing the right approach

For a short list that needs a live alphabetical copy, SORT is usually the simplest option. For a table you want to rearrange permanently, use Data > Sort A to Z or the Sort dialog. For a workbook shared with people using older Excel editions, a ribbon sort is more compatible than a SORT formula. Consider how the result will be used: a spill formula creates a view, while a standard sort changes the order of the data itself.

Before sorting important records, check that all related fields are included and that the data has a clear header row. A formula-generated result is useful for viewing, but it does not automatically rewrite the source range. Conversely, a ribbon sort changes the source order and can affect other formulas or users who expected that order to remain fixed.

Frequently Asked Questions

What is the Excel formula to sort A to Z?
With the values in A2:A20 and a clear output area beginning at C2, enter =SORT(A2:A20) in C2. Excel returns an ascending, A-to-Z list in the cells below.

How do I sort a table by one column but keep each row together?
Include all the related columns in the range you sort, then choose the column number for the sort key. SORT rearranges complete rows in its returned result when the source range includes the complete records.

Can I sort Z to A with a formula?
Yes. Set SORT’s sort-order argument to -1. This reverses the direction while leaving the source range and sort-key column unchanged.

Does SORT remove duplicate names?
No. It sorts entries but retains duplicates. A name that appears more than once in the source appears the same number of times in the sorted result.

Why is my A-to-Z formula showing #SPILL!?
Excel cannot place the full result in the required cells. Clear obstructions in the spill range, check for merged cells, and put the formula outside the source range and outside an Excel Table.

Can I use SORT in Excel 2019?
No. SORT is supported in Microsoft 365 and Excel 2021 or later, but not Excel 2019. Use Data > Sort A to Z or the Sort dialog to arrange the records in that older version.

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.