A monthly expense tracker spreadsheet records what you spend, groups transactions into categories, and totals them for a selected month. In Excel for Microsoft 365, set up a transaction table, enter a month’s start and end dates, and use SUMIFS to calculate monthly and category totals.
What a monthly expense tracker does
An expense tracker is a record of money going out. It can help answer practical questions: How much did groceries cost this month? Which bills are recurring? Did total spending change from last month? Unlike a bank statement, a tracker lets you sort spending into categories you choose and compare it with a budget.
A spreadsheet is useful when you want control over the categories, calculations, and layout. It is not automatically connected to your bank, so you decide what to enter and when. That can improve awareness, but it also means the totals are only as complete as the transactions you record.
Keep spending separate from income unless you specifically want a cash-flow workbook. If you do include income, label it clearly and calculate it separately; otherwise, a positive expense total can be mistaken for money left over.
Choose a simple transaction layout
Use one row per transaction, with one header row and consistent columns. For example, set up columns A through E like this:
- Date: the date the purchase or payment cleared, or another date you use consistently.
- Category: a broad group such as Housing, Groceries, Transport, or Dining.
- Description: a short label, such as “electric bill” or “weekly shop.”
- Amount: the expense as a positive number, entered in the same currency.
- Payment method: optional, such as debit card, cash, or credit card.
For a concrete example, imagine these January entries: rent, $1,200.00; groceries, $82.40; transport, $35.00; utilities, $94.60; and dining, $48.00. Their total is $1,460.00. The amounts are illustrative, not a suggested budget or a statement of typical costs.
Use positive numbers for expenses if you want totals to read naturally. Record refunds as negative amounts in the same category, or enter them in a separate refunds column and subtract them in a report. Choose one method and apply it consistently. Mixing positive and negative conventions can make a correct-looking total misleading.
Build the tracker in Excel
On a blank worksheet, enter the five headers in row 1 and transaction records below them. Select the range and press Ctrl+T on Windows to turn it into an Excel table; confirm that “My table has headers” is selected. On a Mac, Command+T opens the Create Table dialog in Excel. A table expands as you add rows and makes filtering easier. Give it a useful name through Table Design > Table Name, such as Expenses.
Format the Date column as a date and Amount as currency using Home > Number. Avoid typing currency symbols into individual cells: store numeric amounts and apply a number format, so Excel can add them. Use a single currency per tracker, or add a currency column and convert values consistently before combining them. Simply adding dollars, euros, or other currencies together does not produce a meaningful total.
Set aside a small summary area. Put the first day of the month you want to review in F2, and label it “Month start.” For example, enter January 1, 2026 as a real date. Put “Next month” in F3 and use the following formula to calculate the first day of the next month:
With the selected month’s first day in F2, enter this in F3: =EDATE(F2,1). If F2 is January 1, 2026, the result is February 1, 2026.
Using a start date and an exclusive next-month date avoids manually guessing how many days are in a month. It also handles February and leap years without changing the formula.
Calculate monthly and category totals
Put a “Monthly total” label in F4. The following formula assumes transaction dates are in A2:A500, amounts are in D2:D500, and the month boundaries are in F2 and F3:
With those ranges and dates, enter this in F5: =SUMIFS($D$2:$D$500,$A$2:$A$500,">="&$F$2,$A$2:$A$500,"<"&$F$3). For the five illustrative January transactions above, the result is $1,460.00.
SUMIFS adds amounts only when the date is on or after the month start and before the next month. The end date is exclusive, so a transaction on February 1 is not accidentally included in January. If you expect more than 499 transactions, extend the ranges or use an Excel table and structured references.
To see a category total, list a category such as Groceries in G2 and place a total beside it. This formula assumes the same transaction columns and month dates, with the category to match in G2:
With the category name in G2, enter this in H2: =SUMIFS($D$2:$D$500,$B$2:$B$500,G2,$A$2:$A$500,">="&$F$2,$A$2:$A$500,"<"&$F$3). For the sample data, if G2 contains Groceries, H2 returns $82.40.
Copy the category name and formula down for other categories. Exact matching means that “Dining” and “Dining out” are separate labels; spelling and spacing need to be consistent. If you want a budget comparison, enter the planned category amount in another column and subtract actual spending from it. Treat that result as a tracking comparison, not a recommendation about how much anyone should spend.
Make categories and data entry consistent
Categories work best when they are broad enough to reuse and specific enough to answer your questions. A starter list might include Housing, Utilities, Groceries, Dining, Transport, Health, Personal, Subscriptions, and Other. You do not need a category for every individual shop. Too many labels make data entry slow and leave you with categories that have only one transaction.
For consistent labels, place your category list in an unused area or on a separate worksheet. Select the cells where categories will be entered, then choose Data > Data Validation and set Allow to List. Set the source to the category range. This reduces accidental variations, though you should still review imported or pasted records for unexpected labels.
Decide how to handle expenses that could fit multiple categories. For example, a supermarket receipt containing groceries and household supplies can stay under Groceries for convenience, or be split into two rows if that distinction matters to you. The important point is to choose a rule you can follow each month. A tracker that is easy to maintain is usually more useful than an elaborate one that is often left incomplete.
Create a repeatable monthly routine
A tracker stays useful when updating it is part of a routine. Some people enter purchases as they occur; others reconcile their records weekly against receipts and account activity. Either approach can work. Waiting until the end of the month may save time during the month, but it can make small cash purchases and forgotten subscriptions harder to recover.
- Enter or import transactions, checking that dates and amounts are stored as values rather than text.
- Assign a category and review blank or unfamiliar labels.
- Compare the records with statements or receipts to find missed, duplicated, or reversed transactions.
- Set the month-start date in the summary area and check the monthly and category totals.
- Save a copy or keep a dated archive before changing the structure or clearing old records.
When comparing a tracker with a bank statement, distinguish purchases from credit-card payments. If you recorded each card purchase as an expense, recording the later payment from your checking account as another expense counts the same spending twice. You can either track purchases by card and exclude the card payment from expense totals, or track the payment as a transfer and not separately count the underlying purchases. Make the method explicit in a note.
Useful checks, filters, and visuals
Excel table filters let you view one month or category without deleting other rows. Click a filter arrow in the header row to narrow the displayed records. You can also sort by date to spot gaps in entry or sort by amount to review large transactions. Filtering changes what is shown, not the underlying data or the SUMIFS calculation.
Conditional formatting can flag blank categories or unusually large entries, but set thresholds that make sense for your own records. Select the relevant cells and use Home > Conditional Formatting. Formatting is a visual cue, not a substitute for checking the transaction. For a quick chart, select a small category-summary range and choose Insert > Insert Pie or Doughnut Chart, or use a bar chart to compare category amounts. A chart based on incomplete data will still look polished, so reconcile the records first.
Keep the summary modest: a monthly total, category totals, and perhaps a comparison with a personal target are often enough. More charts and metrics do not make the underlying data more accurate. If a calculation looks wrong, check the date boundaries, category spelling, number formats, and whether the relevant rows fall inside the formula ranges.
Common problems and how to avoid them
- Dates stored as text: A date that looks correct may not be recognized as a date. Apply a date format and check whether sorting places dates in chronological order. Re-enter or convert text dates if necessary.
- Totals omit new records: A fixed range ending at row 500 will not include row 501. Extend the range or use an Excel table designed to expand as records are added.
- Categories split unexpectedly: “Transport,” “transport,” and “Transport ” may not match as intended. Use a validation list and review unusual labels.
- Duplicate spending: Check for repeated imports and avoid counting both individual card purchases and the payment that settles the card balance.
- Refunds inflate spending: Confirm whether refunds are negative amounts or separate entries subtracted from expenses. Do not mix conventions without accounting for them.
- Month totals are off by a day: Use a real first-of-month date and the first day of the next month as the exclusive end boundary.
Protecting a workbook is also a practical concern. Save it in a location you control, restrict access if it contains sensitive descriptions, and avoid including full account numbers or passwords. A spreadsheet is not a secure place to store banking credentials. If you use cloud storage, check its sharing settings before sending a link.
Templates, alternatives, and limits
A blank workbook gives you full control and costs nothing beyond access to Excel, but it takes time to set up and maintain. A downloadable template can save setup time; inspect its formulas and remove fields you do not need before entering private information. A budgeting or banking app may automate transaction imports, but the available features, data handling, and fees vary by provider. Check its current terms and privacy information rather than assuming all apps work the same way.
A spreadsheet does not verify that a purchase is appropriate, predict future expenses, or replace an official statement. It is a personal record that helps organize information. The examples here are for tracking and arithmetic only, not financial, tax, investment, or legal advice. For decisions involving a loan, confirm rates, fees, and repayment terms with the lender; for financial or tax questions, consult a qualified professional.
Frequently Asked Questions
How do I make a monthly expense tracker in Excel?
Put one transaction per row with date, category, description, and amount columns. Add a month-start date and the next month’s first day, then use SUMIFS to total the selected month. Check that transaction dates and amounts are numeric values.
Can Excel automatically total expenses by month?
Yes. SUMIFS can add amounts whose dates fall between a month’s first day and the first day of the next month. The formula will not capture records outside its specified ranges, so extend the ranges or use a table as the list grows.
Should I enter expenses as positive or negative numbers?
For a simple tracker, enter expenses as positive amounts and refunds as negative amounts. This makes ordinary spending totals easier to read. Use a different convention only if you apply it consistently and adjust the calculations accordingly.
How many categories should an expense tracker have?
There is no required number. Start with a manageable set that answers the questions you care about, such as housing, food, transport, and utilities. Add a category only when separating that kind of spending will be useful to you.
Is a spreadsheet better than an expense-tracking app?
It depends on your priorities. A spreadsheet offers flexible categories and visible formulas but requires manual upkeep. An app may import transactions automatically, while offering different costs and privacy terms. Choose based on the amount of automation, control, and recordkeeping you need.
Related
- How to Add and Use Notes in a PowerPoint Presentation
- Excel Not Responding When Printing? Causes and Fixes
- Excel Not Responding When Editing a Cell? Causes and Fixes
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Days Until Due: Formula, Date Math, and Alerts ExplainedTo calculate days until a due date in Excel, put the due date in A2 and enter =A2-TODAY()…
- How to Add Parentheses in Excel Formulas CorrectlyTo add parentheses in an Excel formula, edit the cell, type an opening parenthesis where you want a…
- Excel Conditional Formatting for Dates: Rules and FormulasTo conditionally format dates in Excel, select the date cells and choose Home > Conditional Formatting > Highlight…
- NPV Calculator in Excel: Formula, Example, and TipsTo calculate net present value (NPV) in Excel, enter the discount rate and future cash flows, then use…
- Excel Minutes Between Two Times: Formula and ExamplesIf the start time is in A2 and the end time is in B2, enter =(B2-A2)*1440 to calculate…
- How to Round in Microsoft Excel: Formulas and TipsTo round a number in Excel, use ROUND in a formula: if the value is in A2 and…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.