Beginner project: a monthly budget tracker
Build a complete monthly budget tracker with planned and actual columns, differences, a summary and a chart, using everything from lessons 1 to 9.
In this lesson you will
- Set up a budget sheet
- Write difference, share and total formulas
- Build a summary and a chart
- Check your work in three ways
About 20 minutes
Your first real spreadsheet
Nadia earns 30000 a month and wonders where it all goes. She has tried apps, but they never show things the way she thinks about them. In this project you will build the budget tracker she needs, using everything from lessons 1 to 9: data entry, formulas, functions, references, formatting and a chart. The numbers here are examples; replace them with your own.
What you will build
- An income cell and a table of expenses with Planned and Actual columns.
- A Difference column that shows how far each category is over or under plan.
- Totals for planned, actual and difference.
- A summary of what is left, savings percentage, the biggest expense and the biggest overspend.
- A chart of actual spending by category.
Step 1: set up the sheet
| Cell | Content |
|---|---|
| A1 | Monthly income |
| B1 | 30000 |
| A3 to D3 | Category, Planned, Actual, Difference |
| E3 | Share of income |
| A4 to A9 | Rent, Food, Transport, Utilities, Phone, Fun |
| A10 | Total |
Step 2: enter the data
Type your planned and actual amounts into B4:C9. The example numbers are shown below, with the real results of the Difference formula (=B4-C4, copied down). A negative difference means you spent more than planned:
| Category | Planned | Actual | Difference |
|---|---|---|---|
| Rent | 9000 | 9000 | 0 |
| Food | 6000 | 6800 | -800 |
| Transport | 2000 | 1700 | 300 |
| Utilities | 1800 | 1950 | -150 |
| Phone | 600 | 600 | 0 |
| Fun | 2500 | 3200 | -700 |
Step 3: formulas
| Cell | Formula | Purpose |
|---|---|---|
| D4 | =B4-C4 | Planned minus actual, copied down to D9 |
| E4 | =C4/$B$1 | Share of income, copied down; income is locked |
| B10 | =SUM(B4:B9) | Total planned; copy to C10 |
| D10 | =B10-C10 | Total difference |
With the example data the totals came out as follows (really calculated): planned 21900, actual 23250 and difference -1350.
Step 4: the summary
| Cell | Formula | Example result |
|---|---|---|
| B12 (left over) | =B1-C10 | 6750 |
| B13 (savings %) | =B12/B1 | 22.5% |
| B14 (biggest expense) | =MAX(C4:C9) | 9000 |
| B15 (biggest overspend) | =MIN(D4:D9) | -800 |
| B16 (categories) | =COUNT(D4:D9) | 6 |
Format B13 and E4:E9 as percentages. The overspend is the most negative difference, so MIN finds it. Nadia sees at once that Fun and Food are over plan while Transport is under.
Step 5: make it readable
- Make the header rows bold with a light fill.
- Format money cells with commas and no decimals; percentages with one decimal.
- Widen columns and freeze the top rows.
- Use a clear sheet name, such as Budget Mar 2026.
Step 6: add a chart
Select A3:A9 and C3:C9 (hold Ctrl to select the two blocks), then Insert a Column chart. Give it a title that states a finding, label the axis and sort the bars in the table if you wish. Because the chart is linked, it updates when you change a number.
Step 7: check your work
- Estimate: do the totals look right? Add the Actual column roughly in your head.
- Cross-check: add the Difference column with SUM and compare with D10.
- Press Ctrl+` and look for typed numbers among the formulas.
- Change one Actual figure and watch every total and the chart follow.
- Save as budget-tracker-v1.xlsx and make a copy as a backup.
Stretch goals
- Copy the sheet for next month (right-click the tab, Move or Copy, Create a copy).
- Add a Notes column explaining big differences.
- Add a savings goal cell and show how close you are.
- Try the same project in Google Sheets and compare.
Where you are now
You can enter data cleanly, calculate with formulas and functions, lock references, format, sort, filter, chart and check your work. That covers the foundations that most office spreadsheets rely on. The next level of this course covers decision formulas such as IF, lookups, text and date functions and PivotTables, which let you summarise thousands of rows in seconds.
With the example numbers your results should match the tables above: totals of 21900 planned and 23250 actual, a difference of -1350 overall, and 6750 left from an income of 30000. If yours differ, compare your formulas with the ones in step 3; the usual culprit is a missing $ on the income cell.
Entering Planned and Actual as text with currency symbols. Type plain numbers and format them, so the formulas and the chart can use them.
Reflect on what the numbers say
A budget is useful only if it changes a decision. Look at your Difference column. Which categories are over plan month after month? Is the plan unrealistic, or is the spending the problem? Perhaps Food needs a higher planned number, or Fun needs a limit. The spreadsheet shows the facts; you decide what to do with them. Adjust the plan after two or three months of real data, not before.
Sharing your tracker
Before sharing, remove private notes, check that no sensitive details such as account numbers are hidden in cells or other sheets, and save a copy under a new name. If you share in Excel for the web or Google Sheets, choose view-only access unless someone really needs to edit. A good habit is to keep the master file for yourself and send a copy.
Check what you learned
Five quick questions. Pick an answer and you will see straight away whether it is right and why.
Why is B1 locked ($B$1) in the share formula?
- Every row divides by the same income
- To hide it
- To make it text
- To sort it
The income must not slide when copied.
A Difference of -800 means…
- You saved 800
- You spent 800 more than planned
- Nothing
- A date problem
Planned minus actual is negative when over plan.
Which function finds the biggest overspend here?
- MAX on the Planned column
- COUNT
- ROUND
- MIN on the Difference column
The most negative difference is the largest overspend.
How do you check the totals a second way?
- Delete a row
- Sum the Difference column and compare
- Merge cells
- Sort the sheet
Two routes should agree.
Why type plain numbers without currency symbols?
- To save space
- To change colours
- To lock the sheet
- So formulas and charts can use them
Symbols belong in the number format.
Summary
- A good sheet separates inputs, calculations and summary.
- Planned minus actual shows over and under spending.
- Lock the income cell when copying share formulas.
- Check with estimates, cross-checks and Ctrl+`.
Questions
Can I use this tracker for a business?
Yes. Change the categories and use income as sales. For business accounting, check local rules and consider advice from an accountant.
How do I start a new month?
Right-click the sheet tab, choose Move or Copy, tick Create a copy, then clear the Actual column and update the month name.
Will it work in Google Sheets?
Yes. All formulas used here work in Google Sheets with the same names.
