Capstone — Personal Budget Tracker

Every chapter in this course taught one tool in isolation — a reference type, a function, a lookup, a chart. A real workbook never uses just one of them; it stacks all of them together to answer one practical question. This capstone builds a genuine Personal Budget Tracker from scratch, and at each step, names exactly which earlier chapter the technique being used came from — so by the end, the whole course reads as one connected skill rather than nine separate ones.

The Project Brief

The tracker needs to: log individual spending transactions as they happen; know a monthly budget limit per category; automatically flag any category that's gone over budget; and show a clear visual summary of where the money actually went. Three worksheets will do this: Transactions (the raw log), Budget (the fixed monthly limits per category), and Dashboard (the summary, chart, and PivotTable).

Step 1 — Setting Up the Workbook Structure

Create the three worksheets by right-clicking a sheet tab → Insert, renaming each one via double-click on its tab. This is Chapter 1's workbook → worksheet hierarchy in direct practice: keeping raw data, fixed reference values, and summary output in separate worksheets of the same workbook, rather than crowding everything onto one sheet.

Step 2 — The Transactions Table

On the Transactions sheet, enter headers Date, Category, Description, Amount in row 1, log a few weeks of real transactions below, then select the whole range and press Ctrl+T to convert it into an Excel Table — name it Transactions via Table Design. From Chapter 7: every new transaction typed into the row directly below the table becomes part of it automatically, and any formula built from Transactions[Amount] grows with it — no manual range updates as the month goes on.

Step 3 — The Budget Reference Table

On the Budget sheet, list every spending category once, with its fixed monthly limit — Groceries/400, Transport/150, Entertainment/100, and so on. Convert this to an Excel Table too, named Budget. This table is deliberately small and static — it's the fixed reference data every other formula in this workbook will look values up from, the same conceptual role Chapter 3's fixed tax-rate cell played, just organised as a proper lookup table instead of a single cell.

Step 4 — Looking Up Each Category's Budget

On the Dashboard sheet, list every category again, then use Chapter 5's XLOOKUP to pull each one's limit straight from the Budget table rather than retyping it:

If a new spending category ever appears in Transactions before a limit has been added to Budget, this formula shows "No budget set" instead of an error — Chapter 5's if_not_found argument doing exactly the job it was designed for.

Step 5 — Summarizing Actual Spend with a PivotTable

Rather than writing a formula to total each category's spending by hand, build a PivotTable (Chapter 9) from the Transactions table: Category in Rows, Amount in Values (defaulting to Sum, since Amount is a genuine numeric column). This single PivotTable becomes the "Actual Spend" figure for every category at once, and a right-click Refresh after logging new transactions is all that's needed to bring it current — Chapter 9's own refresh rule applies here exactly as written.

Step 6 — Flagging Overspending with IF

With each category's Budget limit (Step 4) and Actual spend (Step 5) sitting side by side on the Dashboard, a simple Chapter 4 IF flags the result:

Step 7 — Highlighting Overspending Automatically

A formula-based conditional formatting rule (Chapter 6) applied to the whole Dashboard row range highlights any row where spending has gone over budget, using the exact same top-left-cell-and-$-locking logic from Chapter 3:

Step 8 — Visualising Spending with a Chart

A column chart (Chapter 8) comparing Budget vs. Actual per category — not a pie chart, since the goal is a precise category-by-category comparison, exactly the case Chapter 8 argued a bar/column chart handles far more reliably than a pie chart's angle-based comparison. The chart's data selection deliberately excludes any PivotTable grand-total row, avoiding Chapter 8's own warning about a totals row visually dwarfing the real category bars.

Putting It All Together

Every piece of this workbook traces back to a specific earlier chapter — nothing in this capstone is a new technique, only a new combination of ones already covered:

Hands-On Exercises