Spreadsheets

Top N expenses in Excel 365 with TAKE and SORTBY

List the top N expenses in Excel 365 with TAKE and SORTBY, set N from a cell, and see the LARGE and INDEX method that works in older Excel.

Editorial illustration for Top N expenses in Excel 365 with TAKE and SORTBY

In Excel 365, =TAKE(SORTBY(A2:D500,D2:D500,-1),H1) sorts your rows by amount from biggest to smallest and keeps the first H1 of them. Type any number into H1 and the list resizes. For Excel 2021 and older, which lack TAKE, the LARGE and INDEX method below does the same job.

How do TAKE and SORTBY fit together?

SORTBY(array, by_array, order) sorts a table by another range. The order is 1 for ascending and -1 for descending. TAKE(array, rows) then keeps the first rows of the sorted result. With dates in A, merchant in B, category in C and amount in D:

  • =SORTBY(A2:D500,D2:D500,-1): every expense, biggest first
  • =TAKE(SORTBY(A2:D500,D2:D500,-1),10): the top ten
  • =TAKE(SORTBY(A2:D500,D2:D500,-1),H1): the top N, with N typed in H1
  • =TAKE(SORTBY(A2:D500,D2:D500,1),5): the five smallest

TAKE is newer than most dynamic array functions. It is in Microsoft 365 but not in Excel 2021 or older, so check Help, About if the function name shows #NAME?.

Can I restrict it to one month?

Put FILTER inside, so only the chosen month gets sorted. The sort key must come from the same filtered rows, so name them once with LET. With month in H2 and year in H3:

=LET(m,FILTER(A2:D500,(MONTH(A2:A500)=H2)*(YEAR(A2:A500)=H3)),TAKE(SORTBY(m,INDEX(m,0,4),-1),H1))

LET stores the filtered rows as m. INDEX(m,0,4) picks its fourth column, the amount, as the sort key. TAKE then keeps N rows. If the month has fewer than N rows, TAKE simply returns what exists.

What if my blanks cause trouble?

Empty cells in the amount column sort to the end in either direction, so they do not push real expenses out of the top. But text such as "n/a" in an amount cell can sort strangely. Keep the amount column numeric, and use =COUNT(D2:D500) against =COUNTA(D2:D500) to see whether anything is text.

What is the method for older Excel?

Without TAKE, rank with LARGE and look the row up. In F2, put 1, 2, 3 and so on, down to the N you want. Then:

  1. Amount: =LARGE($D$2:$D$500,F2)
  2. Merchant: =INDEX($B$2:$B$500,MATCH(G2,$D$2:$D$500,0)) where G2 holds the amount
  3. Date: =INDEX($A$2:$A$500,MATCH(G2,$D$2:$D$500,0))

There is one trap: when two expenses share the same amount, MATCH finds the first one both times, so the second tie shows the same merchant twice. A helper column in E, with =D2+ROW()/1000000, makes every amount unique, and you rank and look up on that column instead.

ApproachExcel versionTiesEffort
TAKE with SORTBYMicrosoft 365Handled; each row stays intactOne formula
LARGE with INDEX MATCHAny versionNeeds a helper columnSeveral columns
Sort the data itselfAny versionHandledManual and not live

How do I label each row with its rank?

A numbered column makes the list easier to read and to quote. Beside the spill, in a column to its left or right, use SEQUENCE so the numbers match however many rows come back:

=SEQUENCE(ROWS(F2#))

Here the spilled result starts in F2. If N is 10, you get the numbers 1 to 10 next to the ten biggest expenses. Change N to 3 and the numbers shrink with the list.

Quick checklist

  • Amount column holds numbers only
  • H1 holds a whole number above zero
  • Spill area is clear
  • Credits are excluded if they would crowd out spending
  • The result matches a manual sort on one month

Sheets are good for a quick look. For ongoing views, SpendixAI keeps a dashboard and insights beside your imported transactions, so a top-expense question rarely needs a formula at all.

SPENDIXAI JOURNAL

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

Explore your reports