Excel Formula Budget Tracker: Setup & Use

Formulas & functions · Updated November 22, 2026

The core of an Excel budget tracker is a simple formula: =SUM for totals and =B2-C2 for the difference between income and expenses. For a working tracker, put your income in column B, your expenses in column C, and use =B2-C2 in column D to see your net each row, then sum the column with =SUM(D2:D13) for a monthly total. This article shows you how to build that tracker, which formulas to use, and how to avoid common mistakes.

Why Use Excel for Budgeting?

Excel gives you complete control over your budget data. Unlike a dedicated budgeting app, you are not locked into categories or layouts—you can design a tracker that matches exactly how you spend. You can also keep everything on your own computer, no cloud account required, and you can analyze your numbers with pivot tables, charts, and conditional formatting. For many people, the flexibility and the one-time cost (or free if you use Excel for the web) make it more appealing than a subscription service.

Core Formulas You Need

Every budget tracker relies on a handful of formulas. Here are the essential ones, with the layout they assume.

  • Total income: With income amounts in B2:B13, use =SUM(B2:B13). This adds all values in that range. For example, if B2:B13 contains 2000, 1500, and 0 for the rest, the result is 3500.
  • Total expenses: With expenses in C2:C13, use =SUM(C2:C13). If C2:C13 has 800, 300, and 200, the result is 1300.
  • Net (income minus expenses): In D2, enter =B2-C2 and drag down to D13. For row 2 with B2=2000 and C2=800, the result is 1200.
  • Running balance: In E2, enter =D2. In E3, enter =E2+D3 and drag down. This gives a cumulative total, so you see your cash position grow or shrink over the month.
  • Percentage of income: In F2, enter =C2/$B$14 (assuming B14 holds total income) and format as a percentage. With C2=800 and B14=3500, the result is 22.86%.

For a more advanced tracker, you can use =IF to flag overspending. For example, with a budget cap in G2, enter =IF(C2>G2, "Over", "OK") in H2. This returns “Over” if the expense exceeds the cap, otherwise “OK”.

How to Build a Simple Budget Tracker

Here is a step-by-step way to set up a monthly tracker in a new worksheet.

  1. Open a blank workbook. In row 1, create headers: A1 = “Category”, B1 = “Income”, C1 = “Expense”, D1 = “Net”, E1 = “Running Balance”.
  2. In column A, list your categories (e.g., Salary, Freelance, Rent, Groceries, Utilities, Entertainment). Put income items in rows with amounts in column B, and expense items in rows with amounts in column C. Leave a few blank rows for unexpected items.
  3. In row 14 (or the first blank row after your last entry), label A14 = “Total”, then in B14 enter =SUM(B2:B13) and in C14 enter =SUM(C2:C13).
  4. In D14, enter =B14-C14 to get your net income for the month.
  5. For the running balance, in E2 enter =D2 (assuming D2 has your first net), then in E3 enter =E2+D3 and drag down to E13. In E14, you can enter =E13 to show the final balance.
  6. Apply number formatting: select B2:E14, go to Home > Number Format > Currency (or press Ctrl+Shift+4 on Windows, Cmd+Shift+4 on Mac).

This gives you a working tracker in under five minutes. You can then add more columns for notes, dates, or payment methods.

Advanced Formulas for Deeper Insight

Once the basics work, you may want to analyze patterns. Use =SUMIF to total expenses by category. With categories in A2:A20 and amounts in C2:C20, enter =SUMIF(A2:A20, "Groceries", C2:C20) to get the total spent on groceries. For a dynamic version, put the category name in a cell (say G2) and use =SUMIF(A2:A20, G2, C2:C20).

To find the highest expense, use =MAX(C2:C20) and to find the lowest, =MIN(C2:C20). To see how many times you spent over a certain amount, use =COUNTIF(C2:C20, ">100").

For a more visual approach, create a pie chart of expenses by category. Select A2:A20 and C2:C20, go to Insert > Charts > Pie, and Excel will generate the chart. Update it automatically by using a table (Ctrl+T) so new rows are included.

Common Problems and How to Fix Them

Many users run into the same issues. Here are the most frequent and their solutions.

  • Negative numbers showing as red: Use conditional formatting. Select D2:D14, go to Home > Conditional Formatting > Highlight Cells Rules > Less Than, and enter 0. Choose a red fill.
  • Formulas not updating: Check that calculation is set to automatic. Go to Formulas > Calculation Options > Automatic. If you changed it to manual, Excel will not recalculate until you press F9 (Windows) or Cmd+= (Mac).
  • SUM includes blank cells that have formatting: If you see a 0 in a blank cell, it may contain a formula or a space. Clear the cell or use =SUMIF(B2:B13, "<>", B2:B13) to ignore blanks.
  • Dragging formulas gives wrong references: Use absolute references (with $) when you want a cell to stay fixed. For example, =C2/$B$14 keeps the denominator fixed.
  • Accidentally deleting a formula: Use cell protection. Select the cells with formulas, go to Home > Format > Lock Cell, then protect the sheet via Review > Protect Sheet. This prevents accidental edits.

Comparing Excel to Other Budget Tools

Excel is not the only option. Dedicated apps like Mint (now part of Credit Karma) or YNAB offer automatic bank syncing and mobile apps, but they require a subscription or have privacy trade-offs. Google Sheets is a free alternative that works in the browser and supports the same formulas, but it lacks some of Excel’s advanced features like Power Query. For a one-time setup and full control, Excel is hard to beat. However, if you want automatic transaction imports, a dedicated app may save time. The choice depends on your comfort with spreadsheets and your need for automation.

Cost and Safety Considerations

Excel is part of Microsoft 365, which costs about $10 per month or $100 per year for a personal subscription. You can also use the free Excel for the web at office.com, which supports most formulas but not all features like macros. If you use a macro to automate tasks, remember to save a copy of your workbook before running it, because macros cannot be undone with Ctrl+Z. Also, be careful when downloading budget templates from the internet—they may contain malicious macros. Only use templates from trusted sources like Microsoft’s official template gallery.

Frequently Asked Questions

How do I make a budget tracker in Excel?
Start with a simple layout: list categories in column A, income in B, expenses in C, and use =B2-C2 in D for net. Sum the columns with =SUM, and add a running balance with =E2+D3 dragged down. Format as currency and you have a basic tracker.

What is the best Excel formula for budgeting?
There is no single best formula—it depends on what you need. =SUM is essential for totals, =SUMIF for category totals, and =IF for alerts. A combination of these covers most budgeting needs.

Can I use Excel for free?
Yes, Excel for the web is free with a Microsoft account. It supports most formulas, but lacks some features like macros and certain data tools. For full functionality, you need a Microsoft 365 subscription.

How do I track multiple months in one sheet?
Create a new sheet for each month, or use a single sheet with columns for each month (e.g., B for Jan, C for Feb). Use =SUM across rows for yearly totals. For a dynamic approach, use a pivot table.

Why is my running balance not updating?
Check that your formulas are correct and that calculation is set to automatic. Also, ensure you are dragging the formula down far enough to cover new rows. If you added a new row, copy the formula from the row above.

How do I add a savings goal to my tracker?
Add a new column for “Savings” and enter your goal amount in a cell. Use =B14-C14 to see your net, then compare it to your goal with =IF(D14>=G2, "Goal met", "Short by " & G2-D14).

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.