Build a Monthly Budget in Excel
By ExcelReady TeamUpdated 6 min read
The short answer
To build a monthly budget in Excel, list your income and total it with SUM. List each spending category with a Budget column and an Actual column, then subtract to get the difference. Total both columns, and subtract total spending from total income to see what is left. SUMIF can fill the Actual column from a list of purchases.
Key takeaways
- Use three columns per category: Budget (the plan), Actual (what you spent) and Difference.
- Money left over is total income minus total actual spending: one SUM inside a subtraction.
- Keep a spending log and let SUMIF add each category, so you never total receipts by hand.
- Lock the income total with $ when you work out each category's share of income.
- Copy the sheet each month and clear only the Actual numbers; the formulas stay.
On this page+
- What the monthly budget will look like
- Step 1: List your income and total it
- Step 2: Set up spending categories with Budget and Actual
- Step 3: Calculate the difference for each category
- Step 4: Total the columns and find the money left over
- Step 5: Fill the Actual column from a spending log with SUMIF
- Step 6: Show each category's share of income
- How to reuse the budget every month
- Practise it
A monthly budget in Excel needs only a few formulas: SUM for your totals, a subtraction for each category's difference, and SUMIF to add up spending from a list of purchases. This guide walks you through a complete, small budget you can type in 15 minutes. It is aimed at beginners, and the same skills appear in office jobs that track department spending.
What the monthly budget will look like#
The finished sheet has three parts: income at the top, a category table in the middle, and a spending log on a second sheet.
| Part | What it holds | Main formula |
|---|---|---|
| Income | Salary and any other money in | SUM |
| Categories | Budget, Actual and Difference for each spending area | Subtraction, SUM |
| Summary | Total spent and money left over | SUM inside a subtraction |
| Spending log | One row per purchase: date, category, amount | Feeds SUMIF |
If you've never typed a formula before, start with how to write your first Excel formula. Every formula below begins with =.
Step 1: List your income and total it#
Put each source of income on its own row, then add them with SUM.
| A | B | |
|---|---|---|
| 1 | Income | Amount |
| 2 | Salary | 3,200.00 |
| 3 | Side job | 450.00 |
| 4 | Total income | =SUM(B2:B3) |
The total is 3,650.00. On Windows, click B4 and press Alt+= to let AutoSum write the formula; on a Mac, use ⌘⇧T. The guide to the Excel SUM function covers ranges, commas and AutoSum in more detail.
Step 2: Set up spending categories with Budget and Actual#
Each category gets a planned amount (Budget) and the real amount (Actual). Put this table below the income, starting in row 7:
| A | B | C | D | |
|---|---|---|---|---|
| 7 | Category | Budget | Actual | Difference |
| 8 | Rent | 1,200.00 | 1,200.00 | 0.00 |
| 9 | Groceries | 400.00 | 436.75 | -36.75 |
| 10 | Transport | 150.00 | 122.40 | 27.60 |
| 11 | Utilities | 180.00 | 171.20 | 8.80 |
| 12 | Phone and internet | 60.00 | 60.00 | 0.00 |
| 13 | Eating out | 120.00 | 158.50 | -38.50 |
| 14 | Savings | 500.00 | 500.00 | 0.00 |
| 15 | Other | 100.00 | 64.30 | 35.70 |
| 16 | Total | 2,710.00 | 2,713.15 | -3.15 |
Keep categories broad. Eight to twelve rows are easier to keep up with than forty.
Step 3: Calculate the difference for each category#
The difference shows how far each category is from the plan. In D8, subtract the actual from the budget:
=B8-C8Fill it down to D15: select D8:D15 and press Ctrl+D (Windows) or ⌘D (Mac). A positive number means you spent less than planned. A negative number, like Groceries at -36.75, means you overspent.
To show a word instead, add a status column in E8:
=IF(C8>B8,"Over","OK")Groceries and Eating out show Over; every other row shows OK.
Step 4: Total the columns and find the money left over#
Total each column with SUM, then subtract total spending from total income.
| Cell | Formula | Result |
|---|---|---|
| B16 | =SUM(B8:B15) | 2,710.00 |
| C16 | =SUM(C8:C15) | 2,713.15 |
| D16 | =SUM(D8:D15) | -3.15 |
| B18 (left over) | =B4-C16 | 936.85 |
Write B16 first, then select B16:D16 and press Ctrl+R (Mac: ⌘R) to fill it right.
Check your work: the total difference (-3.15) should equal the total budget minus the total actual (2,710.00 − 2,713.15). If it doesn't, one of the rows is missing from a range.
You can also skip the total cell and write the left-over amount as one formula: =B4-SUM(C8:C15). That is the shape of the exercise on this page.
Step 5: Fill the Actual column from a spending log with SUMIF#
Typing totals into the Actual column means adding up receipts by hand. A spending log is faster and leaves a record. Add a second sheet called Log:
| A | B | C | |
|---|---|---|---|
| 1 | Date | Category | Amount |
| 2 | 3/2/2025 | Groceries | 84.30 |
| 3 | 3/5/2025 | Transport | 45.00 |
| 4 | 3/9/2025 | Groceries | 112.45 |
| 5 | 3/12/2025 | Eating out | 38.50 |
| 6 | 3/16/2025 | Groceries | 96.20 |
| 7 | 3/23/2025 | Groceries | 143.80 |
Back on the budget sheet, replace the typed Actual for Groceries in C9 with SUMIF:
=SUMIF(Log!$B$2:$B$200,A9,Log!$C$2:$C$200)SUMIF checks the Category column of the log, keeps the rows that match the category name in A9, and adds their amounts. The four grocery rows give 436.75. The ranges run to row 200 so new purchases are counted, and the $ signs keep them fixed when you fill the formula down the other categories. SUMIF has more examples, including criteria like ">100".
The category names must match exactly. "Grocery" in the log won't be counted under "Groceries", so a drop-down list of categories in the log helps.
Step 6: Show each category's share of income#
A share column shows where the money goes. In F8, divide the actual by total income, and lock the income cell with $ so it doesn't move when you fill down:
=C8/$B$4Format column F as a percentage with Ctrl+Shift+% (Mac: Control+Shift+%). Rent shows 32.9% of income. To flag overspending in color, select C8:C15 and use Home > Conditional Formatting > New Rule with the formula =C8>B8.
How to reuse the budget every month#
Right-click the sheet tab, choose Move or Copy, tick Create a copy and rename it for the new month. Clear the typed Actual amounts (or the log rows) and keep every formula. The totals and differences update as soon as you enter the new month's numbers. The SUM function guide shows more ways to total a range if your layout grows.
The same layout, planned against actual with a difference column, is how departments track budgets at work. That makes this project good practice for the Excel skills admin jobs ask for. Next, try a simple sales report or an attendance tracker to practise the same formulas on different data.
Practise it#
Do the exercise on this page to work out the money left over. Then practise a department budget in a live spreadsheet, with totals, variances and shares of spending, and review SUM and AutoSum if any of the totals felt slow.
Try it yourself
This is a live spreadsheet. Type the formula in the green cell and press Enter.
Income for the month is in B8. In B9, work out the money left over: the income minus the total of the Actual column, C2:C6. Use SUM inside the formula.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Actual | |||
| 2 | ||||
| 3 | ||||
| 4 | ||||
| 5 | ||||
| 6 | ||||
| 7 | ||||
| 8 | ||||
| 9 | Left over | |||
| 10 | ||||
| 11 | ||||
| 12 |
Tip: Start with the income cell, subtract, then SUM the five actual amounts: =B8-SUM(C2:C6).
Practise this in a lesson
Frequently asked questions
Does Excel have a monthly budget template?
Yes. In Excel, choose File > New and search for "budget" to see Microsoft's templates. Building your own takes about the same time and you will understand every formula in it.
Should the difference be budget minus actual or actual minus budget?
Either works if you are consistent. Budget minus actual gives a positive number when you spent less than planned; actual minus budget gives a positive number when you overspent, which is how many finance teams report a variance.
How do I track spending automatically?
Type each purchase into a log with a date, category and amount. A SUMIF formula on the budget sheet then adds up every purchase for each category, so the Actual column updates as you add rows.
Should savings go in my budget?
Many people list savings as a category, like rent, so the money is planned before it is spent. Then the money left over is what is truly free to spend or save extra.