Spreadsheets

Top 10 merchants by spend using a pivot table

Find your top 10 merchants by spend with a pivot table: Value Filters Top 10 in Excel, and a sorted pivot or QUERY formula in Google Sheets.

Editorial illustration for Top 10 merchants by spend using a pivot table

In Excel, put Merchant in Rows and Amount in Values, then open the Row Labels drop-down, choose Value Filters, Top 10, and set it to Top 10 Items by Sum of Amount. Google Sheets has no top-N filter, so sort the pivot by Sum of Amount descending and read the first ten rows, or use a QUERY formula with limit 10.

Why clean merchant names first?

Statement descriptions are messy. The same shop can appear as a long code with a branch number on one day and a shorter name on another, and a pivot treats each spelling as a different merchant. Ten rows that are really three shops will give you a wrong ranking. Add a helper column called Merchant that holds one clean name, for example by typing the standard name beside each distinct description, or with a lookup table. Then pivot on that column.

How do I build it in Excel?

  1. Format the data as a Table with Ctrl+T, with columns Date, Merchant, Category and Amount.
  2. Choose Insert, PivotTable. Drag Merchant to Rows and Amount to Values so it reads Sum of Amount.
  3. Click the drop-down on Row Labels and choose Value Filters, then Top 10.
  4. In the box, set Show Top, enter 10 and Items, and choose By Sum of Amount.
  5. Press OK. The pivot shows ten merchants.
  6. Sort largest first: right-click a number, choose Sort, Sort Largest to Smallest.

If you change 10 to 5, the pivot shows five. In the same dialog, Bottom lets you list the smallest, and Percent shows the merchants that together make up a chosen share of the total.

How do I do it in Google Sheets?

Sheets pivots have no Top 10 filter, so you have two routes.

  • Sorted pivot: Merchant in Rows, Amount in Values as SUM. Under Rows set Sort by to Sum of Amount and Order to Descending. The first ten rows are your top ten; ignore the rest or hide them.
  • QUERY formula: with merchants in column B and amounts in column D, =QUERY($A$1:$E$500,"select B, sum(D) where D > 0 group by B order by sum(D) desc limit 10 label sum(D) 'Total'",1) returns the ten largest merchants with their totals.
  • The where D > 0 part leaves out refunds and negative rows; change it if your sheet records spending as negative numbers.
MerchantVisitsTotal spent (₹)Share of the top 10
Grocery store A1411,80031%
Fuel pump B69,60025%
Online mart C97,30019%
Others (7 merchants)289,50025%

The table above is hypothetical: its totals add to ₹38,200, and 11,800 divided by 38,200 gives about 31 percent. Treat each share as a cue to look closer, not as a verdict.

What should I do with the list?

  • Check the top three against your memory. A surprise often means a mislabelled or duplicated row.
  • Look at the visits column, using Count of Amount, to see whether the total comes from many small purchases or a few big ones.
  • Spot subscriptions that appear every month at the same amount.
  • Note which merchants are cash-like transfers, such as to a friend, and decide whether they belong in the ranking.

How can I avoid cleaning names every month?

The cleaning is the real work. SpendixAI normalises merchant names when it categorises imported statements, lets you add merchant aliases so variants group together, and corrections you make become history for next time. Reports can then be filtered by merchant without a helper column. If you stay in a sheet, keep one lookup table of messy name to clean name and extend it each month.

  • Clean merchant column in place.
  • Top 10 filter or sorted pivot built.
  • Refunds and income excluded.
  • Total of the ten compared with the overall total.
SPENDIXAI JOURNAL

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

Explore your reports