Budget vs. actual in Excel: how to analyze variances

Building a budget is the easy part. The hard part comes at month end, when actual spending doesn’t match the plan and you have to explain why. A well-built Excel sheet tells you in seconds which line items are off, by how much, and whether the gap is worrying or just noise.

In this guide you’ll set up a budget vs. actual table, calculate the variance in amount and in percentage, summarize expenses by line item with SUMIFS, automatically highlight anything beyond a threshold and avoid the mistakes that most distort the analysis.

Set up the table: budget and actual side by side

Variance analysis only works if each line item appears once, with the same name in the budget and in the expense log. Before writing any formulas, organize the sheet like this:

1. In column A, the name of each line item (Rent, Marketing, Utilities…), with no repeats.
2. In column B, the budgeted amount for the period.
3. In column C, the actual spending for the same period.
4. Keep columns D and E for the variance in amount and in percentage.
5. Select the range and press Ctrl + T to turn it into a table, so formulas fill down automatically to every new row.

With sample data, the sheet would look like this:

Notice that the total almost matches (only 20 under), yet inside there are line items far off in both directions. That’s why looking at the last row is never enough.

Calculate the variance in amount and in percentage

Absolute variance

This is the difference between what you spent and what you planned. In cell D2, type:

=C2-B2

With this convention, a positive result means you spent more than budgeted, and a negative one means you spent less. What matters is choosing one convention and sticking to it across the whole workbook.

Percentage variance

The amount alone is misleading: 150 over on an 800 line item doesn’t weigh the same as 150 over on a 10,000 one. To compare line items of different sizes, divide the variance by the budget. In E2:

=IF(B2=0,"",D2/B2)

The condition avoids the #DIV/0! error when a line item has no budget. Then apply percentage formatting with Home › Percent Style and add a decimal place if you need it.

Favorable or unfavorable?

The sign doesn’t always mean the same thing. It depends on whether the row is an expense or income:

• Expenses: a positive variance is unfavorable (you overspent) and a negative one is usually favorable, unless it means something planned didn’t happen.
• Income: it’s the other way around. Earning more than planned is favorable; earning less is unfavorable.
• Unbudgeted items: any actual spending is a variance that needs explaining, even if the percentage can’t be calculated.

From expense detail to a summary by line item

Actual spending rarely arrives already summarized. Usually it’s a list of transactions: each invoice or payment in its own row. Suppose a sheet named Expenses with the date in column A, the line item in B, the amount in C and the month in D. To bring the actual total of each line item into the analysis table, enter in C2:

=SUMIFS(Expenses!$C:$C,Expenses!$B:$B,$A2)

If you want to analyze a specific month, type the month in a control cell, for example H1, and add a second criterion:

=SUMIFS(Expenses!$C:$C,Expenses!$B:$B,$A2,Expenses!$D:$D,$H$1)

Change H1 from January to February and the whole table recalculates. SUMIFS has been available since Excel 2007, so it works in virtually any version, including Excel for the web.

To make sure nothing is left out, add up column C of the table and compare it with the total amount on the Expenses sheet. If they don’t match, some transactions have a misspelled line item or one that doesn’t exist in the budget.

Highlight the variances that matter

Not every variance deserves a meeting. Set a threshold, for example 10% above or below, and let Excel classify each line item. In F2:

=IF(E2="","No budget",IF(E2>0.1,"Review",IF(E2<-0.1,"Underused","On track")))

With the sample data, Marketing (18.8%) and Travel (28%) show up as Review, Training (-70%) as Underused and the rest as On track. Utilities, at exactly -10%, stays within the margin because the comparison is strict.

To see it at a glance, add color:

1. Select the values in column E, without the header.
2. Go to Home › Conditional Formatting › Highlight Cells Rules › Greater Than.
3. Type 0.1 and pick a light red fill.
4. Repeat with Less Than, the value -0.1 and a yellow fill, to spot spending that hasn’t happened.

Common mistakes when comparing budget and actual

• Different line item names: “Marketing” in the budget and “Marketing ” with a trailing space in the expenses don’t match, and the sum returns 0. Clean up the text with TRIM or use a drop-down list to enter line items.
• Mismatched periods: comparing the annual budget with three months of spending produces huge, false variances. Prorate the budget or compare year-to-date with year-to-date.
• Amounts stored as text: if expenses come from a CSV or a bank statement, some numbers may end up as text and SUMIFS ignores them. Check that they’re right-aligned.
• Duplicate transactions: an invoice recorded twice inflates actual spending. Before the analysis, check that there are no repeated rows.
• Looking only at the total: as the example showed, an almost perfect total can hide line items that are far off and cancel each other out.

Compare budget versions

Budgets change too: someone adjusts a line item, a new line is added or an amount is corrected mid-year. If you work with several versions of the file, it’s worth knowing exactly what changed before analyzing variances, because part of the gap may come from the budget no longer being the same. With our free tool to compare Excel files you upload the old and new versions and get a file showing the changes. And if actual spending arrives split into one file per month or per department, you can merge all the files into one before applying the formulas.

Conclusion

Variance analysis doesn’t require financial software. All you need is a well-structured table, a subtraction, a division protected against zero and a rule that flags anything beyond your threshold. What really makes the difference is discipline: the same line item names, the same periods and a clear sign convention. With that, every month-end close becomes a review of a few minutes instead of a blind search.

Frequently asked questions