If your total budget is in B2 and the amount spent is in C2, enter =B2-C2 in another cell to calculate the budget amount remaining. For example, a $2,000 budget minus $1,250 spent leaves $750.
The Basic Remaining-Budget Formula
The calculation is simply the amount set aside minus the amount already spent. In the example above, B2 contains 2000 and C2 contains 1250, so the result is 750. Format the result as currency if you want it to display with a dollar sign and two decimal places.
This formula works whether your spending is a single total or a total you update over time. The key is to keep the two inputs clear: one cell represents the budget available for the period, and the other represents spending already counted against it. If you reverse the cell references, you will calculate spending minus budget instead, which gives the opposite sign.
A negative result is not an error. It means spending is greater than the budget. For example, if B2 contains 2,000 and C2 contains 2,150, the result is -150: the budget is exceeded by $150. Keeping that negative value visible can be more informative than changing it to zero.
Set Up the Cells Clearly
Start with labels so the sheet remains understandable when you return to it later. For a small budget summary, put Budget in A2, the budget amount in B2, Spent in A3, the current spending total in B3, and Remaining in A4. Put the result in B4. In this layout, the budget is in B2 and spending is in B3, so the remaining-balance formula for B4 is =B2-B3. If B2 is 2,000 and B3 is 1,250, B4 returns 750.
Use real numbers in the input cells, not text that merely looks like a number. Enter 1250 and apply a currency format rather than typing a value such as $1,250 spent. Text entries can prevent calculations from working as expected. In Excel for Microsoft 365 on Windows, select the amount cells and use Home > Number to choose a currency or accounting format. The corresponding number-format controls are also available on the Mac ribbon, though their placement can vary slightly with the window size.
- Use one consistent period, such as a month, for the budget and its expenses.
- Label whether a figure is a planned amount, an actual amount, or a remaining amount.
- Keep the budget and spending inputs separate from the formula result.
- Use the same currency and number format throughout the sheet.
Calculate Remaining Money From a Transaction List
If you record each expense on its own row, Excel can add the transactions before subtracting them from the budget. With the budget in B2 and expense amounts in C2:C10, enter =B2-SUM(C2:C10) in the remaining cell. If the amounts in C2:C10 total 1,250 and B2 is 2,000, the result is 750. The SUM function ignores blank cells in the range, which makes it convenient to leave unused transaction rows empty.
For instance, the transaction list might contain 120 for groceries, 65 for transport, and 90 for a utility bill. Those entries total 275. If the budget is 500, the balance is 225. Adding each new expense to the list updates the result as long as the transaction falls within the range included in the formula. If you expect the list to grow past row 10, use a range with extra rows or convert the list to an Excel Table so new rows can be included more conveniently.
Be careful not to include the remaining-balance cell inside the SUM range. If the formula adds its own result as part of spending, it can create a circular reference or an incorrect total. Keep transaction amounts in one column and the calculation outside that column.
Track Remaining Amounts by Category
A single total tells you how much is left overall, but category balances can show where the money is going. Suppose categories are listed in A2:A10, their budget amounts are in B2:B10, transaction categories are in D2:D100, and transaction amounts are in E2:E100. In C2, enter =B2-SUMIF($D$2:$D$100,A2,$E$2:$E$100) and copy it down through the category rows. The formula subtracts expenses tagged with the category in A2 from that category’s budget in B2.
For a concrete example, suppose A2 says Food and B2 contains 600. If the transaction list has Food expenses of 125, 84, and 91, those expenses total 300, so C2 returns 300 remaining. The dollar signs in the transaction ranges keep those ranges fixed when you copy the formula down; the category reference changes from A2 to A3, then A4, and so on.
Category labels need to match between the budget table and transaction list. A category typed as Dining in one place and Food in another will not be treated as the same category. Consistent spelling, or a dropdown list for category entries, can reduce this problem. Also make sure each transaction amount appears once: duplicate rows will be counted twice.
Show a Running Balance as You Enter Expenses
A running balance displays what remains after each transaction, rather than only showing the final total. With the budget in B2 and expense amounts beginning in C2, enter =$B$2-SUM($C$2:C2) in D2 and fill it down. The first row subtracts the first expense from the budget; each later row expands the end of the SUM range to include the next transaction.
For example, if B2 is 500 and C2 and C3 contain 120 and 80, the result in D2 is 380. After filling the formula down to D3, it returns 300 because total spending is then 200. The fixed references to B2 and the start of the expense range keep the starting points in place as the formula is copied. Enter transactions in the order you want the balance to reflect; adding a past expense in the middle of the list will recalculate later balances.
Calculate the Percentage of the Budget Left
A remaining amount in dollars answers how much is unspent. A percentage can make it easier to compare categories with very different budget sizes. With the original budget in B2 and the remaining amount in D2, enter =IFERROR(D2/B2,0) in E2. If the budget is 2,000 and the remaining amount is 750, the formula returns 0.375, which displays as 37.5% when E2 is formatted as a percentage.
IFERROR returns zero if the division produces an error, such as when the budget is zero. That avoids an error message, but it can make a zero-budget category look as though it has 0% remaining. If that distinction matters, label the percentage column clearly or use a separate note for categories with no budget. Do not confuse percentage remaining with percentage spent: in this example, 37.5% remains and 62.5% has been spent.
Make Overspending Easy to Spot
Conditional formatting can highlight negative balances without changing the underlying calculation. Select the remaining-balance cells, then choose Home > Conditional Formatting > Highlight Cells Rules > Less Than. Enter 0 and choose a visible format. Excel will apply that formatting to negative results, so an overspent category stands out while still showing the actual amount over budget.
You can also use a simple status label, but the amount itself should remain available for context. A result of -150 means the budget has been exceeded by $150; displaying only the word Over budget hides how far it has been exceeded. Consider using a separate status column if you want both the numeric balance and a quick visual indicator.
Common Formula Problems and How to Fix Them
- The result is unexpectedly negative: Check whether spending is greater than the budget, and confirm that the budget and spending cells have not been reversed.
- The result does not change: Confirm that the formula points to the cells you are editing. Also check that calculation is set to automatic under Formulas > Calculation Options > Automatic.
- A transaction is missing from the total: Check whether its row falls inside the SUM or SUMIF range. Extend the range if the entry is below its last row.
- A category total is too large: Look for duplicate transactions or inconsistent category assignments. SUMIF includes every matching row in its specified range.
- The answer looks like a date or has too many decimals: Change the result cell’s number format to Currency, Accounting, or an appropriate number format.
- Excel displays a formula instead of a result: Check whether the cell is formatted as Text, or whether Show Formulas is enabled. Set the cell to General or a number format, then re-enter the formula if needed.
For reliable totals, avoid mixing refunds and expenses without deciding how refunds should be represented. A refund entered as a negative expense reduces total spending, which increases the remaining balance under the subtraction method. That is often useful, but it should match the way you intend to track the budget.
Choosing the Right Formula for Your Sheet
Use the basic subtraction formula when you already have one total for spending. Use a SUM-based formula when you list transactions and want Excel to total them. Use SUMIF when transactions have categories and you need a separate balance for each one. A running-balance formula is useful when you want to see how the available amount changes after each expense.
Whichever approach you choose, keep the underlying data easy to audit. A transaction list with a date, description, category, and amount makes it easier to check where a total came from than a single manually updated spending figure. For recurring budgets, copy the layout for each period or use a clear period column so that expenses from one month do not accidentally reduce another month’s budget.
These examples show spreadsheet arithmetic, not financial advice. If a budget includes loan payments or other contractual amounts, confirm the payment figures and terms with the lender or a qualified professional rather than relying on a spreadsheet estimate.
Frequently Asked Questions
What is the formula for budget minus actual spending?
Subtract actual spending from the budget. If the budget is in B2 and actual spending is in C2, use =B2-C2. A positive result is still available; a negative result indicates that spending has exceeded the budget.
How do I subtract a list of expenses from a budget?
With the budget in B2 and expense amounts in C2:C100, use =B2-SUM(C2:C100) in the remaining cell. Make sure the range includes all relevant transactions but does not include the result cell.
Why does my remaining budget show a negative number?
A negative balance means the spending total is larger than the budget total. Check that both amounts cover the same time period and that expenses have not been duplicated. If the inputs are correct, the negative value shows the amount over budget.
How can I calculate what remains for each category?
Put category budgets in one table and categorized expenses in a transaction list, then subtract a SUMIF total for each category from its budget. Make sure category names match exactly so the transactions are included in the intended total.
Can I calculate a remaining budget as a percentage?
Yes. Divide the remaining amount by the original budget, then format the result as a percentage. For a budget in B2 and remaining amount in D2, =IFERROR(D2/B2,0) returns 37.5% when D2 is 750 and B2 is 2,000.
Related
- AI and Power Query: What It Can and Cannot Do in Excel
- Power Query Split Cell Into Rows: Steps and Examples
- Power Query Editor: How to Split Text into Multiple Columns
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel: Test If a Cell Contains Specific TextIn Excel for Microsoft 365, test whether a cell contains specific text with =ISNUMBER(SEARCH("apple",A2)). It returns TRUE when…
- Gross Margin Excel Calculation: Formula and ExamplesWith revenue in B2 and cost of goods sold (COGS) in C2, calculate gross margin in Excel with…
- INDIRECT in Excel: Create Dynamic Cell ReferencesIn Excel, INDIRECT turns text that looks like a cell or range address into a usable reference. For…
- COUNTIF Excel Range: What to Select and How It WorksIn Excel, the range in COUNTIF is the group of cells the function checks; the basic form is…
- Excel Cell Fill Shortcut: Fill Down, Fill Right, and ColorIn Excel for Microsoft 365 on Windows, press Ctrl+D to fill down from the top cell of a…
- Average Filtered Column in Excel: Formula and TipsTo average only the visible rows in a filtered Excel column, use =SUBTOTAL(1,B2:B100), replacing B2:B100 with your data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.