Sort Button vs SORT Function in Excel

The Sort button vs the SORT function in Excel is a choice between changing your data and making a sorted copy of it. The Sort button (on the Data tab) reorders the rows of your table once, in place, and works in every version. The SORT function leaves the table alone and returns a live sorted copy that updates when the data changes, in Excel 2021, Excel 2024, Microsoft 365 and Excel for the web. This guide is for anyone who sorts lists at work or in a job test and wants to pick the right tool.

Sort button vs SORT function at a glance#

The Sort button is a one-off action on the data itself; SORT is a formula that keeps a sorted view up to date.

How the Sort button works#

The Sort button rearranges whole rows of your table so they follow the order of one column. Click any single cell in the column you want to sort by, then:

• Windows: Data > Sort A to Z (or Sort Z to A). The keyboard path is Alt, A, S, A for A to Z and Alt, A, S, D for Z to A.
• Mac: Data > Sort A to Z or Sort Z to A.
• Both: Home > Sort & Filter has the same commands.

For more than one level, open the full Sort dialog: Data > Sort (on Windows, Alt, A, S, S). Tick My data has headers, choose the first column under Sort by, then click Add Level for the next one. The dialog can also sort On cell colour, font colour or conditional formatting icon, and Order > Custom List sorts months or weekdays in calendar order instead of alphabetical order.

The safest habit: select one cell, not one column. If you select a whole column on its own, Excel shows a Sort Warning box. Choose Expand the selection. Continue with the current selection sorts only that column and separates each value from the rest of its row. Our guide on how to filter data in Excel covers the same Data tab tools for hiding rows.

How the SORT function works#

SORT is a formula that returns a sorted copy of a range and leaves the source as it is. Its shape is:

=SORT(array, [sort_index], [sort_order], [by_col])

• array: the range to sort, usually without the header row.
• sort_index: which column of the range to sort by (1 is its first column). The default is 1.
• sort_order: 1 for smallest first or A to Z (the default), -1 for largest first or Z to A.
• by_col: TRUE sorts columns left to right instead of rows. You rarely need it.

You type SORT in one cell and the results spill into the cells below and to the right. If any of those cells already hold something, you get #SPILL!. Our guide to SORT and SORTBY covers SORTBY, which sorts by a column that doesn't appear in the result.

The same sort, side by side#

Here is a small product list. The task: show the best sellers first.

With the Sort button#

Click C2, then Data > Sort Z to A. Rows 2 to 7 now read Sparkling water (210), Iced tea (150), Trail mix (140), Granola bar (120), Orange juice (85), Rice crackers (65). The original order is gone. If you change Orange juice to 300 units, it stays in row 6 until you sort again.

With the SORT function#

In E2, type:

=SORT(A2:C7,3,-1)

E2:G7 fill with the same six rows in the same order, and A2:C7 is untouched. Change Orange juice to 300 and it jumps to the top of the sorted copy by itself.

Two levels: category, then units#

To group by category A to Z and put the best seller first inside each group, the Sort dialog needs two levels: Category A to Z, then Units Largest to Smallest. The formula does it in one line:

=SORT(A2:C7,{2,3},{1,-1})

The result is Drinks first (Sparkling water 210, Iced tea 150, Orange juice 85), then Snacks (Trail mix 140, Granola bar 120, Rice crackers 65).

When to use the Sort button#

Use the Sort button when you want the data itself in a new order. It is the better choice when:

• You're tidying a list once, such as an alphabetical staff list or a price list you will print.
• You need colour or custom-list sorting, such as red cells first, or months in calendar order.
• The file must work in Excel 2019 or older, where SORT doesn't exist.
• You will type into the sorted rows afterwards. Spilled SORT results can't be edited.
• A test asks you to "sort the table". That usually means the Sort button, and it is one of the core tasks in Excel skills for admin and office jobs.

When to use the SORT function#

Use SORT when the original order matters or the data keeps changing. It is the better choice when:

• You need a live leaderboard or report that re-sorts itself when new numbers arrive.
• The raw data must stay in its original order, for example rows in the order they were entered.
• You want to sort a filtered result. =SORT(FILTER(A2:C7,B2:B7="Snacks"),3,-1) shows only Snacks, best sellers first. The FILTER function guide explains the inner part.
• You need a top-N list. Combine SORT with TAKE in Microsoft 365, or compare the result with LARGE as in our comparison of MAX vs LARGE.

Common mistakes when sorting in Excel#

Most sorting problems come from a handful of slips:

1. Sorting one column on its own. Names and numbers no longer line up. Undo straight away with Ctrl+Z (⌘Z) and sort with one cell selected instead.
2. Forgetting the header row. If My data has headers is unticked, the header gets sorted into the data.
3. Counting sort_index from column A of the sheet. In =SORT(B2:C11,2,-1), column 2 means column C, the 2nd column of the range.
4. Blocking the spill area. Any value below or beside the SORT cell gives #SPILL!.
5. Sorting numbers stored as text. "100" stored as text sorts before "25". Convert them to numbers first.
6. Sorting with formulas that point at fixed rows. A formula like =B5*2 outside the table still points at row 5 after the sort, which may now hold a different product. Lookups such as those in VLOOKUP vs HLOOKUP are safer because they find the row by value.

Our recommendation#

Use the Sort button when you want to reorder the data once and keep working in it, and use the SORT function when you need a sorted view that stays up to date without touching the source. In a job test, read the wording: "sort the table" usually means the Sort button, while "create a sorted list" or "show the top sellers" is a good fit for SORT on Microsoft 365.

In short: the button changes your data; the function shows it sorted.

Practise it#

Try the SORT exercise on this page, then work through the SORT function lesson to sort by any column, combine SORT with FILTER and UNIQUE, and see what sort_index really counts. The filter a table lesson covers the Data tab filter buttons and SUBTOTAL, which sit right next to Sort.