SORTBY returns a sorted copy of your table without touching the original. To sort expenses by category A to Z and then by amount largest first, use =SORTBY(A2:C500,A2:A500,1,C2:C500,-1). The sorted view updates when you add or change rows. SORTBY needs Excel 2021 or Microsoft 365.
What does the table look like?
Suppose category is in column A, date in B and amount in C, with data in rows 2 to 500. You want a view grouped by category in alphabetical order, with the biggest amount first inside each category. Here are five hypothetical rows to test with.
| Category | Date | Amount |
|---|---|---|
| Food | 3 Oct | ₹450 |
| Travel | 5 Oct | ₹2,200 |
| Food | 9 Oct | ₹1,800 |
| Bills | 12 Oct | ₹999 |
| Travel | 14 Oct | ₹640 |
The basic formula
SORTBY(array, by_array1, sort_order1, by_array2, sort_order2) takes the table to return, then the column to sort by and the direction. In the direction, 1 means ascending and -1 means descending.
=SORTBY($A$2:$C$500,$A$2:$A$500,1,$C$2:$C$500,-1)
Type it in an empty area, say F2. The result spills to the right and down, and for the five rows it reads: Bills 999; Food 1,800; Food 450; Travel 2,200; Travel 640. Categories run A to Z, and within Food and Travel the larger amount comes first. Type your own headings above it, since SORTBY returns only the data.
What about the blank rows?
A range that runs to row 500 includes empty rows. Remove them first with FILTER, which also needs Excel 2021 or 365:
=LET(d,FILTER($A$2:$C$500,$A$2:$A$500<>""),SORTBY(d,INDEX(d,,1),1,INDEX(d,,3),-1))
FILTER keeps only the rows where category is not empty. INDEX(d,,1) picks out the first column of that result and INDEX(d,,3) the third, so the sort refers to the right columns even though the data is now a new array.
Can it update when I add rows?
Yes, in two ways. If your range is fixed, as in the examples, anything typed within row 500 is picked up. A better approach is to turn the data into an Excel Table, by selecting it and pressing Ctrl+T. If the table is named Expenses, the formula becomes:
=SORTBY(Expenses,Expenses[Category],1,Expenses[Amount],-1)
A table grows automatically as you add rows, and the sorted view grows with it.
Why not use the Sort button?
- The Sort button rearranges the original data, which may break a running balance or the order of your statement.
- It is a one-time action. New rows are not sorted until you do it again.
- SORTBY leaves the source as it is and gives you a live second view.
Can I add a third sort level?
Yes. Add another pair at the end. To sort by category, then amount largest first, then date oldest first when amounts are equal, the formula is =SORTBY($A$2:$C$500,$A$2:$A$500,1,$C$2:$C$500,-1,$B$2:$B$500,1). The dates must be real dates, not text, or they will sort as words.
Why do some categories sort in the wrong place?
Text sorts by its characters, so a category typed with a leading space, such as ' Food', sorts before everything else, and a typo like Fod becomes its own group. The sort ignores upper and lower case, so Food and food sit together, but a stray space still makes two names differ. Tidy the column first. A helper formula such as =TRIM(A2) in another column, filled down and then used as the sort key, removes stray spaces without changing your original.
What if I am on an older Excel?
Excel 2019 and earlier will show #NAME?. Use Data, then Sort, and add two levels in the dialog: category A to Z, then amount largest to smallest. Do it on a copy so your original order is safe. It will not update itself, so repeat it after adding rows.
If you find yourself re-sorting every month after pasting statements, a tracker that imports statements and keeps categories ready can take that routine away.
Quick checklist
- Check that amounts are numbers, not text, or they will sort oddly.
- Keep the area for the result empty.
- Use FILTER or a Table to avoid blank rows.
- Compare the first few results with the source to confirm the order.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


