Compare Actual vs Budget in Excel: Step-by-Step

How-to basics · Updated November 20, 2026

To compare actual spending with a budget in Excel, place the budget in column B and actual results in column C, then subtract the budget from actuals. With budget in B2 and actual in C2, enter =C2-B2 in D2; if the budget is $2,000 and actual spending is $2,100, the variance is $100, meaning spending is $100 over budget. Add a variance percentage to see the difference relative to the plan. You will need a budget and actual figures that use the same categories and time period.

What You’ll Need

  • Microsoft Excel for Microsoft 365 on Windows. The core formulas and steps also work in Excel for Mac and Excel for the web, though ribbon placement can vary slightly.
  • A budget and actual figures for the same period, such as one month or one quarter. Use the same currency, category names, and accounting basis in both sets.
  • A blank worksheet or a copy of your existing workbook. Keep an untouched copy of source data so you can check figures if totals do not match.

Step-by-Step

  1. Choose a comparison period. Decide whether you are reviewing a month, quarter, or year before building the comparison. A budget for January should not be compared with actual figures for a full quarter. If your source records cover multiple periods, filter or calculate the actuals for the chosen period only. Write the period and currency near the top of the sheet, such as “January 2026” and “USD,” so the scope is clear to anyone reading it.
  2. Set up a comparison table. In a new worksheet, make row 1 the header row: Category in A1, Budget in B1, Actual in C1, Variance in D1, and Variance % in E1. Put one category on each row, such as Rent, Payroll, Travel, and Supplies. Enter budget amounts in column B and actual amounts in column C. For example, with Rent in A2, a $2,000 budget in B2, and $2,100 actual spending in C2, the row is ready to calculate. Do not type currency symbols into the number values; apply a currency number format instead.
  3. Use consistent categories and values. Check that each budget category has a corresponding actual amount and that category names are spelled consistently. “Office supplies” and “Office Supplies” look similar, but inconsistent labels can cause a lookup or summary to miss transactions. If you do not have actuals for a category yet, enter zero only when zero is genuinely the correct amount; otherwise leave it blank and mark the row as incomplete so it is not mistaken for a confirmed zero.
  4. Calculate the variance amount. With budget in B2 and actual in C2, enter =C2-B2 in D2. For the example of $2,100 actual and $2,000 budget, Excel returns 100, or $100 over budget. A negative result means actuals are below budget. This definition—actual minus budget—is easy to audit, but the sign does not always mean “good” or “bad”: for an expense, positive usually means overspending; for revenue, positive usually means revenue exceeded the plan.
  5. Calculate variance as a percentage. With budget in B2, actual in C2, and the dollar variance in D2, enter =IF(B2=0,"",D2/B2) in E2. With a $100 variance against a $2,000 budget, the result is 0.05, which displays as 5% when formatted as a percentage. The formula leaves the cell blank when budget is zero, since dividing by zero cannot produce a meaningful percentage. Treat a blank percentage as “not applicable,” not as zero percent.
  6. Copy the formulas down the table. Select D2 and E2, then drag the small square at the lower-right corner of the selection down through the category rows. Excel adjusts the row references automatically, so the next row uses B3 and C3, then B4 and C4. Double-check the last row: the formula should reference that row’s budget and actual cells rather than a fixed row. If the table is an Excel Table, formulas entered in a calculated column generally fill down automatically.
  7. Optionally summarize transactions into actuals. If actual spending is stored as transactions, you can total it by category and month instead of adding it by hand. With transaction dates in A2:A100, category names in B2:B100, amounts in C2:C100, the category to summarize in A2, and the first day of the month in F1, enter =SUMIFS($C$2:$C$100,$B$2:$B$100,$A2,$A$2:$A$100,">="&$F$1,$A$2:$A$100,"<"&EDATE($F$1,1)) in the actual cell. For a Travel category with $120 and $80 of qualifying transactions in that month, the result is $200. F1 must be a real Excel date, and transaction dates must be real dates too, not text that merely looks like a date. This formula uses SUMIFS and EDATE, which are available in current desktop Excel.
  8. Add totals and check the overall result. With category rows 2 through 13 and a total row in row 14, enter =SUM(B2:B13) in B14 to total budgets, and =SUM(C2:C13) in C14 to total actuals. With those totals in B14 and C14, enter =C14-B14 in D14 for the overall dollar variance. With the overall variance in D14 and budget total in B14, enter =IF(B14=0,"",D14/B14) in E14 for the overall percentage. For example, a $12,000 budget total and $12,600 actual total produce a $600 variance and 5%. Confirm that this result agrees with the sum of the individual category variances.
  9. Format the comparison for reading. Select the budget, actual, and variance amounts, then use Home > Number to apply a currency or accounting format. Select the percentage column and choose Percentage; adjust the decimal places if needed. Bold the header and total row. To highlight unusual variances, select the variance cells and use Home > Conditional Formatting > Highlight Cells Rules. Choose rules that make sense for the review, and label the meaning of any colors; a red positive expense variance can indicate overspending, but the same color convention may not suit revenue.
  10. Review the comparison before sharing it. Verify the date range, categories, currency, and source totals. Check at least one row by subtracting actual minus budget with a calculator or by hand. Look into large variances rather than assuming they are errors: they may reflect a genuine price change, a timing difference, a missing budget line, or a transaction coded to the wrong category. Add a brief note to explain important exceptions and save the workbook with a clear period in its filename.

Common Mistakes

Comparing different periods: A monthly budget beside year-to-date actuals produces a misleading variance. Filter actual transactions to the exact budget period and state the dates in the worksheet.

Mixing up the subtraction order: The guide uses actual minus budget. If you instead calculate budget minus actual, the signs reverse. Keep one convention throughout the workbook and explain what positive and negative mean.

Assuming every positive variance is bad: For expense categories, actual above budget often signals overspending. For income or revenue, actual above budget can be favorable. Interpret the sign according to the type of line item.

Dividing by a zero budget: A percentage variance cannot be calculated against zero. Avoid replacing the error with an arbitrary percentage; use a blank or a clear label such as “N/A,” then review why the category has no budget.

Using text instead of numbers or dates: Entries such as “$2,000” typed as text may not add correctly, and text-formatted dates may be excluded from date-based summaries. Use numeric values and apply formatting, and confirm that dates are recognized by Excel.

Leaving out missing categories or transactions: A total can look plausible even when a transaction has no category or a budget line is missing. Compare source totals with the summary, and include an uncategorized line or exception check during cleanup.

Tips

  • Convert a manually maintained comparison range into an Excel Table using Insert > Table. Tables make headers easier to filter and help formulas extend when you add rows. On Windows, Ctrl+T opens the Create Table dialog; on Mac, use Command+T.
  • Keep raw transactions on a separate sheet from the comparison. This makes it easier to refresh or audit the source without overwriting formulas in the summary.
  • Use the same category list for budgets and actuals. A dropdown based on a maintained category list can reduce spelling differences and make future comparisons more reliable.
  • Show dollars and percentages together. A 50% variance might be only $10 on a small budget, while a 5% variance could be a substantial amount on a large one.
  • Choose a materiality threshold before reviewing results—for example, investigate variances over a chosen dollar amount or percentage. The right threshold depends on the purpose and scale of the workbook.
  • For several periods, create one comparison per month or a month-by-month layout, rather than combining everything into one unexplained total. Make sure formulas and labels clearly identify each period.

Troubleshooting

The formula shows a formula error: Check that the formula begins with an equals sign, cell references are correct, and parentheses are balanced. Excel normally uses commas between arguments in Microsoft 365 on Windows. If your regional settings use a different list separator, Excel may expect semicolons; use the separator your installation recognizes.

The formula displays as text instead of calculating: The cell may be formatted as Text, or the formula may have an apostrophe before the equals sign. Change the cell format to General, select the formula cell, press F2, and then press Enter. On a Mac, use the equivalent edit-and-confirm keys for your keyboard setup.

The total is smaller than expected: Look for numbers stored as text, filtered-out source rows, blank category labels, and transactions outside the date window. In a SUMIFS setup, confirm that the date column contains actual dates and that every criteria range covers the same rows as the amount range.

The variance signs seem backwards: Confirm whether the worksheet uses actual minus budget or budget minus actual. Then interpret the sign by category type. If the workbook must show “favorable” consistently across both revenue and expenses, add a separate explanation or category-aware calculation rather than silently changing the subtraction order for selected rows.

The percentage is blank: The example formula deliberately returns a blank when the budget is zero. Check the budget cell and decide whether the line should remain not applicable, receive a valid budget, or be explained separately. Do not confuse a blank result with a zero variance.

The conditional formatting colors unexpected rows: Select the intended variance range and open Home > Conditional Formatting > Manage Rules. Check the rule’s range and condition, and remember that a generic rule for positive values does not know whether a row represents revenue or an expense.

Frequently Asked Questions

What is the variance formula for actual vs budget in Excel?
Use actual minus budget. If actual is in C2 and budget is in B2, enter =C2-B2. A positive result means actual is higher than budget; whether that is favorable depends on the category.

How do I calculate the percentage variance?
Divide the dollar variance by the budget. If the variance is in D2 and budget in B2, use =IF(B2=0,"",D2/B2), then format the result as a percentage. A zero budget has no meaningful percentage comparison.

Should a favorable variance be positive or negative?
There is no single interpretation for every category. Positive actual-minus-budget variance usually means an expense exceeded its budget, but it can mean revenue exceeded its target. State the convention and interpret each kind of line item appropriately.

Can I compare actuals across several months?
Yes. Use separate month columns or repeat the category table for each period, and calculate each month’s actuals using its own date boundaries. Keep monthly budgets matched to monthly actuals; do not compare a single month’s plan with a cumulative actual total.

Can Excel update actuals from a transaction list?
Yes. SUMIFS can total amounts by category and date range, as shown above. Ensure the source ranges include new transactions as they are added; using an Excel Table as the source can make the ranges easier to maintain.

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.