Spreadsheets

SORTN to get the top 5 expenses in Google Sheets

Get the five biggest expenses in Google Sheets with a single SORTN formula, handle ties properly and combine it with FILTER for one month.

Editorial illustration for SORTN to get the top 5 expenses in Google Sheets

Use =SORTN(A2:D500,5,0,4,FALSE) to return the five largest expenses from a table whose amounts are in column D. The arguments are the range, how many rows, the ties mode, the column to sort by and FALSE for descending order. Put FILTER inside it to limit the list to one month.

What does each SORTN argument mean?

PositionArgumentIn the formulaMeaning
1rangeA2:D500The rows to rank, without the header
2n5How many rows to return
3display_ties_mode0How ties at the cut-off are shown
4sort_column4Which column of the range to sort by; 4 is column D here
5is_ascendingFALSEFALSE puts the biggest first

Note that the sort column is counted inside the range, not by the sheet's column letters. If your range started at column B, the amount would be column 3, not 4.

How does SORTN handle ties?

This is the argument people skip. Say your fifth and sixth biggest expenses are both ₹4,200. With mode 0, SORTN returns exactly five rows and quietly drops one of the two. With mode 1, it returns the five plus any rows tied with the last one, so you would see six rows. The other modes deal with removing duplicate rows; check the Sheets help for how they behave before using them.

  • =SORTN(A2:D500,5,0,4,FALSE): exactly five rows
  • =SORTN(A2:D500,5,1,4,FALSE): five rows, plus any ties for last place

For a household list, mode 1 is more honest: if two bills are equal, you want to see both.

How do I get the top five for one month?

SORTN accepts any range, including one produced by FILTER. With month in H1 and year in H2:

=IFNA(SORTN(FILTER(A2:D500,(MONTH(A2:A500)=H1)*(YEAR(A2:A500)=H2)),5,1,4,FALSE),"No expenses")

FILTER narrows the table to the chosen month, SORTN ranks what is left, and IFNA covers an empty month. One formula, one list.

What if I want the top five categories?

SORTN ranks rows as they exist, so it ranks single transactions. For totals per category, build the totals first, with a unique list and SUMIF, or a QUERY with group by, and apply SORTN to that block. Say the summary sits in F2:G12, categories in F and totals in G: =SORTN(F2:G12,5,1,2,FALSE).

Problems and quick fixes

  • Header row appears in the results: your range starts at row 1; start at row 2
  • Wrong order: check is_ascending; TRUE gives smallest first
  • Amounts treated as text: they sort alphabetically, so ₹900 appears above ₹10,000; convert them with VALUE
  • Unexpected rows: blank rows can sort to the top or bottom depending on the data, so filter blanks out with D2:D500<>"" inside FILTER

A worked example with hypothetical numbers

Say a month has these amounts in D: 18,000 rent, 6,400 school fees, 4,200 phone and broadband, 4,200 electricity, 3,100 groceries, 1,950 fuel. Sorted descending, the order is 18,000, 6,400, 4,200, 4,200, 3,100, 1,950. With n of 5 and mode 0, you get through 3,100. With mode 1 and n of 3, you get rent, school fees and both 4,200 rows, because the two 4,200 amounts tie for third place.

Setting it up

  1. Keep a clean table with a header row and real numbers
  2. Decide the number of rows and the ties mode
  3. Add FILTER for the month you want
  4. Wrap in IFNA
  5. Leave space under the formula so it can spill

Once you track money in SpendixAI, the dashboard and insights give you the larger picture, so a sheet like this is best for the quick questions the app does not answer.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports