To create a sorted list in Excel, select a cell in your data and choose Data > Sort A to Z or Data > Sort Z to A. For a separate sorted copy that updates when the source changes, enter Excel’s SORT function in an empty area of a Microsoft 365 worksheet.
What a sorted list means in Excel
Sorting rearranges values according to a rule: alphabetically, numerically, by date, or by another property. A simple sort changes the order of the existing rows. It does not make a new list or delete records. If you need to preserve the original order, copy the data first or use a formula to display a sorted result elsewhere.
In a table of employees, for example, sorting the Department column alphabetically should move each employee’s entire row, including their name and start date. Sorting just the department cells on their own would separate departments from the correct employees. Excel usually warns you if you select one column within a larger data range and asks whether to expand the selection; choose to expand it when the columns belong together.
Before sorting, check that each column has a clear heading and that every record occupies one row. Avoid blank rows or columns inside the data. These simple layout choices help Excel recognize the full list and keep related values together.
Sort a list with the ribbon
For a quick ascending or descending sort in Excel for Microsoft 365 on Windows, click any cell in the column you want to sort. Then use Data > Sort A to Z for ascending order or Data > Sort Z to A for descending order. The labels change with the selected data type: dates and numbers sort in chronological or numerical order rather than alphabetically.
- Click a cell in the list. If the data is not a formatted table, make sure the list has headers and no empty rows splitting it.
- Open the Data tab and select the ascending or descending sort button in the Sort & Filter group.
- If Excel asks whether to expand the selection, choose Expand the selection to keep each row intact, then confirm the sort.
- Check a few records to ensure their values in other columns still match.
For a formatted Excel table, clicking a cell in the desired column and using the ribbon sort command sorts the table rows together. You can also open the filter arrow in a table’s heading and choose a sort option there. In Excel for Mac, the Data tab and sort options are also available, although ribbon layout and wording can differ slightly by release. Excel for the web offers sorting from the Data tab and from table or filter menus; available controls may vary with the current selection.
Sort by multiple columns or rules
A single-column sort is not enough when values repeat. To sort first by department and then by last name, open the full Sort dialog using Data > Sort. Confirm that My data has headers is selected if the first row contains labels. Choose the first heading under Sort by, leave the sort type as Cell Values, and choose an order. Select Add Level to specify the next column and order.
For instance, to list employees by department and then alphabetically by surname, set Department as the first level and Surname as the second. Excel applies the second level within groups tied on the first one. You can add more levels when the data calls for them, such as department, surname, and then first name.
The Sort dialog can also order cells by fill color, font color, or a conditional-formatting icon when those properties are present. Choose the relevant option in the Sort On field, then specify which color or icon should appear first. This is useful for prioritizing highlighted records, but color-based sorting is less transparent than sorting by a status column. A text field such as Urgent, Normal, and Low makes the rule easier to understand and maintain.
Use a custom order for categories
Alphabetical order is not always the order people expect. A priority list such as High, Medium, Low would sort alphabetically in a different sequence. In the Sort dialog, use the Order menu and select Custom List to choose a built-in order or create one for the sort. Excel also recognizes familiar sequences such as days of the week and months.
On Windows, you can manage custom lists through File > Options > Advanced, then find the General section and choose Edit Custom Lists. A custom sequence can be useful for repeated work, such as sorting by regional sales territories or by the stages of a process. Check the selected list before applying the sort: a custom list defines the order of the values it contains, while values not in that list may be placed after them.
If the order is important to the workbook’s users, consider adding a numeric rank column—for example, 1 for High, 2 for Medium, and 3 for Low—and sorting by that column. The ranking is visible, works well in shared files, and is easier to reproduce than relying on a saved custom list on one computer.
Create a separate sorted list with a formula
The SORT function returns a sorted copy in a new area instead of rearranging the source cells. It is available in Microsoft 365 and Excel 2021 or later. Leave enough empty cells below and beside the formula for the result to spill; otherwise, Excel may show a #SPILL! error.
Assume A1:B5 contains the headings Name and Score, and A2:B5 contains Ava, 84; Ben, 92; Cam, 76; and Dia, 88. With that layout, enter this formula in D2 to sort the two-column records by Score from highest to lowest:
=SORT(A2:B5,2,-1)
The result is Ben, 92; Dia, 88; Ava, 84; and Cam, 76. The first argument is the range to return, the second identifies its second column as the sort key, and -1 requests descending order. Use 1 for ascending order. Because the formula refers to the source range, changing a score updates the displayed sorted result automatically. The source range itself remains in its original order.
Enter the formula outside the source data, not inside the cells it needs to return. If the source range grows beyond the rows named in the formula, update that range or use an Excel table as the source reference. A normal ribbon sort is often simpler for a one-time rearrangement; a formula is useful for dashboards and reports where the original data should remain untouched.
Prepare your data before sorting
Excel sorts values according to how it interprets them, so inconsistent data can produce surprising results. A column that looks numeric may contain numbers stored as text, and dates pasted from different sources may not all be recognized as dates. Mixed formats can make values appear out of sequence even when the sort command worked as designed.
- Use one type of value per column. Keep dates together, numbers together, and category labels together where possible.
- Check for numbers stored as text. Look for warning indicators or values aligned differently from nearby numbers, then convert them consistently before sorting.
- Keep headers in one top row. Avoid merged heading cells within the data, which can make selecting and sorting a range awkward.
- Remove accidental blank rows and columns. A gap can make Excel treat part of a list as a separate region.
- Decide how blanks should be handled. Review empty cells after sorting, especially if they represent missing information rather than intentional blanks.
For repeat use, convert a clean range into an Excel table with Insert > Table, or press Ctrl+T on Windows. On Mac, the equivalent shortcut is Command+T. Confirm whether the range has headers. Tables provide heading filter arrows and generally extend to include new rows added directly beneath them, making routine sorts easier to manage.
Common sorting problems and how to fix them
Only one column moved. If related columns no longer match, undo the sort immediately with Ctrl+Z on Windows or Command+Z on Mac. Then select the complete range and sort again, or choose to expand the selection when Excel prompts you. Sorting a single selected column without expanding it can disconnect values from their records.
Numbers appear in the wrong order. A text value such as 100 may be sorted alongside text rather than as a number, so it can appear before 2. Convert the entire column to numbers consistently, then sort again. Do not fix only the visible problem cells if the column contains mixed types.
Dates are not chronological. Check whether all entries are actual Excel dates rather than text that merely looks like a date. Regional date formats can also cause ambiguity—for example, a date with a day and month both at or below 12 may be interpreted differently depending on the source and settings. Verify a few cells before relying on the order.
The sorted formula shows an error or incomplete results. For a #SPILL! error, clear cells blocking the output area. For results that omit newer records, extend the formula’s source range or use a table-based range that includes them. If SORT is not recognized, the Excel version may not support dynamic-array functions; use the ribbon sort instead or open the workbook in Microsoft 365 or Excel 2021 and later.
Choose the right sorting method
Use a ribbon sort when you want to rearrange the existing list once, such as alphabetizing a contact list before printing. Use the Sort dialog when the order depends on several columns, a custom sequence, or cell color. Choose a formula-generated list when the original data must remain unchanged and the displayed order should refresh as the source changes.
These methods solve different problems: the ribbon changes the source order, while a formula creates a separate result. A filter is different again. Filtering hides rows that do not meet criteria but does not, by itself, sort the rows that remain. You can filter and sort together, for example to show only open orders and then arrange them by due date.
Frequently Asked Questions
How do I sort a list alphabetically in Excel?
Click a cell in the list’s text column, then choose Data > Sort A to Z. If the list has related columns, expand the selection when prompted so complete rows move together.
How do I sort a list without changing the original data?
In Microsoft 365 or Excel 2021 and later, use SORT in an empty area to produce a separate sorted result. Alternatively, copy the source data to another sheet and sort the copy using the ribbon.
Can I sort by two columns in Excel?
Yes. Open Data > Sort, choose the first column and order, select Add Level, and specify the second column. The second rule applies within ties in the first column.
Why does Excel sort 1, 10, and 2 in that order?
The values may be stored as text rather than numbers. Convert the entries to numeric values consistently, then sort again. Text sorting compares characters, which can produce an order that differs from numerical order.
Can Excel keep a sorted list updated automatically?
A SORT formula recalculates when values in its referenced range change. Make sure the formula includes any new rows; a fixed range does not automatically include data added beyond its last cell.
Related
- Copy an Entire Excel Spreadsheet to a New File
- Copy and Paste X Times in Excel: Repeat Cells or Rows
- Convert H:M:S to a Number in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Monthly Excel Budget Spreadsheet: Setup and FormulasTo make a monthly Excel budget spreadsheet, list spending categories in column A, planned amounts in column B,…
- Sumifs with date range?Learn how SUMIFS handles dates, boundaries, months, extra criteria, and common errors, with clear Excel examples to help…
- Excel Formula Opposite of CONCATENATE: Split Text ApartThe closest Excel formula to the opposite of CONCATENATE is =TEXTSPLIT(A2,"|"): it takes text in A2 and splits…
- Excel SUMIF: Sum Values When a Checkbox Is CheckedTo add only the values whose checkboxes are checked, put each checkbox’s TRUE/FALSE value in one column and…
- COUNTIFS Does Not Contain: Excel Formula and ExamplesTo count cells that do not contain a particular text string in Excel, use a COUNTIFS criterion with…
- Excel Date Minus 1 Month? Formula and Date RulesTo subtract one calendar month from an Excel date, use =EDATE(A1,-1), where A1 contains a real Excel date.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.