A personal expense tracker gives you one place to record purchases, organize them by category, and see where your money went during the month. You do not need a budgeting app or a connection to your bank account to build one.
In this project, you will create a straightforward Microsoft Excel workbook with a transaction list, category controls, a monthly summary, and a budget comparison. The downloadable workbook includes made-up practice data so you can inspect the setup before replacing it with your own information.
By the end, you will be able to answer three basic questions: How much did I spend? Which categories accounted for most of it? Did the summary match the underlying transactions?
What the finished expense tracker includes
The workbook has three main working areas:
- Transactions: one row for each purchase or payment.
- Lists: the approved spending categories used by the category drop-down.
- Summary: category totals, budget amounts, differences, and a spending chart.
The example contains 12 practice transactions for October 2026. Together, they total $2,321.10 across Housing, Groceries, Transportation, Utilities, and Personal spending.
Use the accompanying Personal Expense Tracker workbook to follow the project, inspect the formulas, and try the included examples. The workbook also contains instructions, documented tests, a failure-and-repair example, and a version history.
Workbook note: The file uses made-up practice data that contains no real personal information. Make a separate copy before entering your own transactions. Review the formulas, security settings, and compatibility before using the workbook for real financial decisions.
Start with the transaction table
Open the Transactions sheet. Each row represents one transaction, and each column answers a specific question about it.
| Column | What to enter |
|---|---|
| Transaction ID | A unique label such as T001, T002, or T003. |
| Date | The date the purchase or payment occurred. |
| Description | A short name such as Rent, Market, Fuel, or Internet. |
| Category | The spending group selected from the drop-down list. |
| Amount | The transaction amount using one consistent sign convention. |
| Account | The account used, such as Checking or Card. |
| Notes | Optional context that will help you understand the entry later. |
Keep one transaction per row. Do not add category totals or monthly summaries inside the transaction table. Separating the source entries from the summary makes the workbook easier to check and less likely to produce accidental double-counting.
Create a category list
The practice workbook uses these categories:
- Housing
- Groceries
- Transportation
- Utilities
- Personal
- Uncategorized
The category column uses data validation, an Excel rule that limits what someone can enter in a cell. Selecting categories from a drop-down prevents small spelling differences such as “Grocery,” “Groceries,” and “grocery” from creating separate totals.
Keep Uncategorized as an available choice. It is better to leave a transaction visibly unfinished than to force it into the wrong category or hide it inside a broad Other category.
Build the monthly summary
The Summary sheet adds the transactions in each category. The workbook uses SUMIFS, an Excel function that adds values that meet one or more conditions.
The category formula follows this pattern:
=SUMIFS(Transactions[Amount],Transactions[Category],A6)
This tells Excel to add values from the Amount column when the transaction category matches the category listed in the summary row.
The October practice transactions produce these results:
| Category | Actual spending | Budget | Budget minus actual |
|---|---|---|---|
| Housing | $1,450.00 | $1,500.00 | $50.00 |
| Groceries | $412.35 | $450.00 | $37.65 |
| Transportation | $188.40 | $220.00 | $31.60 |
| Utilities | $176.25 | $190.00 | $13.75 |
| Personal | $94.10 | $100.00 | $5.90 |
A positive result in the final column means money remains in that category. A negative result means actual spending exceeded the category budget. Keep that sign convention visible so the numbers are not misread.
Check that the totals match
The most important accuracy check is simple: the total of the transaction table should equal the total of the category summary.
- Add all 12 amounts in the Transactions table.
- Add the category totals on the Summary sheet.
- Subtract the summary total from the transaction total.
- Confirm that the difference is $0.00.
In the practice workbook, both totals equal $2,321.10. If the difference is not zero, check for:
- An amount stored as text instead of a number
- A category typed outside the approved list
- A date or transaction accidentally left outside the intended month
- A summary formula that does not include every category
- A row added outside the Excel Table
Review entries that need attention
Before relying on the summary, look for entries that may need correction:
- Blank or Uncategorized categories
- Duplicate transaction IDs
- Dates stored as text
- Zero or negative amounts that do not match your chosen convention
- Unusually large transactions
- Transactions assigned to the wrong account or month
The workbook is a learning example rather than a connected financial application. It does not import bank activity, identify fraud, calculate taxes, or replace professional financial advice.
Try five useful checks
You do not need formal testing experience. Use a few practical checks to make sure the tracker behaves as expected.
| Check | What to do | Expected result |
|---|---|---|
| Complete month | Load the 12 practice transactions. | The total is $2,321.10. |
| First and last date | Enter transactions on the first and final day of the month. | Both appear in the correct monthly total. |
| Incorrect category | Try to enter a category that is not on the approved list. | Excel blocks or warns about the entry. |
| Total match | Compare the transaction total with the category summary. | The difference is $0.00. |
| Formula recovery | In a copy, replace one summary formula and then restore it. | The correct total returns after the formula is restored. |
Customize the tracker for your own use
Once you understand the practice workbook, make a copy and adjust it carefully.
- Replace the practice transactions with your own entries.
- Change category names to fit your spending.
- Update the category budgets.
- Add accounts such as Checking, Credit Card, or Cash.
- Keep an Uncategorized option for entries that still need review.
- Recheck the totals after adding categories or changing formulas.
If you add a new category, add it to the approved list and create a corresponding row in the summary. If you add many new transactions, confirm that they are inside the Transactions table and included in the formulas and chart.
Common mistakes to avoid
- Mixing entries and summaries: Keep transaction data separate from category totals.
- Using inconsistent signs: Decide whether spending is entered as positive or negative and use that approach throughout.
- Hiding unfinished entries: Leave questionable transactions visible as Uncategorized.
- Trusting a chart before the totals: Confirm the numbers first. A polished chart can still be based on incorrect data.
- Editing formulas without a backup: Make a copy before changing calculation cells.
- Entering sensitive information: Avoid account numbers, passwords, card numbers, or other information the tracker does not need.
What this project helps you practice
This project gives you practical experience with Excel Tables, category drop-downs, SUMIFS, budget comparisons, basic charts, and total checks. More importantly, it shows how a small spreadsheet can turn a list of transactions into information you can review and use.
Continue the project workflow
- Clean inconsistent transaction data when dates, categories, or descriptions need standardization.
- Turn the tracker into a monthly report when you want a repeatable review process.
- Present the expense tracker as a spreadsheet project if you want to show the decisions, formulas, and checks behind the workbook.