The quickest Excel formula to flag an over-budget item is =IF(B2>C2,"Over","OK"), where B2 holds the actual spend and C2 holds the budget. This returns “Over” when actuals exceed the budget and “OK” otherwise; for a more detailed result, use =IF(B2>C2,"Over by "&TEXT(B2-C2,"$0.00"),"Within budget").
Why You Need a Budget Variance Formula
Tracking spending against a budget is a core task in personal finance, small business accounting, and project management. Manually scanning columns of numbers is error-prone and slow, especially when you have hundreds of line items. A simple Excel formula automates the comparison, letting you spot problem areas instantly. The IF function is the workhorse here because it evaluates a condition (actual > budget) and returns one value if true, another if false. You can extend it to compute the variance amount, percentage, or even trigger conditional formatting to highlight over-budget cells in red.
Setting Up Your Data for the Formula
Before writing any formula, arrange your spreadsheet so each row is one budget item (e.g., a department, project, or expense category). A clean layout makes formulas easier to write and maintain.
- Column A: Item name (e.g., “Marketing”, “Utilities”)
- Column B: Actual amount spent
- Column C: Budgeted amount
- Column D: Variance (actual minus budget) – optional but useful
- Column E: Status (Over/Under) – where the IF formula goes
For example, put actuals in B2:B20, budgets in C2:C20, and status in E2:E20. Always keep numbers as numeric values, not text with dollar signs, so Excel can calculate correctly.
Core Formulas: Over Budget, Variance, and Percentage
Here are the essential formulas, assuming data in row 2:
- Status flag:
=IF(B2>C2,"Over","Under")– returns “Over” if actual exceeds budget, otherwise “Under”. - Variance amount:
=B2-C2– positive means over budget, negative means under. - Variance percentage:
=IF(C2=0,"N/A",(B2-C2)/C2)– shows the over/under as a percentage of budget. The IF avoids division by zero. - Over-budget amount only:
=IF(B2>C2,B2-C2,0)– gives the excess spend, or 0 if within budget.
For the percentage formula, format the result as a percentage using Home > Number > Percent Style. If you want to show a negative percentage for under budget, use =(B2-C2)/C2 directly, but handle zero budgets separately.
Using Conditional Formatting to Highlight Over-Budget Cells
Instead of (or in addition to) a text status, you can make over-budget items visually pop. Select the range of actual amounts (e.g., B2:B20), then go to Home > Conditional Formatting > New Rule. Choose “Use a formula to determine which cells to format” and enter =B2>C2 (adjust to the first data row). Click Format, choose a red fill, and OK. Now any cell where the actual exceeds its corresponding budget turns red automatically. This works in Excel for Microsoft 365 on Windows, Mac, and Excel for the web. On Mac, the menu path is the same: Home tab > Conditional Formatting.
Handling Common Problems and Edge Cases
Real spreadsheets are messy. Here are typical issues and fixes:
- Blank cells or text in numeric columns: If B2 or C2 contains text, the comparison may return unexpected results. Use
=IF(OR(NOT(ISNUMBER(B2)),NOT(ISNUMBER(C2))),"Check data",IF(B2>C2,"Over","Under"))to flag non-numeric entries. - Zero budget: Dividing by zero gives #DIV/0!. Use
=IF(C2=0,"No budget",(B2-C2)/C2)for percentage. - Negative budgets (e.g., cost savings targets): The simple comparison still works, but interpret “over” carefully. For a negative budget, spending less than the budget (e.g., -500 vs -300) might be considered over. Adjust the condition accordingly, e.g.,
=IF(B2if lower is worse. - Ranges that shift: Use Excel Tables (Insert > Table) so formulas auto-fill and references adjust automatically.
Comparing Excel Functions: IF vs. IFS vs. SUMIFS
For simple over/under checks, IF is enough. But if you have multiple conditions, consider:
- IFS (Excel 2019 and later) for multiple thresholds:
=IFS(B2>C2,"Over",B2=C2,"Exact",TRUE,"Under")– easier than nested IFs. - SUMIFS to total over-budget amounts across a category:
=SUMIFS(B2:B20,A2:A20,"Marketing",B2:B20,">"&C2:C20)– but note this sums actuals where actual > budget for each row; it requires a helper column if you need the excess amount. A simpler approach: use a helper column D with=IF(B2>C2,B2-C2,0)then=SUM(D2:D20).
For most budget tracking, IF and a helper column are the clearest and most maintainable.
Frequently Asked Questions
How do I show "Over Budget" in red automatically?
Use Conditional Formatting as described above. Select the actual amount cells, create a new rule with the formula =B2>C2, and set a red fill. This updates instantly as you enter data.
What if my budget and actuals are in different sheets?
Reference the other sheet directly, e.g., =IF(B2>Sheet2!C2,"Over","Under"). Make sure the sheet name with spaces is in single quotes, like 'My Budget'!C2.
Can I count how many items are over budget?
Yes, use COUNTIF with a helper column: put the status formula in E2:E20, then =COUNTIF(E2:E20,"Over"). Or use SUMPRODUCT without a helper: =SUMPRODUCT(--(B2:B20>C2:C20)).
How do I calculate the total amount over budget?
Add a helper column D with =IF(B2>C2,B2-C2,0), then sum it with =SUM(D2:D20). Alternatively, use =SUMPRODUCT((B2:B20>C2:C20)*(B2:B20-C2:C20)).
Why does my formula show #VALUE!?
Usually because one of the cells contains text or an error. Check that both B and C columns are numeric. Use the ISNUMBER check in the formula to identify problem rows.
Does this work in Google Sheets?
Yes, the IF function works identically in Google Sheets. Conditional formatting is under Format > Conditional formatting, and the formula syntax is the same.
Final Tips for Accurate Budget Tracking
Always double-check your formulas with a few manual calculations. For financial data, ensure your actuals and budgets are in the same currency and time period. If you're tracking a project, consider using a pivot table to summarize over-budget items by category. Remember, these formulas are tools to inform decisions; they don't replace professional financial advice. For complex budgets, consult an accountant or financial advisor.
Related
- Excel Eye Center Provo: Services, Doctors & What to Know
- Excel Eye Center Spanish Fork: Services, Doctors & More
- Free Excel Expense Tracking Sheet Template
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Power Query Unique Values: Remove Duplicates or Keep One?To get unique values in Power Query in Excel for Microsoft 365, select the column or columns that…
- Excel Filter Duplicates Without Deleting Data SafelyYes. In Excel for Microsoft 365 on Windows, add a helper column that identifies repeated values, then choose…
- XLOOKUP Example: Step-by-Step Formula Guide for ExcelTo find a product’s price by its ID in Excel, enter =XLOOKUP(E2,$A$2:$A$6,$C$2:$C$6,"Not found") in the result cell. If…
- Show Formulas in Excel Print: Steps and FixesTo show formulas in an Excel printout, turn on Show Formulas for the worksheet, then print it as…
- Excel OFFSET Function: Dynamic Ranges and ExamplesIn Excel, OFFSET is a function that returns a reference shifted a specified number of rows and columns…
- Average Between Two Dates in Excel: Formulas & TipsTo average values whose dates fall between two dates in Excel, use =AVERAGEIFS(B2:B100,A2:A100,">="&E2,A2:A100,"<="&F2). This averages numbers in B2:B100…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.