Excel Template for Personal Expense Tracking

How-to basics · Updated November 22, 2026

Yes, you can build a personal expense tracker in Excel in under 10 minutes using a simple table, a few SUMIF formulas, and a pivot table — no add-ins or coding required. The core template is a dated log of transactions (date, category, description, amount) plus a summary sheet that totals spending by category and month.

Why Excel Still Works for Budgeting

Dedicated apps like Mint or YNAB automate bank syncing, but Excel gives you full control over categories, formulas, and privacy. You never share your bank credentials with a third party, and you can customize every column, chart, and calculation. For many people, the act of manually entering each expense creates more awareness than automatic imports. Excel also works offline, has no subscription fee (if you already own Office), and stores your data as a normal file you can back up to OneDrive, Google Drive, or a USB stick.

Core Structure of a Basic Tracker

Start with a worksheet named Transactions. Use these columns in row 1:

  • A: Date (enter as a real date, e.g., 2025-01-15)
  • B: Category (e.g., Groceries, Rent, Utilities, Dining Out)
  • C: Description (e.g., “Weekly supermarket run”)
  • D: Amount (positive for spending, negative for income or refunds)
  • E: Payment Method (optional: Cash, Credit Card, Debit)

Format column A as a date, column D as currency. Then create a second sheet called Summary. In A2:A20, list your categories. In B1, put the month number (e.g., 1 for January). In B2, enter this formula:

=SUMIFS(Transactions!$D$2:$D$1000, Transactions!$A$2:$A$1000, ">="&DATE(2025,B$1,1), Transactions!$A$2:$A$1000, "<"&DATE(2025,B$1+1,1), Transactions!$B$2:$B$1000, $A2)

Assumes dates are in Transactions!A2:A1000, categories in B2:B1000, amounts in D2:D1000, and the year is 2025. Drag this formula down for all categories. It returns the total spending for each category in January 2025. For a full year, copy the formula across columns C, D, etc., changing the month number in row 1.

Better: Use a Pivot Table for Automatic Summaries

If you prefer not to write SUMIFS, create a pivot table from your transaction range:

  1. Click anywhere in the Transactions table (Ctrl+T to make it an Excel Table).
  2. Go to Insert > PivotTable and choose New Worksheet.
  3. Drag Category to Rows, Amount to Values (it will default to Sum of Amount), and Date to Filters.
  4. Group the Date field by month: right-click any date in the pivot table, choose Group, then select Months and Years.

This gives you a dynamic report that updates as you add rows. To refresh after new entries, right-click the pivot and choose Refresh, or set the workbook to refresh on open (Data > Queries & Connections > Properties).

How to Choose a Template (or Build Your Own)

You have three main options. First, use a built-in Excel template: open Excel, search “personal budget” in the template gallery, and you’ll find several pre-made trackers with charts. Second, download a free template from sites like Vertex42 or Microsoft Create — these are usually well-tested and include sample data. Third, build your own as described above. For most people, building your own is best because you can align categories with your actual spending habits. If you have irregular income or multiple accounts, add a column for Account or split transactions into separate sheets.

Common Problems and Fixes

New users often hit these issues:

  • Dates not recognized: Enter dates as MM/DD/YYYY or use the Date picker (Ctrl+; inserts today’s date). Avoid text like “Jan 15” without a year.
  • SUMIFS returns 0: Check that your date column is truly dates, not text. Use ISNUMBER(A2) to test. Also ensure the category in the summary matches exactly (no extra spaces).
  • Negative amounts for refunds: Enter refunds as negative numbers, so the sum nets correctly. If you prefer positive entries, add a column called Type (Income/Expense) and use SUMIFS with a condition on that column.
  • Pivot table not updating: Convert your range to an Excel Table (Ctrl+T) so the pivot automatically picks up new rows. Then refresh.
  • Accidentally deleting formulas: Protect the Summary sheet: Review > Protect Sheet, and uncheck “Select locked cells” after locking the formula cells.

Comparing Excel vs. Google Sheets vs. Budget Apps

Excel (desktop) is the most powerful for complex formulas and offline use. Google Sheets is free and easier to share with a partner — you can both edit in real time, and it has the same SUMIFS and pivot features. However, Google Sheets lacks some advanced functions like XLOOKUP (it has VLOOKUP) and its pivot grouping is less flexible. Budget apps (YNAB, Mint, EveryDollar) automate transaction imports but charge a subscription (YNAB is about $99/year) and force you into their budgeting method. A spreadsheet gives you total freedom but requires manual entry or bank CSV downloads. If you want automatic categorization, you can download a CSV from your bank and paste it into your Excel sheet, then use a lookup table to assign categories.

Costs and Safety Considerations

Excel itself is part of Microsoft 365 (about $70/year or $6.99/month) or you can use the free Excel for the web. Free templates are fine, but be careful downloading from random websites — scan files with antivirus and avoid macros unless you trust the source. When you store your budget file, keep it in a password-protected folder or use Excel’s File > Info > Protect Workbook > Encrypt with Password. Never put your full bank account numbers in the description column; use a nickname like “Chase Checking” instead. If you use bank CSV imports, delete the file after importing or store it in an encrypted location.

Frequently Asked Questions

How do I track cash expenses? Keep a small notebook or a note on your phone, then enter them weekly. Or create a “Cash” account in your tracker and record every cash withdrawal as an expense, then track what you actually spend from that cash envelope.

Can I link Excel to my bank automatically? Not directly. Excel has a built-in “Get & Transform” (Power Query) feature that can connect to some banks via web services, but most banks don’t offer a simple API. The practical method is to download a CSV statement and import it with Data > From Text/CSV.

What if I have multiple income sources? Add a column called “Income/Expense” and use two separate SUMIFS formulas — one for income (positive amounts) and one for expenses (negative amounts). Or keep income on a separate sheet and subtract total expenses from total income.

How do I track monthly recurring bills vs. variable spending? Create a separate sheet for “Fixed Bills” listing each bill and its due date, then use a formula to flag if it’s paid this month. Alternatively, just tag categories like Rent, Utilities, and Subscriptions as “Fixed” in a helper column.

Is there a way to see a chart of spending by category? Yes. After creating a pivot table, select it and go to Insert > Charts > Pie or Bar. Or use a SUMIFS summary and insert a pie chart from that range. The chart will update when you refresh the pivot or recalculate formulas.

Can I use this on my phone? Yes, if you save the file to OneDrive and open it in the Excel mobile app. You can enter transactions on the go, but pivot tables and complex formulas work better on a desktop. For mobile-first tracking, consider Google Sheets which has a better phone interface.

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.