Spreadsheets

Top 5 expenses in Excel using LARGE (works in older versions)

List your top 5 expenses in Excel with LARGE, fetch the merchant and date with INDEX and MATCH, and handle tied amounts. Works in older Excel versions too.

Editorial illustration for Top 5 expenses in Excel using LARGE (works in older versions)

To list your five biggest expenses in Excel, use LARGE(range, k) with k running from 1 to 5, then INDEX and MATCH to bring back the merchant and date for each amount. LARGE exists in every Excel version, so no 365 features are needed. The one catch is two expenses with exactly the same amount, and a small helper column solves that.

What does LARGE do on its own?

LARGE returns the k-th biggest number in a range. Say your log has Date in column A, Merchant in column B and Amount in column C, rows 2 to 500. Then:

  • =LARGE($C$2:$C$500, 1) gives the biggest amount
  • =LARGE($C$2:$C$500, 2) gives the second biggest
  • =LARGE($C$2:$C$500, ROWS($F$2:F2)) fills down as 1, 2, 3, 4, 5 without typing the rank by hand

Set up a small block: ranks 1 to 5 in column E, amounts from LARGE in column F, merchants in column G and dates in column H.

How do you fetch the merchant and date?

LARGE gives only the number, so look up the row where that number sits. In G2:

=INDEX($B$2:$B$500, MATCH(F2, $C$2:$C$500, 0))

And in H2 for the date:

=INDEX($A$2:$A$500, MATCH(F2, $C$2:$C$500, 0))

MATCH with 0 as its last argument looks for an exact match and returns the position of the first one found. Format H as a date. Fill both formulas down to row 6.

What goes wrong when two expenses have the same amount?

MATCH always returns the first matching row. If two payments are both ₹4,200, the second line of your top-five list would show the first one's merchant again. The fix is a helper column that makes every amount unique without changing it noticeably. In D2 enter:

=C2 + ROW()/10000000

Fill it down. Row 2 adds 0.0000002 and row 500 adds 0.00005, far smaller than a paisa, so nothing visible changes. Now use column D in place of column C inside LARGE and MATCH:

  • F2: =LARGE($D$2:$D$500, ROWS($F$2:F2))
  • G2: =INDEX($B$2:$B$500, MATCH(F2, $D$2:$D$500, 0))
  • H2: =INDEX($A$2:$A$500, MATCH(F2, $D$2:$D$500, 0))
  • I2: =ROUND(F2, 2) to show the clean amount

Does the formula give sensible results on sample data?

With made-up data, here is what the block could show after the tie-breaker:

RankAmountMerchantDate
1₹48,500Appliance store12-Aug
2₹18,000Rent01-Sep
3₹4,200Electrician20-Jul
4₹4,200Pharmacy03-Sep
5₹3,950Grocery order29-Aug

Ranks 3 and 4 both show ₹4,200 but name different merchants, because the helper column kept them apart.

How do you restrict the top five to one month?

Put the first day of the month in B1, then use AGGREGATE, which handles arrays without Ctrl+Shift+Enter in older Excel:

=AGGREGATE(14, 6, $D$2:$D$500/(($A$2:$A$500>=$B$1)*($A$2:$A$500<EDATE($B$1,1))), ROWS($F$2:F2))

Function number 14 means LARGE and option 6 means ignore errors. Rows outside the month divide by zero, become errors and are skipped. The INDEX and MATCH lookups stay the same.

Keep the helper column visible while you test, then hide it once the block works. If you later sort the log by date or category, the formulas still work, because they search the whole range instead of relying on row order. That is one advantage of LARGE over simply sorting the Amount column, which would shuffle your entries every time you wanted a different view.

What should you check before relying on the list?

  • Income and refunds should not sit in the same Amount column, or they will crowd the list.
  • Blank rows inside the range are ignored by LARGE, so leave the range wider than your data.
  • Amounts stored as text are skipped silently, so look for numbers that are left-aligned.

Revisit the top five once a month. A single large payment that you had forgotten about often explains more of a heavy month than ten small ones.

SPENDIXAI JOURNAL

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

Explore your reports