Expense Tracker and Budget Spreadsheet: Setup Guide

How-to basics · Updated November 21, 2026

An expense tracker and budget spreadsheet is a workbook for recording income and spending, setting category limits, and comparing actual expenses with a monthly plan. In Excel for Microsoft 365 on Windows, create a transaction table with dates, categories, transaction types, and amounts; add a monthly budget by category; then use SUMIFS to total expenses for the month.

What an expense tracker and budget spreadsheet does

An expense tracker keeps a record of what money came in or went out. A budget spreadsheet adds a forward-looking plan: how much you intend to spend in areas such as housing, groceries, transport, and entertainment. Used together, the two views help answer different questions: “Where did the money go?” and “How does that compare with what I planned?”

A spreadsheet is useful because its categories, reporting period, and level of detail can be adjusted to suit the person maintaining it. A simple version can be enough for one person tracking monthly household expenses. A more detailed one might separate accounts, distinguish recurring bills from flexible spending, or track reimbursements. The important thing is that each transaction is entered consistently; a complex workbook with incomplete data is less informative than a small, regularly updated one.

This is a tracking and planning tool, not financial, tax, investment, or legal advice. For loan calculations or decisions, check the actual terms with the lender or a qualified professional.

Choose a clear workbook layout

Keep raw transactions separate from the monthly summary. On a sheet named Transactions, use one row per transaction and these headings in row 1:

  • Date: the date the transaction posted or occurred. Pick one convention and use it consistently.
  • Description: a short label such as “Market” or “Electric bill.” Avoid storing sensitive account details here.
  • Category: a standard label such as Groceries, Rent, or Pay.
  • Type: Income or Expense. This distinction matters when adding totals.
  • Amount: a positive number for either type. The Type column shows whether it is money in or out.

On a separate Budget sheet, put categories in column G and their monthly planned amounts in column H, starting in row 2. Add Actual, Remaining, and Percent used in columns I, J, and K. Put the first day of the month being reviewed in M2, such as January 1, 2026; place the first day of the following month in M3. Keeping the month boundaries in cells makes it easier to reuse the summary.

Use the same spelling for a category in Transactions and Budget. If “Dining out” appears in one place and “Restaurants” in another, Excel treats them as separate categories. Likewise, do not enter a card payment as a new expense if you already recorded the purchases paid by that card; that would count the same spending twice.

Build the workbook in Excel

  1. Enter the transaction headings, then select the range and choose Insert > Table. Confirm that the table has headers. A table makes it easier to filter and sort records and to extend the formatted range as entries are added.
  2. Enter the budget categories and planned monthly amounts on the Budget sheet. Include a category only if it will help you make sense of spending; overly fine categories can make upkeep tedious.
  3. For a category column, select its entry cells and choose Data > Data Validation. In the dialog, set Allow to List and provide the allowed category labels as the source. A consistent list reduces spelling variations. If you keep the list in cells, use that range as the source.
  4. Format the Date column as dates and Amount and budget cells as currency. On the Home tab, use the Number group to select a suitable date or currency format. Formatting changes how values display, not the underlying value used in calculations.
  5. Enter the month start date in M2 and the next month start date in M3. This convention includes the first day of the month and excludes the first day of the next month, so transactions with times attached to their dates are handled reliably.

These steps describe Excel for Microsoft 365 on Windows. Excel for Mac has the same core table, validation, and formula capabilities, though ribbon placement or dialog labels can differ slightly between platforms and updates. Excel for the web also supports many of these features, but some workbook-editing controls may appear in different places.

Use formulas to total spending

In the examples below, the Transactions sheet has dates in A2:A1000, categories in C2:C1000, types in D2:D1000, and positive amounts in E2:E1000. On Budget, category names start in G2, planned amounts in H2, and the selected month starts in M2. These formulas use regular worksheet ranges, so increase the last row if the workbook grows beyond 1,000 transactions.

With the first day of the month in M2, enter this in M3 to calculate the next month’s boundary. For a January 1, 2026 start date, the result is February 1, 2026.

=EOMONTH(M2,0)+1

With category names in Transactions!C2:C1000, type in D2:D1000, amounts in E2:E1000, dates in A2:A1000, the category being summarized in Budget!G2, and the month boundaries in Budget!M2:M3, enter this formula in Budget!I2. If January includes Grocery expenses of $120, $85, and $60, the result for that category is $265.

=SUMIFS(Transactions!$E$2:$E$1000,Transactions!$C$2:$C$1000,G2,Transactions!$D$2:$D$1000,"Expense",Transactions!$A$2:$A$1000,">="&$M$2,Transactions!$A$2:$A$1000,"<"&$M$3)

Copy the formula down the Actual column for the remaining budget categories. SUMIFS adds amounts only when every condition is met: the category matches, the type is Expense, and the date falls within the selected month. The formula uses the same January example to show the result, not a promise that any particular month will have that total.

With planned amounts in H2:H20 and actual totals in I2:I20, put this in J2 and copy it down. If H2 is $500 and I2 is $265, the result is $235 remaining.

=H2-I2

A positive result means the actual total is below the planned amount; a negative result means it has exceeded it. With a planned amount in H2 and actual spending in I2, put this in K2 to calculate the share of the budget used. For $500 planned and $265 spent, it returns 53% after formatting the cell as Percentage.

=IF(H2=0,"",I2/H2)

The blank result avoids a divide-by-zero error when a category has no budget. These formulas depend on using positive expense amounts and identifying expenses with the Type field. If an imported bank file represents expenses as negative numbers, standardize the amounts before applying this layout or adapt the calculation consistently; mixing positive and negative conventions can make totals misleading.

Set categories and budget amounts thoughtfully

Categories should be specific enough to guide a decision but broad enough to apply consistently. For example, “Groceries” and “Dining out” can reveal different patterns; splitting groceries into many store-specific categories usually adds effort without improving a monthly overview. A reasonable starting set might include Housing, Utilities, Groceries, Transport, Health, Dining out, Subscriptions, Personal, and Savings. Add or combine categories after seeing what you actually use.

Set planned amounts before the month begins if you want a genuine plan-versus-actual comparison. You can also start by tracking a month or two, then use that record to create more realistic category limits. A budget amount is an input for comparison, not an automatic prediction or guarantee. Irregular costs—such as annual insurance or a car repair—may need a separate sinking-fund category or a note explaining which month’s budget includes them.

Decide how to treat refunds, reimbursements, and transfers. One simple method is to record a refund as an income-type entry with the same category, while documenting that convention in the workbook. Transfers between your own accounts are not new income or spending; recording both sides as ordinary transactions can inflate totals. Consistency matters more than choosing one universal method, but the method should be clear to anyone reading the sheet.

Review the month and make the summary readable

At a regular interval, enter or import transactions, check categories, and review the Actual and Remaining columns. Filtering the Transactions table by date or category can help locate a specific purchase. Sorting by date makes missing entries easier to notice. Excel tables provide filter controls in the heading row; clear the filters when you want to see all records again.

Conditional formatting can make exceptions visible without changing the underlying numbers. Select the Remaining cells and choose Home > Conditional Formatting > Highlight Cells Rules to set a rule for values less than zero. Use a restrained format so the sheet remains readable. You can also create a simple chart from the category and actual-total columns, but check that the chart uses the intended month and does not include empty rows or totals as categories.

Reconcile the workbook against bank or card statements periodically. A spreadsheet can only report entries it contains, and a missed transaction, duplicate import, or incorrect date can change the apparent result. Comparing statement totals and investigating differences is more dependable than assuming that a formula makes the data correct.

Common spreadsheet problems and how to avoid them

  • Dates stored as text: a date that looks right may not behave like a date in a formula. If a transaction is excluded unexpectedly, check the cell’s value and number format, then convert text dates to real Excel dates.
  • Inconsistent category names: use a validation list and review new labels. Extra spaces and spelling differences can split one category into multiple totals.
  • Wrong month boundaries: use a first-of-month start and the next month’s first day as the exclusive end. This avoids relying on a fixed day count, since months have different lengths.
  • Duplicate or missing imports: compare imported entries with the source statement and inspect repeated dates, descriptions, and amounts. Do not assume identical amounts are duplicates; recurring bills can legitimately repeat.
  • Double-counted payments: record purchases as expenses, but do not also classify the credit-card bill payment as another expense if those purchases are already included.
  • Formula ranges that stop too early: the sample formulas end at row 1000. Extend their ranges when needed, or use a well-structured Excel table and table references if you are comfortable updating the formulas.

When results seem wrong, test one category and one month against a few known transactions. Small checks are often faster than inspecting a large workbook all at once.

Privacy, backups, and spreadsheet limits

A budget file can reveal sensitive habits and financial details. Keep only information needed for tracking: descriptions and amounts are usually sufficient, and full account numbers, passwords, and security answers do not belong in the workbook. Save it in a location with access controls appropriate for your circumstances, and be cautious when sharing copies or emailing attachments.

Keep a backup before making major structural changes, and consider saving a dated copy at the end of each month if you need a record of the figures as they stood then. A spreadsheet is editable, so formulas can be overwritten and entries can be changed without an audit trail. It is not a substitute for official bank statements or accounting records where those records are required.

Manual upkeep is the main trade-off. Entering transactions takes time, and a custom workbook does not automatically know whether a transaction is miscategorized. A spreadsheet may be a poor fit if several people need simultaneous updates, automatic bank synchronization, or detailed business bookkeeping with formal reporting requirements.

Spreadsheet versus apps and other options

A spreadsheet offers flexibility and transparent formulas: you can inspect how totals are calculated, add fields, and keep a copy in a format you control. It usually requires more manual entry and maintenance than an expense-tracking app. App features, account connections, pricing, data handling, and export options vary, so review the provider’s current terms rather than assuming that every app offers the same capabilities.

A paper ledger can work for a small number of transactions and avoids spreadsheet setup, but totals and category comparisons must be calculated by hand. A bank’s built-in spending summary can be convenient, but its categories may not match your own, and it may cover only accounts held at that institution. You can also use a hybrid approach: export transaction data from a bank when available, review and categorize it in Excel, and retain the original statement for reference.

Frequently Asked Questions

Is an expense tracker the same as a budget spreadsheet?
An expense tracker records transactions that have happened. A budget spreadsheet records planned amounts and can compare those plans with actual spending. One workbook can do both when it keeps transaction data and budget targets in clearly separated sections.

Can I use Excel to track expenses for free?
Excel itself is a paid product in many circumstances, though access may be included through certain Microsoft plans or provided by an organization. The workbook structure and formulas described here do not require a paid budgeting add-in. Check Microsoft’s current licensing and access options for your situation.

Should I enter expenses as negative numbers?
Not with the example layout. It records all amounts as positive numbers and uses the Type column to distinguish Income from Expense. A negative-number convention can also work, but the formulas and totals must be designed for that convention and used consistently.

How do I make the spreadsheet update for a new month?
Change M2 to the first day of the month you want to review and update M3 to the first day of the following month. The sample formula in M3 calculates that boundary automatically. The transaction totals then use the new date range.

Can I import transactions from my bank?
Some banks provide downloadable transaction files, but available formats and fields differ. If you import a file, check its dates, signs, categories, and duplicate rows before relying on the summary. Keep the original statement or download so you can investigate discrepancies.

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.