Guide Dynamic arrays
I want a sorted list that stays sorted
Sorting from the ribbon rearranges your actual data and has to be redone every time a row is added. A formula makes a sorted copy that re-sorts itself and leaves the original alone.
=SORT(range, column, order) returns a sorted copy;
=SORTBY(range, by_range, order) sorts by something that need not be in the
result. Order is 1 for ascending and -1 for descending. Both leave
your data exactly where it is and re-sort on their own when it changes, which ribbon sorting cannot
do.
Why sorting from the ribbon causes trouble
Ribbon sorting physically rearranges rows. Three consequences follow, and all of them bite eventually:
- It has to be repeated. A row added tomorrow sits at the bottom until someone sorts again.
- It can be done wrong. Selecting one column and sorting it alone, without extending the selection, detaches that column from its rows. Excel warns, the warning gets dismissed, and the data is now silently scrambled.
- The original order is gone. Unless there was a column recording it, entry order cannot be recovered.
A formula avoids all three. It produces a copy, in order, that keeps itself current.
| What you see | What actually happened | What fixes it |
|---|---|---|
| New rows sit at the bottom | Ribbon sorting is a one-off action | Use SORT for a live copy |
| Columns no longer line up | One column was sorted without the others | Undo immediately; use a formula instead |
| Cannot get the original order back | Nothing recorded it | Keep an entry-order column |
| Want the top 10 only | SORT returns everything | Wrap it in TAKE, or use LARGE |
| Sorted by text, not by value | The column is numbers stored as text | Convert it to numbers first |
How to sort with a formula
Sort a range by one of its columns
Column number counts from the left of the range you gave it, not from column A of the sheet:
=SORT(A2:D500,4,-1)Sort by the fourth column, descending. The result spills, so put it where there is room.
Sort by something you do not want to show
This is the difference between the two functions:
=SORTBY(A2:B500,D2:D500,-1)Return columns A and B, ordered by column D. SORT cannot do this because
its column number has to be inside the range it returns.
Sort by two things at once
Add more pairs of range and order:
=SORTBY(A2:D500,B2:B500,1,D2:D500,-1)Region A to Z, and inside each region the largest value first. Read left to right: the first pair is the outer order.
Take just the top few
A live top-10 in one formula:
=TAKE(SORT(A2:D500,4,-1),10)TAKE needs Microsoft 365. On Excel 2021, INDEX with
SEQUENCE does the same: =INDEX(SORT(A2:D500,4,-1),SEQUENCE(10),{1,2,3,4}).
Everything on this page, as a workbook you can use on your own data
The Excel Data Cleanup Kit — a seven-tab workbook that finds all nine of these faults in a pasted column and hands back a cleaned version, a five-page PDF guide, and a short read-me. Free, no email required.
- Excel-Data-Cleanup-Workbook-Free.xlsx — paste a column, read the diagnosis, take the cleaned output
- Excel-Data-Survival-Guide.pdf — the eight failures, the import routine that prevents them, every formula explained
Plain .xlsx: no macros, no add-ins. Opens in Excel, Google Sheets,
Apple Numbers and LibreOffice Calc.
Blank rows sort to the bottom as zeros
A range that includes empty rows below your data returns those rows too, as zeros and blanks. Bound the range to the rows you actually have, or filter first:
=SORT(FILTER(A2:D500,A2:A500<>"","none"),4,-1)FILTER removes the empty rows, then SORT orders what is left.
Keep an entry-order column
Even with formula sorting, a plain incrementing number beside your data is worth having. It is the only way back to the order things were entered, and it costs one column.
Sorting text with numbers in it
Item 10 sorts before Item 9, because text sorts character by character
and 1 comes before 9. This is not a bug and there is no setting for it. The
fixes are to pad the numbers (Item 09) or to split the number into its own column and
sort on that.
Google Sheets
Sheets has both functions. SORT takes the same arguments; SORTBY is
spelled SORTN for the top-N case and behaves slightly differently, so check the arguments
rather than assuming.
Questions people ask
What is the difference between SORT and SORTBY?
SORT orders a range by one of its own columns. SORTBY orders it by a separate range, so you can sort by a column you are not displaying.
Does sorting with a formula change my data?
No. It produces a sorted copy elsewhere and leaves the original untouched, which is the main reason to prefer it.
How do I sort by two columns?
Use SORTBY with more pairs: =SORTBY(range, first, 1, second, -1). The first pair is the outer order.
How do I get just the top 10?
Wrap a descending sort in TAKE. On Excel 2021 without TAKE, use INDEX with SEQUENCE.
Why does Item 10 sort before Item 9?
Text sorts character by character, and 1 comes before 9. Pad the numbers, or split the number into its own column and sort on that.
Why do I get rows of zeros at the bottom?
The range includes empty rows below your data. Bound the range, or wrap it in FILTER to drop the blanks before sorting.
Related guides
Take the workbook with you
The Excel Data Cleanup Kit — a seven-tab workbook that finds all nine of these faults in a pasted column and hands back a cleaned version, a five-page PDF guide, and a short read-me. Free, no email required.
- Excel-Data-Cleanup-Workbook-Free.xlsx — paste a column, read the diagnosis, take the cleaned output
- Excel-Data-Survival-Guide.pdf — the eight failures, the import routine that prevents them, every formula explained
Plain .xlsx: no macros, no add-ins. Opens in Excel, Google Sheets,
Apple Numbers and LibreOffice Calc.