Excel Named Range Sort: Sort Data or Name Definitions

Formulas & functions · Updated November 19, 2026

In Excel, a named range is a label for a cell or group of cells, not a separate list that you can sort by itself. To sort the data a name refers to, select that range and use Data > Sort, or, in Microsoft 365 or Excel 2021 and later, enter a SORT formula such as =SORT(SalesData,2,-1) to display a sorted copy.

What “sort a named range” can mean

The phrase can describe two different tasks. You might want to sort the values in the cells covered by a name such as SalesData, or you might want to sort the list of names in Excel’s Name Manager. These are not the same operation: a name points to a reference, while a sort rearranges rows or values in cells.

For example, suppose SalesData refers to A2:B5, where column A contains employee names and column B contains sales. Sorting SalesData by its second column rearranges the rows in those worksheet cells. The name still refers to A2:B5; it does not become a stored, independently sorted collection. If you mean arranging names or definitions in Name Manager, Excel does not offer a worksheet-style Sort command there. You can use Name Manager to review and filter definitions, but sorting the underlying worksheet data is a separate task.

Sort the cells a name refers to

For a one-time sort that changes the worksheet data, first identify the full range the name covers. In Excel for Microsoft 365 on Windows, open Formulas > Name Manager, select the name, and check the Refers To field. You can also type the name into the Name Box to the left of the formula bar and press Enter; Excel selects the referenced cells when the name identifies a worksheet range.

  1. Select the entire data block, including every column that belongs to each record. If the named range covers the whole block, selecting it is a useful starting point.
  2. Choose Data > Sort. In the Sort dialog, select the column to sort by, the sort order, and whether the first row contains headers.
  3. Click OK and check that values from each row stayed together. Sorting one column by itself can separate names from the figures or details associated with them.

For the example with employee names in A2:A5 and sales figures in B2:B5, select A2:B5 and sort by the sales column, largest to smallest. If the figures are 12, 19, 15, and 8 in row order, the resulting order is 19, 15, 12, and 8, with each employee name moving with the matching figure. If Excel asks whether to expand the selection, choose the option that includes the related columns.

On Mac, the equivalent command is on the Data tab; select the range and use Data > Sort. Excel for the web also has sorting commands on the Data tab, though dialog details can differ slightly from the desktop app. The essential requirement is the same in all versions: sort complete records together, not an isolated column that has related data beside it.

Use SORT to return a sorted copy

A formula is preferable when you want to keep the original data order and show a sorted result elsewhere. In Microsoft 365 or Excel 2021 and later, the SORT function returns a dynamic array that updates when the source values change. It does not rearrange the original cells.

Assume A2:B5 contains four employees and their sales, with no header row, and the workbook name SalesData refers to A2:B5. Enter =SORT(SalesData,2,-1) in an empty cell outside the source range. This sorts by the second column in descending order. With sales of 12, 19, 15, and 8, the displayed result has the rows with 19, 15, 12, and 8 sales, in that order. The first argument is the named range, the second identifies its sort column, and -1 requests descending order; 1 would request ascending order.

Leave enough empty cells below and to the right of the formula for the results to spill. If a cell in the spill area is occupied, Excel returns a #SPILL! error. The formula produces a view, not a replacement for the source: edit the original data if you need to change its values. Dynamic array formulas are not supported inside Excel tables as spilling formulas, so place the SORT formula in ordinary worksheet cells outside a table.

To sort by another column, change the column index. For a named range covering A2:C20, the number 1 means its first column, 2 its second, and 3 its third. The index is relative to the named range, not necessarily the worksheet column letter. A name beginning in column D still uses 1 to sort by its first included column.

Check what the name includes

Before sorting, confirm the name’s scope and boundaries. A workbook-scoped name can be used throughout the workbook, while a worksheet-scoped name belongs to one sheet and may share its text with a workbook-scoped name. In formulas, Excel can resolve a name differently depending on scope, so check the Name Manager entry if a formula sorts an unexpected range.

Also check whether the name includes a header row, blank rows, totals, or extra columns. The SORT formula does not automatically identify headers: if the header is inside the named range, Excel treats it as ordinary data and may move it into the sorted results. A manual sort has a setting to identify headers, but it is still important to select the correct range and verify the result. For a formula-based output, define the name to include data rows only, or handle the header separately.

A fixed name such as =Sheet1!$A$2:$B$20 continues to refer to those cell addresses. If you add new records below row 20, they may not be included in either a manual selection of the name or a formula using it. Update the definition or use a range design that expands with your data. Excel tables are often a practical choice for growing datasets because they expand as records are added, but verify exactly which table reference a name uses before relying on it.

Choose between rearranging data and showing a sorted view

Use the Data > Sort command when the worksheet itself should be reordered—for example, before printing a final report or when users need the records permanently displayed in a particular sequence. This changes the order of the source cells, so save a copy first if the original order matters. Undo can reverse a recent worksheet sort, but it is not a substitute for keeping an original copy when the workbook has important data or later edits.

Use SORT when you want a live, separate view while preserving the source order. It is useful for a dashboard or a comparison that should update as the source values change. Keep in mind that the sorted result is a formula output: typing into a cell occupied by the spill range is not the way to edit a record. Change the source data instead, and the result recalculates.

  • Change the source order: use Data > Sort on the complete range.
  • Keep source order and create a changing result: use SORT in an empty area.
  • Sort only temporarily for browsing: use filters or a table’s sort controls, while checking that the full records remain together.

Common problems and how to fix them

If Excel sorts only one column, undo the action immediately if possible and repeat it with the whole record range selected. When prompted, expand the selection. A row represents one record; keeping all its fields together is essential. For a table, use the sort control in the relevant header so Excel sorts the table’s rows together.

If the result looks incorrectly ordered, check the data type and sort settings. Numbers stored as text can sort differently from numeric values, and dates stored as text may not follow chronological order. Mixed entries, leading spaces, or inconsistent date formats can also produce results that seem surprising. Correct the source values and sort again rather than assuming the named range itself is faulty.

If a SORT formula returns #NAME?, confirm that the function is available in your Excel version and that the named range is spelled correctly. SORT is available in Microsoft 365 and Excel 2021 or later, but not in every older perpetual version. If the formula returns #SPILL!, clear the cells where the output needs to appear. If it omits recent records, inspect the name’s Refers To range and extend it or choose a more suitable expanding source.

What Name Manager can and cannot do

Name Manager is for reviewing, editing, creating, and deleting defined names, not for sorting the records those names refer to. Open it with Formulas > Name Manager on Windows. Its Filter options can help narrow the list by criteria such as names with errors or names scoped to a particular sheet. This is useful when a workbook has many definitions, but it does not alter worksheet data or turn names into a sortable table.

If your goal is to organize the definitions themselves, use clear, consistent names and review their scope and references in Name Manager. For example, prefixes such as Input_, Report_, or Lookup_ can make related names easier to find. If you need an actual sorted inventory of defined names for documentation, that is a different reporting task: Name Manager is not a regular worksheet range that you can sort with Data > Sort. Do not confuse alphabetical navigation or filtering of definitions with sorting the values inside a named range.

Frequently Asked Questions

Can I sort a named range alphabetically?
Yes, if you mean sorting text values in the cells the name refers to. Select the full range and use Data > Sort, choosing the text column and A to Z. If you mean alphabetically sorting the names listed in Name Manager, Excel does not provide a normal worksheet Sort command for that list.

Does sorting change what a named range refers to?
Usually, a name defined as a fixed cell range continues to refer to the same addresses after those cells are sorted. The values and row order in the cells change; the definition is not a separate copy of the data. Check Name Manager if the definition uses a formula or a more complex reference.

Can I sort a named range without changing the original data?
Yes. In Microsoft 365 or Excel 2021 and later, use a SORT formula such as =SORT(SalesData,2,-1) in an empty area, assuming SalesData has at least two columns and the second column is the sort key. The formula displays a sorted result while leaving the source cells in place.

Why does my named-range sort leave out new rows?
The name may refer to a fixed address that stops before the new records. Open Formulas > Name Manager and inspect Refers To, then update the range or use a source structure that expands as records are added. A formula can only sort the cells included in its source.

Can I sort a named range that includes a header?
For a manual sort, select the header option in the Sort dialog so Excel treats that row as labels. For a SORT formula, the function does not automatically recognize headers, so define the range to exclude the header or build the displayed header separately.

Related

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.