In Google Sheets, select your expense data, choose Insert, then Pivot table, and put Category in Rows and Amount in Values, summarised by SUM. Turn on Show totals, then sort by Sum of Amount in descending order, and you have a category summary that updates whenever the data changes. The sample here uses hypothetical numbers.
How do I create the pivot table?
- Make sure row 1 holds clear headers such as Date, Merchant, Category, Amount, Mode.
- Click any cell in the data, or select the range, then choose Insert, Pivot table.
- In the dialog, check the Data range. Choose New sheet, which keeps the pivot away from your data, and press Create.
- The editor panel opens on the right. Next to Rows click Add, and pick Category.
- Next to Values click Add and pick Amount. Check Summarise by shows SUM.
- Next to Columns click Add if you want a second dimension, such as Mode for UPI, Card and Cash.
What do rows, columns and values do?
| Panel | What you place there | Example |
|---|---|---|
| Rows | The things you want listed down the side | Category |
| Columns | A second split across the top | Payment mode |
| Values | The numbers to add up | Sum of Amount |
| Filters | A field used to narrow the whole pivot | Month or Account |
Say Groceries is 4,500 by UPI and 1,800 by card, and Fuel is 3,200 by card. With Mode in Columns, Groceries shows 4,500 under UPI and 1,800 under Card, and its grand total is 6,300. Fuel shows only 3,200 under Card.
How do I show totals and sort the result?
Under Rows there is a Show totals checkbox, and another under Columns. Tick them to add grand totals. To sort, use the Sort by dropdown under Rows and choose Sum of Amount, then set Order to Descending. The largest category now sits at the top, which is usually what you want when asking where the money went.
- Order lists Ascending or Descending.
- Sort by lets you choose the category name itself or any value column.
- Show totals can be switched off for a cleaner table.
- If you add Columns, you can also sort the column headings.
Will it pick up new rows?
Sheets pivot tables update automatically when the data inside the source range changes, with no refresh button. The catch is the range itself. If you chose A1:E200 and add row 201, it is not counted. Either edit the Data range in the editor, or select whole columns such as A:E when you create the pivot, so new rows are always inside it. Whole-column ranges include the empty cells below, and Sheets treats a blank Category as a (blank) row, which you can hide using a filter.
What goes wrong most often?
- Dates or amounts stored as text. Amounts with a rupee sign typed in front are text and will not add up; format as number and remove the symbol.
- Duplicate headers, which make the editor show confusing field names.
- Category spelled two ways, giving two rows.
- A blank row inside the data, which can cut the range short.
- Merged cells in the data region.
What next?
Once the category summary works, add Month as Columns using a pivot date group, or build a chart from the pivot. When a sheet starts to mean typing in every payment, an importer saves time: SpendixAI accepts statements in PDF, CSV or Excel, and you review rows before they are saved. It stays optional; a pivot is a good way to learn what your spending looks like.
- Headers in row 1 and one record per row.
- Pivot on a new sheet, Rows set to Category, Values set to SUM of Amount.
- Show totals on, sorted by Sum of Amount, descending.
- Compare the grand total with =SUM of the Amount column.
Useful ideas start with a clearer picture of your own spending.
Explore your reports


