Spreadsheets

Year-end expense summary in Excel: pivot table by month and category

Build a year-end expense summary in Excel with a pivot table by month and category, plus a top-merchants pivot, with the matching steps for Google Sheets.

Editorial illustration for Year-end expense summary in Excel: pivot table by month and category

To summarise a year of spending in Excel, turn your transaction list into a Table, add a column that numbers the months from April, insert a PivotTable with Category in Rows, month number in Columns and Sum of Amount in Values. You get a category-by-month grid with a yearly total in a few clicks, and a second pivot on Merchant gives the top spenders.

What should the transaction list look like?

The pivot is only as good as the list. You need one row per transaction and a header in row 1, with no blank rows, merged cells or subtotal lines inside the data. Columns: Date, Merchant, Category, Amount, Account. Dates must be real dates, and every row must have a category; type Uncategorised instead of leaving cells blank so they appear in the pivot rather than hiding.

Press Ctrl+T with a cell in the list selected to make it a Table. A Table grows when you add rows and the pivot can be refreshed to include them.

How do I get months in April-to-March order?

Month names sort alphabetically unless you group them, and calendar order puts January first. For a financial year, add a helper column F called FY Month with:

=MOD(MONTH([@Date])-4,12)+1

April returns 1, May returns 2, January returns 10 and March returns 12. Add another column G called FY Month Name with =TEXT([@Date],"mmm") if you want a label beside the number. In the pivot, use the number so the columns sort 1 to 12.

How do I build the pivot step by step?

  1. Click any cell in the Table and choose Insert > PivotTable. Place it on a new sheet.
  2. Drag Category into Rows.
  3. Drag FY Month into Columns.
  4. Drag Amount into Values. Check it says Sum of Amount, not Count of Amount; if it says Count, click it, choose Value Field Settings and pick Sum.
  5. Under PivotTable Design > Grand Totals, switch on totals for rows and columns.
  6. Right-click any number, choose Number Format, and pick Number with a thousands separator.

If the year matters, add a Financial Year column and put it in Filters, so two years never mix. The grand total in the bottom-right cell should equal the sum of the Amount column for the same year. If it does not, rows are missing a date or contain text instead of numbers.

How do I list the top merchants?

Build a second pivot on a new sheet. Put Merchant in Rows and Sum of Amount in Values. Click the drop-down on Row Labels, choose Value Filters > Top 10, and change 10 to 5. The pivot now shows the five merchants you paid most across the year, which is often a surprise. Two spellings of one shop split the total, so tidy names in the Table first with Find and Replace.

What are the steps in Google Sheets?

Select the data range, choose Insert > Pivot table, and place it in a new sheet. In the editor, add Category under Rows, FY Month under Columns and Amount under Values, summarised by SUM. Sheets updates the pivot as the data changes, so there is no refresh step. For the top merchants, add Merchant to Rows, set Sort by to SUM of Amount, descending, and read the first five rows.

A small example to test against

Say a three-month extract shows Groceries at ₹14,000, ₹15,500 and ₹13,200 in months 1 to 3, and Travel at ₹0, ₹6,800 and ₹2,400. The Groceries row total is 14,000 + 15,500 + 13,200 = ₹42,700, the Travel row total is ₹9,200, and the grand total for the two categories is ₹51,900. A grand total that matches your own addition is how you know the pivot is reading everything.

If your data starts life as bank statements, SpendixAI can import them and give you reports by month and category, with a CSV download you can drop into the same pivot.

Year-end checklist

  • Filter to one financial year only.
  • Search the pivot for Uncategorised and fix those rows.
  • Check the grand total against the sum of the Table's Amount column.
  • Save the pivot sheet as a PDF for your records.
SPENDIXAI JOURNAL

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

Explore your reports