Cellwise Try it free

Excel guide · 3 min read

Formulas for a monthly budget in Excel

Quick answerA working monthly budget needs four formulas: Remaining =C2-D2 (budget minus actual) for each category, a total with =SUM(D2:D10), money left with =F2-SUM(D2:D10) (take-home pay minus spending), and group totals with =SUMIF(B2:B10,"Wants",D2:D10). Everything else is built from these.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABCDEF
1CategoryGroupBudgetActualTake-home pay
2RentNeeds145014504200
3GroceriesNeeds520586.4
4UtilitiesNeeds180164.25
5TransportNeeds220198
6Dining outWants200263.8
7SubscriptionsWants4558.97
8Fun moneyWants150120
9Emergency fundSavings300300
10RetirementSavings250250

Remaining in each category

Subtract actual from budget. A positive number is money still available; a negative one is overspending. Groceries were budgeted at $520 and came in at $586.40.

Pick one sign convention and stick to it. Budget minus actual (positive is good) is the easiest to read. Next to it, =D3/C3 formatted as a percentage shows how much of the category you have used, so 113% jumps out more than -66.40 does.

=C3-D3

Result: -66.4

Groceries are $66.40 over budget, shown as -66.4.

What is left from your pay

Total the Actual column and subtract it from take-home pay in F2. Do the same with the Budget column to see whether the plan itself fits your income before the month starts: here the plan uses $3,315 of $4,200, leaving $885 unassigned.

Use take-home pay, the amount that actually arrives, not your salary before tax.

=F2-SUM(D2:D10)

Result: 808.58

Spending totals $3,391.42, so $808.58 of the $4,200 is left.

Totals by group with SUMIF

Give each category a group (Needs, Wants, Savings) in column B. SUMIF adds the Actual amounts only where the group matches. Put the group names in cells and refer to them, for example =SUMIF($B$2:$B$10,H2,$D$2:$D$10), so one formula copies down a small summary table.

The group label must match exactly. "Want" or "Wants " (with a trailing space) is a different group, and SUMIF quietly leaves it out. A drop-down list (Data > Data Validation > List) stops typos.

=SUMIF(B2:B10,"Wants",D2:D10)

Result: 442.77

Dining out, Subscriptions and Fun money add up to $442.77.

Check the 50/30/20 split

The 50/30/20 rule of thumb puts about 50% of take-home pay on needs, 30% on wants and 20% on savings. Divide each group total by income and format as a percentage.

Here needs take 57%, which is common for renters in expensive cities. The rule is a guide, not a target: if needs are high, the useful question is which wants or savings to trim so the total still fits.

=SUMIF(B2:B10,"Needs",D2:D10)/F2

Result: 0.57110714286 shows as 57% with Percent format

Needs add up to $2,398.65, about 57% of take-home pay.

Count and total the overspending

COUNTIF cannot compare two columns row by row, but SUMPRODUCT can. (D2:D10>C2:C10) gives TRUE or FALSE for each row; the double minus turns them into 1 and 0, and SUMPRODUCT adds them up.

To get the overspend in dollars, multiply the same test by the difference: =SUMPRODUCT((D2:D10>C2:C10)*(D2:D10-C2:C10)) returns 144.17. Under-spending in other rows does not cancel it out, which is what you want for spotting leaks.

=SUMPRODUCT(--(D2:D10>C2:C10))

Result: 3

Groceries, Dining out and Subscriptions are over budget, so the count is 3.

Fill Actual from a transactions list

Typing totals by hand is where budgets drift. Paste your bank export on a second sheet called Transactions (date in A, category in B, amount in C) and let SUMIFS add each category for the month: =SUMIFS(Transactions!C:C,Transactions!B:B,A2,Transactions!A:A,">="&DATE(2026,11,1),Transactions!A:A,"<"&DATE(2026,12,1)).

The "<" first-of-next-month test catches every transaction on the 30th, whatever time it carries. If the result is 0, the bank amounts are probably text or the category names differ; see the SUMIF troubleshooting guide. For a ready-made sheet with these formulas, download the free monthly budget template at /templates/monthly-budget-template/.

Common mistakes

Adding a category below the last row

SUM(D2:D10) does not grow by itself. Insert new rows inside the list, or turn it into a table with Ctrl+T so totals expand automatically.

Mixing signs

If some expenses are entered as negatives (as bank exports often do), totals shrink. Keep all expenses positive in a budget, or wrap the import in ABS.

Hard-coding income into formulas

Keep take-home pay in one cell (F2) and reference it, so a pay rise updates every percentage at once.

FAQ

What formulas do I need for a budget in Excel?

SUM for totals, subtraction for remaining amounts, SUMIF or SUMIFS for totals by category or month, and IF to flag categories that went over. SUMPRODUCT helps for counting overspends across two columns.

How do I calculate money left over in an Excel budget?

Subtract total spending from income: =F2-SUM(D2:D10), where F2 holds take-home pay and column D holds actual spending.

How do I track spending by category in Excel?

List transactions with a category column, then use =SUMIF(category_range,"Groceries",amount_range), or SUMIFS when you also need a date range.

Is there a free Excel budget template?

Yes. The Cellwise monthly budget template has budget vs actual, over-budget flags, group totals and a savings rate already set up, and works in Excel and Google Sheets.

Functions used

Related guides

Updated