How to Track Budget vs Actual in Excel Step by Step

Why Most Budget Trackers Break Down Mid-Year

You build a tidy spreadsheet in January. By April, someone has pasted actuals over the budget column, the formulas are broken in three rows, and nobody agrees on which version is current. This is not a discipline problem — it is a structure problem. A well-built budget vs actual tracker in Excel prevents most of these failures before they start.

This guide walks you through the exact layout, formulas, and formatting you need to make your tracker reliable for the whole financial year.

Step 1: Get the Column Structure Right from the Start

The single biggest mistake is cramming budget and actuals into adjacent columns without a clear naming convention. Use this structure as your baseline:

• Column A: Cost category or line item
• Column B: Budget (the original approved figure)
• Column C: Actual (what was spent or received)
• Column D: Variance (Actual minus Budget)
• Column E: Variance % (Variance divided by Budget)
• Column F: Notes or owner

If you are tracking by month, repeat columns B through E for each month across the sheet, then add a year-to-date summary section on the right. Keep every month in the same column order — any inconsistency will cause lookup errors later when you start pulling summaries.

Step 2: Write the Core Variance Formulas

The variance calculation sounds obvious, but there are two versions and you need both.

Absolute variance tells you the raw pound difference:

=C2-B2

A negative result means you spent more than budgeted (for a cost line). A positive result means you came in under. Whether negative is good or bad depends on whether the row is income or expenditure — which is worth flagging in a separate column with a simple label.

Percentage variance tells you how significant the overrun is relative to the budget size:

=IF(B2=0,"N/A",(C2-B2)/B2)

The IF(B2=0) wrapper is important. Without it, any row where no budget was set will return a divide-by-zero error and break your sheet. Format column E as a percentage with one decimal place.

A £500 overrun on a £600 budget is a crisis. The same £500 overrun on a £500,000 budget is noise. The percentage column is what lets you triage quickly.

Step 3: Protect the Budget Column

Once the budget is signed off, lock it. In Excel, you can protect individual columns while leaving others editable:

1. Select the entire sheet and go to Format Cells → Protection, then untick "Locked"
2. Select column B (your budget column) and tick "Locked"
3. Go to Review → Protect Sheet and set a password

This means anyone updating actuals cannot accidentally overwrite the baseline. Without this step, it is only a matter of time before someone pastes data into the wrong column and the variance figures become meaningless.

Step 4: Add Conditional Formatting to Flag Variances Automatically

You should never have to scan a column of numbers manually to find the problem rows. Conditional formatting does that for you.

Select your variance column (D) and add these rules:

• Red fill when the value is less than -[your threshold] — for example, <-500 for a cost line
• Amber fill for a mid-range warning, such as <-100
• Green fill when the variance is positive (under budget or above income target)

Apply the same logic to the percentage variance column using percentage thresholds — for example, flag anything more than 10% over budget in red. Now when you open the file, problem rows jump out immediately without any analysis required.

Step 5: Build a Summary Tab That Pulls From the Detail

A detail tab per department or project is fine, but decision-makers need a one-page summary. Create a separate tab called "Summary" and use SUMIF or simple cell references to pull totals through:

=SUMIF(Detail!A:A,"Staff Costs",Detail!D:D)

This gives you a single view of total budget, total actual, and total variance across all categories — without anyone having to manually copy figures across. The summary tab should update the moment actuals are entered on the detail tab.

Add the same conditional formatting to the summary tab so the high-level view is just as easy to read at a glance.

Step 6: Use a Dropdown for Status or RAG Rating

For project-level tracking, add a manual RAG (Red, Amber, Green) status column that the budget owner updates each period. This is separate from the calculated variance — it captures qualitative context that a formula cannot, such as "this is overspent but we have a committed credit arriving next month."

Use Data Validation (Data → Data Validation → List) to restrict entries to Red, Amber, or Green. Then apply conditional formatting to colour the cell automatically based on the text value. This prevents people typing "R" or "red" and breaking the formatting.

Where This Gets Harder: Multiple Data Sources

The steps above work well when your actuals live in the same spreadsheet or come from a single export. The process gets messy when actuals come from a finance system, a project management tool, and a payroll export — all with different column names, different date formats, and no shared reference code to join them on.

That is a data merging problem as much as a budget tracking problem. If you are spending an hour every month reformatting and copy-pasting actuals before you can even begin the variance analysis, the tracker itself is not the bottleneck — the data preparation is.

Tools like Smart Data Blender are built specifically for this: pulling actuals from multiple sources, standardising the formats, and delivering a clean combined file your Excel tracker can work with directly — without the manual preparation step.

Common Mistakes to Avoid

• Mixing income and cost rows without a sign convention. Decide upfront whether a positive variance always means "good" or always means "over budget" — and document it at the top of the sheet.
• No version control. Save dated copies at the end of each period. If someone breaks the file mid-month, you need a clean restore point.
• Hiding rows instead of deleting them. Hidden rows still affect SUM ranges and cause confusion. Use a filter or a separate archive tab instead.
• Leaving the notes column empty. A variance without context is just a number. The notes column is where the real story lives — make it part of your monthly update process.

What a Good Budget Tracker Looks Like in Practice

A reliable budget vs actual tracker opens in under a second, shows you immediately which lines are over, by how much, and who owns the explanation. Actuals update it — not someone reformatting a paste job for forty minutes. The budget column does not change after sign-off. And the summary tab means a senior stakeholder can get the picture in thirty seconds without asking anyone to run a report.

That is an achievable standard in Excel, and it does not require macros or specialist tools. It just requires getting the structure right once, protecting it, and keeping the data feeding into it clean.