Excel Sheet for Expense Tracking: How to Build One

How-to basics · Updated November 22, 2026

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:

  1. Drag Category to the “Rows” area.
  2. Drag Amount to the “Values” area. Excel will default to “Sum of Amount”.
  3. 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

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.