To chart your top 10 merchants, build a pivot table with Merchant in Rows and Sum of Amount in Values, apply a Top 10 value filter, sort largest to smallest, and insert a clustered bar chart. Clean up the merchant names first, because one shop spelled three ways can drop out of the ranking.
How do I build the pivot with a Top 10 filter?
- Make sure your statement data has a Merchant column and an Amount column, and that it is a table (Ctrl+T).
- Insert, PivotTable. Drag Merchant to Rows and Amount to Values (Sum).
- Click the drop-down arrow beside Row Labels, choose Value Filters, then Top 10.
- Set it to Top 10 Items by Sum of Amount and click OK.
- Right-click any value, choose Sort, then Sort Largest to Smallest.
The word "Top" in that dialog is not fixed at 10; type 5 or 15 if you prefer. Use only debits in the Amount column; if credits are mixed in, filter them out or the totals will net off.
How do I turn it into a sorted bar chart?
- With the pivot selected choose Insert, PivotChart, Clustered Bar.
- Excel puts the largest bar at the bottom, so open Format Axis on the merchant axis and tick Categories in reverse order.
- Delete the legend and the field buttons for a cleaner look, and add data labels.
- Format the label numbers with commas, using Indian grouping if your system supports it.
Why do merchant names change the ranking?
Statements often write one shop several ways. A hypothetical grocery delivery service might appear as FRESHCART STORE, Freshcart Store Bengaluru and FRESHCART*ORDER. Each spelling gets its own row in the pivot. Say they total ₹2,100, ₹1,900 and ₹1,600. Combined that is ₹5,600, but a different shop with a single ₹3,000 spend beats every individual spelling and takes a place in the top 10, while the real number one is split across three rows.
| Name in statement | Spend (₹) | Rank before cleaning |
|---|---|---|
| Another shop | 3,000 | 1 |
| FRESHCART STORE | 2,100 | 2 |
| Freshcart Store Bengaluru | 1,900 | 3 |
| FRESHCART*ORDER | 1,600 | 4 |
Does the ranking by total hide frequent small spends?
It can. Ranking by Sum of Amount favours one big purchase over habits. A hypothetical corner shop where you spend ₹150 about 30 times a month totals ₹4,500, while a single ₹9,000 appliance purchase from another shop outranks it. Drag Amount into Values a second time and set it to Count so each row also shows how many transactions it holds. Big one-off items and everyday habits answer different questions, so read both columns.
How can I standardise names in Excel?
Create an Aliases sheet with a keyword in column A and the clean name in column B, such as freshcart and Freshcart. In a new Merchant (clean) column use, in Excel 365: =IFERROR(INDEX(Aliases!$B$2:$B$100, MATCH(TRUE, ISNUMBER(SEARCH(Aliases!$A$2:$A$100, B2)), 0)), B2). SEARCH finds each keyword inside the statement text, MATCH returns the first hit, and the original text is kept if nothing matches. In older Excel this must be confirmed with Ctrl+Shift+Enter. Build the pivot on the clean column.
Do I need to do this by hand every month?
The alias list only grows, so the work shrinks after the first month. If you would rather not maintain it, SpendixAI standardises merchant names from uploaded statements, lets you add merchant aliases, and treats your corrections as history for later imports. The Excel route above gives you the same chart with full control.
Checklist
- Pick the date range before building the pivot.
- Exclude refunds, transfers between your own accounts and credit card bill payments, or they will dominate the chart.
- Clean names, refresh the pivot, and re-read the top 10.
- Label the chart with the period and the unit.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


