Spreadsheets

Budget vs actual sheet in Excel with a variance column

Build a budget vs actual sheet in Excel with twelve monthly columns, a variance column and a flag for categories that run over, with a groceries example.

Editorial illustration for Budget vs actual sheet in Excel with a variance column

A budget vs actual sheet puts your planned spending and real spending side by side for each category and month, then subtracts one from the other to get the variance. A percentage-over column with conditional formatting highlights the categories that keep overshooting. Three tabs, Budget, Actual and Variance, with identical layouts, keep it simple.

How should I structure the workbook?

Make three tabs with the same shape: categories in A2 downward, and the twelve months of the financial year across B to M, with April in B and March in M. Put the first day of each month in row 1, for example 1-Apr-2026 in B1 and the following months to the right, formatted as mmm. Column N holds the year total.

  • Budget: type your planned amount for each category and month.
  • Actual: formulas that pull spending from an Expenses tab.
  • Variance: formulas comparing the two, with a percentage block below.

Which formula fills the Actual tab?

Keep an Expenses tab with Date in A, Category in B and Amount in C. Then in Actual!B2:

=SUMIFS(Expenses!$C$2:$C$3000, Expenses!$B$2:$B$3000, $A2, Expenses!$A$2:$A$3000, ">="&B$1, Expenses!$A$2:$A$3000, "<"&EDATE(B$1,1))

Fill it across and down. Each cell now adds up the category's expenses in that month. The dollar signs make the category column and the month row stay fixed as you copy.

How do I calculate variance and the percentage over?

  • Variance in Variance!B2: =Actual!B2-Budget!B2, where a positive number means you spent more than planned
  • Percentage over: =IF(Budget!B2=0, "", Actual!B2/Budget!B2-1)
  • Year total in N2: =SUM(B2:M2) on each tab

Format the percentage cells as percentages. For the flag, select the percentage block, choose Conditional Formatting, then New Rule, then Use a formula, and enter =AND(ISNUMBER(B2), B2>0.1) with a red fill. That marks any category more than ten percent over; change the 0.1 to whatever tolerance you want.

A groceries example with made-up months

Say groceries are budgeted at ₹10,000 a month and the actuals for four months are as follows.

MonthBudgetActualVarianceOver by
April₹10,000₹11,200+₹1,20012%
May₹10,000₹11,300+₹1,30013%
June₹10,000₹11,100+₹1,10011%
July₹10,000₹11,400+₹1,40014%

The four months total ₹45,000 against ₹40,000 planned, a gap of ₹5,000, or 12.5 percent. One bad month would not tell you much. Four months in a row, each between 11 and 14 percent over, is a pattern: the budget, not the discipline, is probably wrong.

What should I do about a pattern like that?

  1. Ask what changed since you set the number: prices, guests, a bigger family, more cooking at home.
  2. Look at the Expenses tab for that category and find the biggest lines.
  3. Either raise the budget to something realistic, say ₹11,200, and take the extra from another category, or pick one habit to change.
  4. Re-run for two more months before changing the figure again.

What about categories that do not fit neatly?

Keep the category list short, around ten to fifteen lines, or the monthly columns become hard to scan. Put one-off costs such as a wedding gift or a repair under a single Irregular line so they do not distort the regular ones, and look at that line separately. Where one purchase covers two needs, such as a pharmacy bill with groceries, pick the larger part and move on. Consistency across months matters more than perfect categorisation.

A monthly routine

  • Enter or import the month's expenses before looking at the sheet.
  • Check the Variance tab for red cells.
  • Write one line about each red cell in a Notes column.
  • Adjust the next month's budget only where there is a clear reason.

If typing every expense is the slow part, SpendixAI can import bank and card statements and categorise them, and its reports can show spending by category for comparison with these budgets.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports