How to Track Expenses in Excel

The right structure — one row per transaction

The most important decision: store one transaction per row, not monthly totals. Individual transactions let you filter, categorise, and analyse any way you want. Monthly totals lock you in.

Columns to include:
A: Date
B: Description
C: Category (use a dropdown list)
D: Amount
E: Payment method (optional)
F: Notes (optional)

Set up a category dropdown

Create a list of categories on a separate sheet (Groceries, Transport, Rent, Utilities, Entertainment, etc.). Select column C → Data → Data Validation → List → point to your category list. Now every entry uses a consistent category name.

Summarise spending by category

=SUMIF(C:C, "Groceries", D:D)
-- Total spent on groceries

=SUMIFS(D:D, C:C, "Transport", A:A, ">="&DATE(2026,9,1))
-- Transport spending in September 2026

Monthly summary with pivot table

Click anywhere in your transaction data → Insert → PivotTable. Drag:

Category → Rows
Date → Columns (group by month)
Amount → Values (Sum)

Instant monthly breakdown by category. Add a slicer for the year to filter quickly.

Budget vs actual comparison

-- In a summary table:
Category | Budget | Actual | Variance
Groceries| 400    | =SUMIF(C:C,"Groceries",D:D) | =C2-B2

-- Conditional format variance column:
-- Red if actual > budget, green if under

💡 Convert to a Table first

Convert your transaction data to an Excel Table (Ctrl+T) before creating the pivot table. When you add new transactions at the bottom, just refresh the pivot table (right-click → Refresh) and it includes the new rows automatically.

Now practise it for real

Practice these formulas in the Small Business track — 100 exercises covering cash flow, invoicing, and business analysis. Free to start.

Start the Small Business track free →

How to track expensesin Excel