Spreadsheets

Budget vs actual formula for a monthly budget in Excel

Build a three-column budget vs actual sheet in Excel: type the budget, pull actual spend with SUMIFS from your expense log and show the difference.

Editorial illustration for Budget vs actual formula for a monthly budget in Excel

A budget vs actual sheet has three columns per category: Budget, which you type; Actual, which SUMIFS pulls from your expense log for the chosen month; and Difference, which is Budget minus Actual. A positive difference means you are under budget; a negative one means you are over. One row per category and one cell for the month is all it needs.

Set up the sheet

Assume your expense log is on a sheet named Log, with dates in column A, categories in B and amounts in C, rows 2 to 500. On a Budget sheet, type the first day of the month in B1, such as 01/10/2025. From row 4 down, put the headings Category, Budget, Actual, Difference and % used in A3 to E3, then list the categories in column A and type the budget amounts in column B.

What is the Actual formula?

In C4:

=SUMIFS(Log!$C$2:$C$500,Log!$B$2:$B$500,$A4,Log!$A$2:$A$500,">="&$B$1,Log!$A$2:$A$500,"<"&EDATE($B$1,1))

It adds the amounts for the category named in A4 with a date from the first of the month up to, but not including, the first of the next month. Copy it down. In D4, the difference: =B4-C4. In E4, the share of the budget used: =IF(B4=0,"",C4/B4), formatted as a percentage. The IF stops a divide-by-zero error for a category with no budget.

A worked example with invented figures

CategoryBudgetActualDifference% used
Groceries₹12,000₹10,450₹1,55087%
Fuel₹3,000₹3,420-₹420114%
Dining out₹4,000₹2,900₹1,10073%
Utilities₹5,500₹5,150₹35094%
Total₹24,500₹21,920₹2,58089%

Checking the totals: budgets 12,000 + 3,000 + 4,000 + 5,500 = 24,500; actuals 10,450 + 3,420 + 2,900 + 5,150 = 21,920; difference 24,500 minus 21,920 = 2,580, which also equals 1,550 - 420 + 1,100 + 350. The total row uses SUM, and its percentage is 21,920 divided by 24,500, about 89 percent, not the average of the row percentages.

Notice what the total hides: overall you are ₹2,580 under budget, but fuel is ₹420 over. Budget sheets earn their keep at category level.

Why is the sign Budget minus Actual?

Because it reads naturally as money left. Positive means there is room; negative means over. If you reverse it to Actual minus Budget, positive means overspend, which is also valid (finance teams often call it a variance), but pick one and say so in the heading, for example Left (Budget - Actual). Mixing the two across sheets causes real confusion.

How do I read the sheet at a glance?

Scan the Difference column for negatives first, then the % used column for anything above 90 percent. Those are the rows that need a decision this month. Rows far below budget are worth a second look too: either the budget is padded, or a bill has not been logged yet. A quick way to tell is to compare the Actual cell with a manual filter of the log for that category.

Can I change the month?

Yes. Change B1 to 01/11/2025 and every Actual updates. If you want the sheet to follow the calendar by itself, set B1 to =DATE(YEAR(TODAY()),MONTH(TODAY()),1). Before you change the month, copy the sheet or paste the finished month's numbers as values, so you keep a record of what you spent.

How do I handle irregular expenses such as annual insurance?

Divide the annual cost by twelve and budget that amount every month, then log the actual payment in the month it leaves your account. The month it is paid will show a large overspend and the other months an underspend. That is expected, and it is the reason some people keep a separate sinking-fund row. Whatever you choose, keep the rule consistent.

Checklist

  • Dates in the log are real dates.
  • Category names in the log and the sheet match exactly.
  • The sign convention is written in the heading.
  • The total row uses SUM, not a sum of percentages.

If typing the log is the slow part, SpendixAI has budgets on Pro and imports statements you upload, so actuals are not entered by hand.

SPENDIXAI JOURNAL

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

Explore your reports