A useful budget tracker spreadsheet gives every transaction a date, category, and amount, then compares actual spending with a monthly plan. In Excel for Microsoft 365 on Windows, start with a transaction list and a separate summary sheet; use consistent categories, simple formulas, and a brief weekly review to keep the numbers current and meaningful.
Set up the workbook around two jobs
Keep transaction recording separate from monthly decision-making. A practical workbook has one sheet for transactions and one for the budget summary. The transaction sheet is the record of what happened; the summary groups those records so you can see whether spending is tracking above or below the plan. This separation makes it easier to add transactions without accidentally overwriting totals or formulas.
On a sheet named Transactions, use these headings in row 1: Date, Description, Category, Type, and Amount. Put one transaction on each row. Use a second sheet named Budget with categories in column A, planned amounts in column B, and actual amounts in column C. You can add a fourth column for the difference between plan and actual.
Convert the transaction range into an Excel table by selecting it and choosing Insert > Table, then confirm that the table has headers. Tables expand when new rows are added and make filters and sorting easier to use. Keep the headings short and unique. Avoid blank rows inside the data, merged cells, and extra totals between transactions; those can interfere with sorting and formulas.
Choose categories that are useful, not endless
Categories should help answer a real question about your spending. Too few categories hide meaningful differences; too many turn routine entry into a chore. A starter list might include housing, utilities, groceries, dining out, transportation, health, subscriptions, personal spending, debt payments, savings, and other. Adjust it to match your household rather than copying a generic list exactly.
Decide in advance how to classify borderline purchases. For example, groceries bought at a supermarket can go under Groceries, while a prepared meal can go under Dining out, even if both appear on one receipt. If you want to split a receipt, enter two rows with the same date and description but different categories and amounts. Their amounts should add up to the receipt total.
Use one spelling for each category. “Dining out,” “Restaurants,” and “Food away” will be treated as three different labels by a formula. To reduce typing differences, make a category list and apply Data > Data Validation to the Category cells, choosing List and selecting the category range as the source. If the source is on another sheet, Excel may require a named range for reliable use in the validation box. A drop-down list improves consistency, but review new or misspelled labels when you reconcile the month.
Record transactions consistently
Enter expenses as positive amounts and identify them with Type set to Expense. Record income as positive amounts with Type set to Income. This convention avoids mixing signs and makes totals easier to explain. Record refunds consistently too: one option is to enter a refund as a positive amount with Type set to Refund, then decide whether the summary subtracts refunds from spending. Another is to record it as a negative expense. Either approach can work, but document the choice and apply it uniformly.
Use real Excel dates in the Date column, not text that only looks like a date. A date such as 1/5/2026 can be interpreted differently depending on regional settings, so check that Excel recognizes it as a date and display it in a consistent format. Enter the date the transaction posted or occurred, and stick with that choice. For recurring bills, record each charge when it happens rather than entering several future expenses as if they were already paid.
When you import or paste bank activity, check for duplicate rows and confirm that amounts and dates landed in the intended columns. A spreadsheet is only as dependable as its inputs. Reconcile against statements or account activity periodically, particularly after importing data, and investigate differences instead of forcing the sheet to match with an unexplained adjustment.
Make the monthly summary calculate actual spending
For a formula-driven summary, put the first day of the month being reviewed in Budget!G1. For example, enter January 1, 2026, and format the cell to show “January 2026” if you prefer. With transaction dates in Transactions!A2:A1000, categories in C2:C1000, transaction types in D2:D1000, amounts in E2:E1000, the category name in Budget!A2, and the month start date in Budget!G1, enter this formula in Budget!C2 to total that category’s expenses during the month:
=SUMIFS(Transactions!$E$2:$E$1000,Transactions!$C$2:$C$1000,$A2,Transactions!$D$2:$D$1000,"Expense",Transactions!$A$2:$A$1000,">="&$G$1,Transactions!$A$2:$A$1000,"<"&EDATE($G$1,1))
The example assumes Budget!A2 contains Groceries, G1 contains January 1, 2026, and the matching grocery expenses in January are $82 and $46. The formula returns $128. The inclusive start date and exclusive first day of the next month also work for months of different lengths and avoid relying on a guessed last day. Copy the formula down for the other categories. Make sure the amount and criteria ranges cover the same rows; extending them farther is fine if all ranges remain aligned.
With planned amounts in Budget!B2:B10 and actual amounts in C2:C10, calculate the difference for the category in row 2 by entering this in D2:
=B2-C2
If the planned amount is $500 and actual spending is $420, the result is $80 remaining. If actual spending is $560, the result is -$60, meaning spending exceeded the plan by $60. Label the column clearly, such as “Remaining (positive = under plan),” so a negative result is not mistaken for available money.
With planned category values in B2:B10 and actual values in C2:C10, total each column below the categories. For example, enter the following in B11 for the planned total:
=SUM(B2:B10)
With the same layout, enter this in C11 for actual spending:
=SUM(C2:C10)
If the nine planned categories add up to $3,000 and their actual amounts add up to $2,850, these formulas return $3,000 and $2,850 respectively. Check that every category appears once in the summary and that the summary covers all categories used in transactions; otherwise the grand total can omit spending even when each displayed category formula is correct.
Compare the plan with reality fairly
A budget is a working estimate, not a promise that every category will land on its planned amount. Compare each category with its purpose and timing. An annual insurance bill may make one month look unusually expensive even if the cost was expected. You can either show the full bill in the month it is paid or set aside a monthly amount in a separate planned category. The important point is to distinguish actual cash paid from money reserved for a future bill.
Do not treat transfers between your own accounts as new income or spending if the spreadsheet is meant to track household consumption. Likewise, a credit-card purchase and the later payment of that card should not both count as expenses: the purchase is the spending event, while paying the statement settles the balance. Choose a method that avoids double-counting and keep it consistent. These distinctions explain why a tracker may not match a bank balance exactly: it may track spending rather than cash in every account.
If you want a savings-rate indicator, first decide what counts as income and spending, then state the calculation beside the result. With monthly income in G2 and monthly expenses in G3, enter this formula in G4:
=IFERROR((G2-G3)/G2,0)
For illustrative income of $4,000 and expenses of $3,200, the result is 0.2, or 20% when G4 is formatted as a percentage. IFERROR displays zero if income is blank or zero; it does not mean the rate is actually zero in that situation, so label or review the result accordingly. This example is spreadsheet arithmetic, not financial, tax, investment, or legal advice. Check real account terms with the relevant lender or a qualified professional when those terms matter.
Make the sheet easier to read and maintain
Use formatting to make important information visible without turning the workbook into a dashboard of decoration. Format amounts as currency, dates as dates, and rates as percentages. Freeze the transaction headings with View > Freeze Panes > Freeze Top Row so column labels stay visible while you scroll. Use filters on the transaction table to inspect one category, date range, or transaction type at a time.
Conditional formatting can flag categories that are over plan. Select the actual or remaining cells and choose Home > Conditional Formatting > Highlight Cells Rules, then set a rule suited to the values you want to flag. For a direct comparison between two columns, a formula-based rule can be more precise; select the relevant cells and use Home > Conditional Formatting > New Rule. Use restrained colors and include labels or numbers, because color alone may be hard to interpret or inaccessible to some readers.
Protect the reliability of calculations by distinguishing input cells from formula cells. For instance, use one fill color for values you type and another for formulas, and add a note explaining the color key. Keep a clean backup before making major structural changes. If the workbook is shared, agree on category names and who is responsible for entering or reviewing transactions; simultaneous edits do not resolve inconsistent classifications by themselves.
Build a review routine that catches errors
A tracker is useful only if it is updated often enough to guide decisions. A short weekly check is usually easier than reconstructing a whole month at once. During each review, add missing transactions, classify uncategorized items, check imports for duplicates, and compare totals with the account records you use. At month-end, review the largest differences between planned and actual amounts and decide whether they reflect a one-time event, a changed routine, or an unrealistic plan.
Use the review to improve the next plan rather than simply judging the previous one. If a category repeatedly exceeds its estimate, investigate the transactions and adjust the forecast if the spending is expected to continue. If a large amount is unusual, note it rather than raising the regular monthly plan without considering its timing. A brief Notes column or a separate monthly notes area can preserve the reason for a change and make comparisons across months more informative.
Common problems and practical fixes
- Actuals show zero: Check that category names match exactly, the Type cells say Expense, the dates are real Excel dates, and G1 contains the first day of the intended month.
- The total is too high: Look for duplicate imported transactions, credit-card payments counted alongside purchases, and transfers classified as spending.
- The total is too low: Check for blank or misspelled categories, amounts stored as text, and transactions outside the formula’s row range.
- Negative remaining amounts are confusing: Label the difference column and explain that planned minus actual becomes negative when spending exceeds the plan.
- Monthly totals shift unexpectedly: Verify that the month-start cell is correct and that rows were not sorted in only one column. Sort the table as a whole.
When a formula result looks wrong, test it with a small, known example before changing the formula. For instance, filter Transactions to a single month and category, add the visible expenses, and compare that sum with the summary. This can reveal an incorrect type label or date before you revise a working calculation.
Frequently Asked Questions
How do I make a simple budget tracker in Excel?
Use one sheet for dated transactions with description, category, type, and amount, and a second sheet for monthly planned and actual totals. Start with a manageable category list, enter expenses consistently, and review the sheet regularly.
Should expenses be entered as negative numbers?
Not necessarily. For a basic tracker, entering expenses as positive amounts and marking their type as Expense is straightforward. The key is to choose one method and make the summary formulas follow that method; mixing positive and negative conventions can distort totals.
How do I track a different month?
Change the summary’s month-start date to the first day of the month you want to review. A formula that uses the first day of that month and the first day of the next month as its date boundaries will include the correct transactions without needing a different formula for each month.
Can I use this budget tracker in Excel for the web or on Mac?
Excel for the web and Excel for Mac support common functions such as SUMIFS, SUM, and EDATE, although some interface details can differ from Excel for Microsoft 365 on Windows. If a menu location differs, use the corresponding command in the version’s ribbon or help search; check formulas and data validation after moving a workbook between apps.
Is a spreadsheet better than a budgeting app?
It depends on what you value. A spreadsheet offers flexible categories, visible calculations, and control over the data structure, but usually requires manual entry or import review. An app may automate account connections and categorization, but features, costs, privacy practices, and supported institutions vary. Choose based on the amount of upkeep you will realistically do and review the provider’s terms before linking accounts.
Related
- Goal Seek on Multiple Cells in Excel: What Works?
- Add Slide Numbers to PowerPoint: Steps and Fixes
- Debt Payoff Spreadsheet Template: Free Excel Guide
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets Scheduling: Create a Shared ScheduleTo use Google Sheets for scheduling, create a spreadsheet with one row per shift, appointment, or task; add…
- How to Add a Running Header in Word 2016To add a running header in Word 2016, choose Insert > Header, select a layout, and type the…
- Excel Data Validation Allow List: Create and Manage OneYes. In Excel for Microsoft 365 on Windows, create an allow list by selecting the cells, choosing Data…
- Split Cell Color in Excel: What Works and What Doesn’tExcel cannot natively fill one cell with two separate solid colors. For a clear split-color effect, use two…
- How to Schedule an Email in Outlook Across DevicesTo schedule an email in Outlook, write your message, open the menu beside Send, choose Schedule send or…
- Can You Create a Page Break in Excel? How to Do ItYes. In Excel for Microsoft 365 on Windows, select the cell where the next printed page should begin,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.