BeginnerLesson 10 of 305 min read

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

  1. Make the header rows bold with a light fill.
  2. Format money cells with commas and no decimals; percentages with one decimal.
  3. Widen columns and freeze the top rows.
  4. 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.

Project answer

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.

Common mistake

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?

  1. Every row divides by the same income
  2. To hide it
  3. To make it text
  4. To sort it

The income must not slide when copied.

A Difference of -800 means…

  1. You saved 800
  2. You spent 800 more than planned
  3. Nothing
  4. A date problem

Planned minus actual is negative when over plan.

Which function finds the biggest overspend here?

  1. MAX on the Planned column
  2. COUNT
  3. ROUND
  4. MIN on the Difference column

The most negative difference is the largest overspend.

How do you check the totals a second way?

  1. Delete a row
  2. Sum the Difference column and compare
  3. Merge cells
  4. Sort the sheet

Two routes should agree.

Why type plain numbers without currency symbols?

  1. To save space
  2. To change colours
  3. To lock the sheet
  4. 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+`.

Next lesson

Next: Intermediate level, starting with IF and decision formulas.

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.

Progress is saved in this browser.