Yes, you can build a fully functional expense tracker in Microsoft Excel using a simple table, the SUMIF function, and a pivot table — no add-ins or coding required. This article walks you through creating a reusable tracker, choosing the right template, and avoiding common mistakes, with exact formulas you can copy into your own workbook.
Why Use Excel for Expense Tracking
Excel is a flexible, offline, one-time-cost tool that works on Windows, Mac, and the web. Unlike budgeting apps that sync to your bank and charge monthly fees, Excel gives you full control over categories, custom formulas, and charts. You can start with a blank sheet or download a free template from Microsoft (File > New, search “expense tracker”). Because it’s a spreadsheet, you can also track business expenses, project costs, or a household budget in the same file.
Core Components of a Basic Expense Tracker
Every good tracker needs the same minimum columns. Set them up in row 1 of a sheet named “Log”:
- Date (column A) – enter as a real date, e.g.,
2025-03-14, so you can filter by month. - Category (column B) – e.g., Groceries, Rent, Utilities, Dining, Transport.
- Description (column C) – optional but useful for recalling what the purchase was.
- Amount (column D) – always positive numbers; use a negative sign only for refunds.
- Payment Method (column E) – cash, credit card, debit, etc. (optional).
Add a “Notes” column if you need it. Once you have 10–20 rows, you can start summarizing.
Key Formulas to Automate Totals
Assume your data is in rows 2 to 100. With categories in B2:B100 and amounts in D2:D100, here are the essential formulas:
- Total spent:
=SUM(D2:D100)– returns the sum of all amounts. - Total for one category:
=SUMIF(B2:B100,"Groceries",D2:D100)– returns the sum of all amounts where column B equals “Groceries”. - Monthly total (if dates are in A2:A100):
=SUMPRODUCT((MONTH(A2:A100)=3)*(YEAR(A2:A100)=2025)*D2:D100)– returns total for March 2025. This works in all Excel versions. - Average per month (if you have 12 monthly totals in M1:M12):
=AVERAGE(M1:M12)– returns the arithmetic mean.
For a running balance (income minus expenses), add an “Income” column or use a negative sign for expenses. For example, with income in E2:E100 and expenses in D2:D100, net cash flow is =SUM(E2:E100)-SUM(D2:D100).
How to Build a Pivot Table for Category Breakdown
Pivot tables are the fastest way to see spending by category without writing formulas. Select any cell inside your data range (e.g., A1:E100), then go to Insert > PivotTable. In the dialog, choose “New Worksheet” and click OK. In the PivotTable Fields pane:
- Drag Category to the “Rows” area.
- Drag Amount to the “Values” area. Excel will default to “Sum of Amount”.
- Optional: drag Date to the “Filters” area to filter by month.
This gives you a clean table showing each category’s total. To refresh after adding new rows, right-click the pivot table and choose Refresh. On Mac, the steps are the same, but the menu is Insert > PivotTable as well.
Choosing Between a Template and a Custom Sheet
Microsoft’s built-in “Expense Tracker” template (File > New, search “expense tracker”) is a good starting point because it already has formatting and a chart. However, it may include extra columns you don’t need. Building your own sheet gives you full control and helps you understand the formulas. If you want a simple monthly budget, a custom sheet with just Date, Category, Amount, and a SUMIF table is usually enough. For more complex needs (e.g., tracking reimbursable business expenses), you might add a “Reimbursable” column and a SUMIFS formula like =SUMIFS(D2:D100,B2:B100,"Travel",E2:E100,"Yes").
Common Mistakes and How to Avoid Them
- Mixing dates and text: Always enter dates as dates (e.g., 3/14/2025), not as text like “March 14”. Otherwise month filters won’t work.
- Using inconsistent category names: “Groceries” and “grocery” are treated as different by SUMIF. Use a data validation list (Data > Data Validation > List) to keep categories consistent.
- Forgetting to update formulas when adding rows: If you use
SUM(D2:D100)and then add row 101, the total won’t include it. Use a full-column reference like=SUM(D:D)instead, but be careful not to place the formula in column D itself (it would create a circular reference). - Not backing up the file: Excel files can corrupt. Save a copy to OneDrive or a USB drive regularly.
Comparing Excel to Dedicated Budgeting Apps
Apps like Mint, YNAB, or EveryDollar automatically import transactions from your bank, which saves manual entry. However, they require ongoing subscriptions (YNAB) or may display ads. Excel is a one-time purchase (or part of Microsoft 365) and works offline. The trade-off is manual data entry, but you gain complete privacy and unlimited customization. For someone who wants to actively review spending, manual entry can actually increase awareness. If you prefer automation, you can export bank statements as CSV and import them into Excel using Data > Get Data > From Text/CSV.
Cost and Safety Considerations
Excel itself is not free unless you use Excel for the web (free with a Microsoft account) or a trial. Microsoft 365 subscription costs around $10/month, but a one-time purchase of Excel 2021 is also available. For safety, never store sensitive bank account numbers in the spreadsheet; use only transaction descriptions. If you share the file, avoid macros (VBA) unless you trust the source, and always keep a backup. When using formulas that reference other sheets, ensure the file is saved in a location that syncs (e.g., OneDrive) to prevent data loss.
Frequently Asked Questions
How do I track expenses by month in Excel?
Add a column for month using the formula =TEXT(A2,"YYYY-MM") (if date is in A2). Then use a pivot table with “Month” in Rows and “Amount” in Values. Alternatively, use SUMPRODUCT with MONTH and YEAR as shown above.
Can I track income and expenses together?
Yes. Add a “Type” column with values “Income” or “Expense”. Use SUMIF to total each: =SUMIF(E2:E100,"Income",D2:D100) and =SUMIF(E2:E100,"Expense",D2:D100). Net is income minus expense.
What is the best free expense tracker template?
Microsoft’s built-in “Expense Tracker” template is reliable and free with Excel. For Google Sheets, there are also free templates in the template gallery. The best one is the simplest one you will actually use.
How do I make a chart of my spending by category?
After creating a pivot table, select it and go to Insert > Chart. Choose a pie chart or bar chart. Excel will update the chart when you refresh the pivot table.
Can I use Excel on my phone to track expenses?
Yes, the Excel mobile app is free for viewing and basic editing. You can enter expenses on the go, but complex formulas are easier on a desktop. The app syncs with OneDrive if you save the file there.
Is it better to use Google Sheets instead?
Google Sheets is free, cloud-based, and easier to share. Excel has more advanced functions (like Power Query) and works offline without a browser. For basic expense tracking, either works. The formulas are mostly the same, but Excel uses commas in arguments while Google Sheets uses commas too, so they are largely compatible.
Related
- Excel Power Query Incremental Refresh: How to Set It Up
- Excel Power Query: Append to Existing Table
- Excel Power Query Background Refresh Explained
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Resolve a Corrupted Excel File Without Losing DataTo resolve a corrupted Excel file, first make a copy of it, then try Excel’s built-in Open and…
- How to Find and Replace in One Excel ColumnTo find and replace text in just one Excel column, select the column by clicking its letter, press…
- Excel Page Break Button Locked? Common Causes and FixesIn Excel for Microsoft 365 on Windows, the Page Break command is usually unavailable because Excel is editing…
- Checklist in Excel: How to Create, Track, and Reuse OneIn Excel for Microsoft 365 on Windows, put your tasks in one column, select the cells beside them,…
- Excel Merge Cells Without Losing Data: Safe MethodsExcel’s Merge & Center command does not combine the contents of two cells: it keeps the upper-left cell’s…
- Remove a Section Break in Word for the Web: Step-by-StepTo remove a section break in Word for the web, show formatting marks, place the cursor immediately before…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.