📊 Lesson 7.1: Project — A Personal Budget & Finance Tracker

What We're Building & Why It Ties Everything Together

Up to now, most lessons introduced one idea at a time — a function here, a Table there, conditional formatting on its own. That's how you learn tools. But real spreadsheets are never one tool; they're a handful of tools working together toward a goal. A budget tracker is the perfect first project because it's genuinely useful, it's built from pieces you already know, and it exercises the first three stages of our course model at once: Data → Formulas → Analysis.

Here's the whole thing in one picture. You'll create a workbook with three worksheets: a Setup sheet holding the master list of categories and your monthly budget targets, a Transactions sheet where every bit of income and every expense gets logged into an Excel Table, and a Dashboard sheet that summarizes everything with formulas and color. Nothing here is decoration — each piece answers a real question: How much did I spend on groceries? Am I over budget this month? How much money do I actually have right now?

Why build it in this order? Because each stage depends on the one before it. Clean categories on the Setup sheet make the dropdowns possible; the dropdowns keep the Transactions data tidy; tidy data makes the SUMIFS totals trustworthy; trustworthy totals make the budget comparison meaningful; and the summary is just those totals arranged for a human to read in five seconds. Build it bottom-up and every step "just works." That dependency chain — clean data first, analysis last — is the single most important habit this project teaches.

Step 1 — Set Up the Workbook & the Categories List

Open Excel (we'll teach on the free Excel for the web — everything in this project works there; the desktop app works identically). Create a new blank workbook and, using the tabs at the bottom, make three worksheets renamed Setup, Transactions, and Dashboard. Right-click a tab to rename it. Save the workbook now (to OneDrive on the web, or Ctrl+S on desktop) and give it a real name like Budget Tracker 2026.

The Setup sheet is the brain of the tracker. Everything else refers back to it, which means when you want to add a category or change a budget target, you change it in exactly one place. Start by typing your master category list and your monthly budget targets. Here is concrete starter data — type it into the Setup sheet exactly as shown, starting in cell A1:

Setup sheet
       A                B              C
1   Category         Type          Monthly Budget
2   Salary           Income        3200
3   Freelance        Income        400
4   Rent             Expense       1100
5   Groceries        Expense       450
6   Utilities        Expense       180
7   Transport        Expense       120
8   Dining Out       Expense       150
9   Subscriptions    Expense       60
10  Savings          Expense       300
11  Other            Expense       100

A few deliberate choices here. Each category has a Type (Income or Expense) so we can total the two sides separately later. "Savings" is listed as an Expense on purpose — money you move to savings leaves your spending account, so treating it as an outgoing keeps the running balance honest (this is the "pay yourself first" idea). And "Other" is a catch-all so no transaction is ever left uncategorized.

Now convert this range into an Excel Table (select any cell in the data and press Ctrl+T, confirming "My table has headers"). Name the Table tblCategories using the Table Design tab. Turning it into a Table means that when you add an eleventh category later, the dropdowns and totals that depend on it will grow automatically — no fixed A2:A11 ranges to update by hand.

Step 2 — Build the Transactions Table with Dropdowns

Switch to the Transactions sheet. This is where daily life gets logged. Set up these headers in row 1 and enter the starter transactions below so you have real data to calculate on. Type this starting in A1:

Transactions sheet
      A            B                 C          D
1   Date        Category          Type       Amount
2   2026-01-01  Salary            Income     3200
3   2026-01-02  Rent              Expense    1100
4   2026-01-03  Groceries         Expense    82.40
5   2026-01-05  Utilities         Expense    176.00
6   2026-01-07  Dining Out        Expense    38.50
7   2026-01-09  Freelance         Income     400
8   2026-01-11  Groceries         Expense    64.15
9   2026-01-12  Transport         Expense    45.00
10  2026-01-15  Subscriptions     Expense    24.99
11  2026-01-18  Groceries         Expense    91.20
12  2026-01-20  Dining Out        Expense    52.75
13  2026-01-22  Savings           Expense    300.00
14  2026-02-01  Salary            Income     3200
15  2026-02-03  Rent              Expense    1100
16  2026-02-04  Groceries         Expense    78.60
17  2026-02-06  Utilities         Expense    169.40
18  2026-02-10  Transport         Expense    60.00
19  2026-02-14  Dining Out        Expense    88.00
20  2026-02-20  Savings           Expense    300.00

Convert this range to a Table too (Ctrl+T) and name it tblTransactions. Make sure the Date column is formatted as a date and the Amount column as currency or a number with two decimals (Home → Number Format). Getting the types right now saves confusing errors later — a date stored as text won't sort or filter properly, and that's a classic beginner trap we flagged back in Module 1.

Add the category dropdown

Typing category names by hand invites typos — "Grocery," "Grocries," "Groceries " with a trailing space — and every typo silently breaks your totals, because SUMIFS matches on the exact text (it ignores capitalization, but not spelling or spaces). The fix is a data-validation dropdown (from Lesson 3.2) that lets you only pick from the master list. Select the whole Category column of the Table (click the column's data area), then go to Data → Data Validation, choose List, and point the source at your categories:

Data ▸ Data Validation ▸ Allow: List
Source:  =tblCategories[Category]

(On Excel for the web, if a structured reference isn't accepted in the
 Source box, point it at the cell range instead, e.g. =Setup!$A$2:$A$11.
 Using the Table column is preferred on desktop because it auto-expands.)

Now every cell in the Category column shows a little arrow, and you can only choose a real category. Do the same for the Type column if you like, with a two-item list Income,Expense typed directly into the Source box. Consistent categories are the foundation the entire analysis stands on — this one step is what makes the SUMIFS totals in the next section reliable.

Step 3 — Total by Category & by Month with SUMIFS

Now the fun part: turning that list of transactions into answers. Go to the Dashboard sheet. We'll build a category summary that reads straight from the Transactions Table. First, list your expense categories down a column and add columns for the actual amount spent and the count of transactions. Type this into the Dashboard sheet starting at A1:

Dashboard sheet — category summary
      A               B            C            D
1   Category        Budget       Actual       # Txns
2   Rent
3   Groceries
4   Utilities
5   Transport
6   Dining Out
7   Subscriptions
8   Savings
9   Other

Pull the Budget figure from the Setup sheet with a lookup, then compute the Actual with SUMIFS and the transaction count with COUNTIFS. Enter these in row 2 and fill down through row 9:

B2  (Budget from Setup)
    =XLOOKUP(A2, tblCategories[Category], tblCategories[Monthly Budget], 0)

C2  (Actual spent on this category — ALL months)
    =SUMIFS(tblTransactions[Amount], tblTransactions[Category], A2)

D2  (How many transactions in this category)
    =COUNTIFS(tblTransactions[Category], A2)

One honest caveat: this Actual adds up all months in the Table, while the Budget on Setup is a monthly figure. The starter data holds January and February, so categories like Rent will read double their budget. That's fine while you learn the formulas; for a true month-by-month comparison, add the month criteria from the next section to C2 and D2 (a pair like tblTransactions[Date], ">="&$H$2 and tblTransactions[Date], "<"&EDATE($H$2,1)), so Actual counts only the month you choose.

Read SUMIFS out loud and it makes sense: "sum the Amount column, where the Category column equals the label in A2." COUNTIFS is the same idea but counts matching rows instead of adding a number. Both take pairs of (range, criterion), and you can stack more pairs to narrow further — which is exactly how we add a month filter next.

Totals by month

To answer "how much did I spend in January vs February?", add month criteria to SUMIFS. The tidiest way is to compare against the first and last day of the month. Off to the side of your dashboard (starting in cell H1, which leaves columns E and F free for the Remaining and Status columns you'll add in Step 4), set up a small monthly block:

Dashboard sheet — monthly totals
      H              I                 J
1   Month          Income            Expenses
2   2026-01-01
3   2026-02-01

I2  (Income in the month starting H2)
    =SUMIFS(tblTransactions[Amount],
            tblTransactions[Type], "Income",
            tblTransactions[Date], ">="&H2,
            tblTransactions[Date], "<"&EDATE(H2,1))

J2  (Expenses in the month starting H2)
    =SUMIFS(tblTransactions[Amount],
            tblTransactions[Type], "Expense",
            tblTransactions[Date], ">="&H2,
            tblTransactions[Date], "<"&EDATE(H2,1))

The trick in the date criteria is joining an operator to a cell with &: ">="&H2 builds the text "on or after Jan 1," and "<"&EDATE(H2,1) builds "before Feb 1" — EDATE(H2,1) just adds one month to the start date. Together they capture exactly one calendar month without you hard-coding day counts. Fill I2:J2 down for February and you have a clean monthly income-vs-expense summary that updates the instant you add a transaction.

Step 4 — Budget vs Actual with IF & Conditional Formatting

Numbers are good; judgement is better. A budget is only useful if it tells you, at a glance, where you're over and where you have room. Add two more columns to your category summary — one that calculates the difference and one that labels it — and then let color do the talking.

Dashboard sheet — add these columns to the category summary
      E                    F (or next free col)
1   Remaining            Status
2   =B2-C2               =IF(C2>B2, "OVER", "OK")

(fill E2:F2 down through row 9)

Remaining  = Budget − Actual  → positive means money left, negative means overspent
Status     = the word OVER when actual spending beats the budget, otherwise OK

The IF here is doing exactly one job: turning a number comparison into a human word. You could get fancier — =IF(C2>B2,"OVER",IF(C2>B2*0.9,"CLOSE","OK")) adds a "CLOSE" warning band when you've used more than 90% of a category's budget — but start simple and add nuance only once the basic version works.

Make it light up with conditional formatting

Now apply conditional formatting (Lesson 5.3) so overspending is impossible to miss. Select the Remaining column (E2:E9), then Home → Conditional Formatting → New Rule. Add two rules:

You can also select the Status column and use a "Text that contains" rule to color the word OVER red. Or, for a lovely visual, apply data bars to the Actual column so each row shows a little in-cell bar proportional to the spend. The point of conditional formatting is that your eye finds the problem before you've read a single number — the sheet highlights itself.

Step 5 — Running Balance & the Summary Block

The last piece answers the most human question of all: how much money do I actually have right now? For that we build a running balance — a column on the Transactions sheet that carries the account total forward row by row. Go back to the Transactions sheet and add a header Balance in column E of the Table.

The logic: income adds to the balance, expenses subtract, and each row starts from the previous row's balance. Because Type tells us the direction, one formula handles both:

Transactions sheet — running balance in column E
E2  (first row — nothing before it, so start from zero)
    =IF([@Type]="Income", [@Amount], -[@Amount])

E3 and down (add or subtract from the row above)
    =E2 + IF([@Type]="Income", [@Amount], -[@Amount])

If your rows are sorted by Date, the last Balance cell is your current
money on hand. Structured refs like [@Amount] mean "this row's Amount".

A quick note on structured references: inside a Table, [@Amount] means "the Amount in this row," which is why the same formula works down the whole column. The running balance depends on the rows being in date order — so sort the Table by Date ascending (Data → Sort, or the Date column's dropdown) before you trust the last balance figure.

The summary block

Finally, assemble a small at-a-glance panel on the Dashboard. This is the "five-second view" — the numbers you'd want if someone woke you at 3am and asked how your finances are doing. Put it near the top of the Dashboard sheet, for example starting at A12:

Dashboard sheet — summary block
      A                       B
12  SUMMARY
13  Total Income          =SUMIFS(tblTransactions[Amount], tblTransactions[Type], "Income")
14  Total Expenses        =SUMIFS(tblTransactions[Amount], tblTransactions[Type], "Expense")
15  Net (Income−Expenses) =B13-B14
16  Current Balance       =INDEX(tblTransactions[Balance], ROWS(tblTransactions[Balance]))
17  Budget Status         =IF(B14>SUM(B2:B9),"OVER budget overall","Within budget")

The clever one is Current Balance: INDEX(column, ROWS(column)) grabs the last value in the Balance column no matter how many rows you add — so your current balance is always live. Format the summary block boldly (bigger font, a border, currency formatting) so it reads as the headline of the whole workbook. That's your finished tracker: data in, formulas doing the thinking, analysis and color telling you the story.

🎯 Project: Build the Full Tracker

You've walked through every piece — now build the whole thing in one sitting, using the starter data above (or your own real numbers, which is even better). Work top to bottom; test each step before moving on. When you're done you'll have a genuinely useful workbook, not a throwaway exercise.

-- Setup: categories table --> Ctrl+T --> name tblCategories
-- Transactions: Ctrl+T --> name tblTransactions

Category dropdown (Data ▸ Data Validation ▸ List):
   Source:  =tblCategories[Category]   (or =Setup!$A$2:$A$11 on web)

Budget for a category (Dashboard B2):
   =XLOOKUP(A2, tblCategories[Category], tblCategories[Monthly Budget], 0)

Actual spent (Dashboard C2):
   =SUMIFS(tblTransactions[Amount], tblTransactions[Category], A2)

Count of transactions (Dashboard D2):
   =COUNTIFS(tblTransactions[Category], A2)

Remaining & Status (Dashboard E2, F2):
   =B2-C2
   =IF(C2>B2, "OVER", "OK")

Monthly expenses (Dashboard J2, month start in H2):
   =SUMIFS(tblTransactions[Amount],
           tblTransactions[Type], "Expense",
           tblTransactions[Date], ">="&H2,
           tblTransactions[Date], "<"&EDATE(H2,1))

Running balance (Transactions E2, then E3 down):
   =IF([@Type]="Income", [@Amount], -[@Amount])
   =E2 + IF([@Type]="Income", [@Amount], -[@Amount])
   (or one formula for every row: =N(E1) + IF([@Type]="Income", [@Amount], -[@Amount]))

Current balance (summary):
   =INDEX(tblTransactions[Balance], ROWS(tblTransactions[Balance]))

Best Practices for a Budget Workbook

✅ Do's

• Keep one master list of categories on the Setup sheet and feed the dropdown from it. Change a category in one place, everywhere updates.
• Use Tables, not fixed ranges. tblTransactions[Amount] grows with your data; D2:D500 does not.
• Sort by date before trusting the running balance, and format Amount as currency and Date as a real date.
• Let color show the story. Conditional formatting on the Remaining column means you spot overspending without reading numbers.

❌ Don'ts

• Don't type category names freehand. One "Grocery" vs "Groceries" and a whole total goes wrong silently — that's what the dropdown prevents.
• Don't hard-code a total like =82.4+64.15+91.2. It won't update when you add a transaction. Always use SUMIFS.
• Don't mix income and expenses in one signed column without a Type flag. The Type column is what lets the running balance add or subtract correctly.

📓 Learning Journal

📝 Lesson Summary

🎉 What You've Accomplished

You built a complete, genuinely useful financial tool from an empty workbook — and in doing so you pulled together everything from Modules 1 through 5: data types and formatting, references, functions, Tables, data validation, lookups, conditional logic, and conditional formatting. This is the first time all those separate skills served a single real goal, and that's exactly what "using Excel" looks like in the wild.

❓ Common Questions at This Stage

🔭 Looking Ahead

In the next lesson — Lesson 7.2: Project — A Task Tracker with a Mini-Dashboard — we build a second real project, this time leaning into visualization. You'll track tasks with status and priority dropdowns, flag overdue items automatically with TODAY, summarize with COUNTIFS and a PivotTable, and assemble a small dashboard with charts and sparklines — introducing the dashboard mindset that leads straight to the capstone.

✅ Before the Next Lesson

• Finish your Budget & Finance Tracker and confirm every checklist item works
• Try adding one brand-new transaction and watch the totals, colors, and balance update by themselves
• Write your Learning Journal entry for this lesson

📚 Additional Resources

• Microsoft Excel Help & Learning (Microsoft Support)
• Microsoft budget templates (create.microsoft.com)
• microsoft365.com — open Excel for the web