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

Plan the Structure

Before we type a single number, we plan — because the shape of a workbook decides how pleasant it is to live with for the next year. The single most important rule for any tracker is one you've met before in this course: keep raw data raw. Your transactions are the ground truth. They go in one tab, one row per transaction, and nothing else clutters them — no totals mixed in, no blank spacer rows, no little side calculations wedged into column H. Every summary, chart, and budget number lives on a separate tab that reads the raw data. Think of it as the Cells → Formulas → Views model spread across tabs: the Transactions tab is Cells, the Summary tab is Formulas and Views.

Here's the three-tab plan we'll build:

The Transactions tab uses these five columns. Notice how deliberately small each one is — every column holds exactly one piece of information, which is what makes SUMIF and filtering effortless later:

• Date — a real date value (so we can roll up by month)
• Category — chosen from a dropdown (Groceries, Rent, Transport, …)
• Description — free text, a short note
• Amount — a positive number, always
• Type — a dropdown: Income or Expense

That flow — enter, categorize, summarize, compare — is the spine of the whole project. Everything we do from here hangs off it.

Build the Transactions Table

Open a fresh spreadsheet at sheets.google.com and rename it something like Budget Tracker 2026. Rename the first tab to Transactions (double-click the tab name at the bottom). Then, before anything else, create your Lists tab, because the dropdowns on Transactions will read from it.

Set up the Lists tab first

On the Lists tab, we'll keep two small reference tables. In column A, a list of categories; in column B, the monthly budget for each; and off to the side, the two Type options. It looks like this:

        A              B            |   D
1   Category       Monthly Budget   |   Type
2   Groceries            400        |   Income
3   Rent                1200        |   Expense
4   Transport            150        |
5   Dining Out           120        |
6   Utilities            180        |
7   Entertainment         80        |
8   Savings              300        |
9   Income                 0        |

Type your own categories and realistic monthly budgets — this is your tracker. We include an "Income" row in the category list so income transactions can be categorized too (its budget is 0 because you don't budget income the same way). Keep the Type options (Income, Expense) in a separate little column so validation can point at them cleanly.

Build the header and columns on Transactions

Back on the Transactions tab, put your headers in row 1: Date, Category, Description, Amount, Type in A1:E1. Bold them, then freeze row 1 (View → Freeze → 1 row) so the headers stay put as you scroll — we covered freezing back in Lessons 1.3 and 3.3. Format column A as a date (Format → Number → Date) and column D as currency (Format → Number → Currency).

Add the dropdowns with data validation

This is where validation from Lesson 3.2 pays off. Dropdowns stop typos cold — no more "Grocries" and "groceries" splitting your totals in two. Select B2:B1000 (the Category column, generously far down), then Data → Data validation → Add rule → Dropdown (from a range), and point it at Lists!A2:A9. Do the same for the Type column E2:E1000, pointing at Lists!D2:D3.

Now enter a dozen or so sample transactions so you have something to summarize. Mix income and expenses across a couple of months. A few rows might look like:

    A            B             C                 D        E
1   Date         Category      Description       Amount   Type
2   2026-01-03   Income        Paycheck          2500     Income
3   2026-01-04   Groceries     Weekly shop       82.40    Expense
4   2026-01-05   Transport     Bus pass          45.00    Expense
5   2026-01-08   Dining Out    Lunch with Sam    18.50    Expense
6   2026-01-15   Rent          January rent      1200     Expense
7   2026-02-02   Income        Paycheck          2500     Income
8   2026-02-06   Groceries     Weekly shop       91.10    Expense

⚠️ Important Note: Enter dates as real dates, not text. If a date sits left-aligned in its cell, Sheets is treating it as text and your monthly rollups will silently fail. Real dates right-align by default. If yours don't, re-enter them in your locale's format or use Format → Number → Date. We'll lean on real dates hard in the monthly rollup.

Build the Summary with SUMIF & Budget Variance

Create a third tab called Summary. This is the brain of the tracker, and it never stores data of its own — it only reads Transactions and Lists. Every number here recalculates the instant you add a transaction. Let's build it in layers.

Totals by category with SUMIF

We met SUMIF back in Lesson 4.3 — "add up the amounts where the category matches." Pull your category list over from Lists, then sum against Transactions. In A1 put a header Category, in B1 put Budget, in C1 put Actual. Then:

A2:   =Lists!A2          (or just retype your categories)
B2:   =Lists!B2          (pull the budget across)
C2:   =SUMIF(Transactions!$B$2:$B$1000, A2, Transactions!$D$2:$D$1000)

Read C2 aloud: "Look down the Transactions Category column; wherever it equals the category in A2, add up the matching Amount." The $ signs lock the ranges (absolute references, from Lesson 2.1) so you can fill the formula down all your categories without the ranges drifting. Fill A2:C2 down to cover every category.

Income vs expenses and the running balance

Now the headline numbers. Off to the side (say in E1:F5), build a small summary block. Here we filter by the Type column instead of category:

Total Income     =SUMIF(Transactions!$E$2:$E$1000, "Income",  Transactions!$D$2:$D$1000)
Total Expenses   =SUMIF(Transactions!$E$2:$E$1000, "Expense", Transactions!$D$2:$D$1000)
Net / Balance    =F2 - F3

That Net / Balance cell (=F2 - F3) is your running balance: income minus expenses across everything you've entered. Add a transaction and it updates on the spot. This is the number most budget apps charge a subscription for, and you just built it in one line.

Budget vs actual — the variance column

Here's the payoff of keeping a Budget column beside Actual. Add a Variance column in D with header Remaining:

D2:   =B2 - C2

A positive number means you have budget left in that category; a negative number means you've overspent. Fill it down. Now your Summary has, per category, Budget, Actual spend, and how much room is left — a genuine budget-vs-actual comparison. One honest caveat: Budget is monthly, but this Actual adds up every transaction you've entered, so once you have two months of data it's a total-to-date figure. Read it that way (or compare against budget × months so far), or use the SUMIFS date-bounds technique below to compute Actual for a single month. For a percentage view, add another column =C2/B2 and format it as a percent (guard against dividing by a zero budget — we'll flag that with an IFERROR, echoing error-handling from Lesson 4.3):

=IFERROR(C2/B2, 0)

Monthly rollups with SUMIFS

To see spending by month, we graduate from SUMIF (one condition) to SUMIFS (many conditions), which you met in Lesson 4.3. We want "total expenses where Type is Expense AND the date falls within January." The cleanest approach uses two date comparisons as the extra criteria:

Jan Expenses:
=SUMIFS(Transactions!$D$2:$D$1000,
        Transactions!$E$2:$E$1000, "Expense",
        Transactions!$A$2:$A$1000, ">="&DATE(2026,1,1),
        Transactions!$A$2:$A$1000, "<="&DATE(2026,1,31))

Notice the operators are built by joining text and a real date: ">="&DATE(2026,1,1) means "on or after Jan 1." (That & is the concatenation operator from Lesson 2.3, joining the comparison text to a date value.) Copy the block for February, changing the two dates, and you have a monthly rollup you can chart.

⚠️ Important Note: When you compare against a date inside SUMIFS, build it with DATE(year,month,day) rather than typing ">=1/1/2026". Typed date text is interpreted by locale and breaks for readers in other regions; DATE is unambiguous everywhere. This is a small habit that prevents a genuinely confusing class of bugs.

Visualize — Chart, Sparklines & Overspend Formatting

Numbers are the foundation; a glance-able picture is what makes you actually use the tracker. We'll add three visual layers, each drawing on skills from earlier modules.

A category chart

Select your category names and their Actual amounts (A1:A8 and C1:C8 — hold Ctrl/Cmd to select two non-adjacent ranges). Stop at row 8: the Income row would dwarf every spending slice, then Insert → Chart. Sheets will usually guess a column chart; a pie chart or bar chart reads especially well for "share of spending by category," as we discussed in the charts lesson (Lesson 5.2). Give it a clear title like Spending by Category. Because the chart points at your SUMIF results, it redraws automatically every time you add a transaction.

Sparklines for a compact trend

A SPARKLINE is a tiny in-cell chart — perfect for showing each category's month-by-month trend without a full chart. If you build a small grid of monthly amounts per category, a sparkline in the next cell draws the shape:

=SPARKLINE(H2:M2, {"charttype","column"})

It also works beautifully as a mini budget bar. Sparklines keep dashboards dense and readable — a lot of signal in one cell.

Conditional formatting for overspend

This is the feature that makes the tracker feel alive. We want overspent categories to turn red automatically. Select the expense rows of your Actual column (C2:C8 — skip the Income row, whose budget of 0 would always read as "over"), then Format → Conditional formatting → Custom formula is, and enter:

=$C2 > $B2

Choose a red fill. Now any category where Actual exceeds Budget lights up red — no checking required, it just shows you. This is the exact custom-formula technique from Lesson 5.3, applied to a rule that matters to your wallet. Add a second rule for "getting close," say =$C2 > $B2*0.9 in amber, so you get a warning before you tip over.

Make It Reusable & Shareable

A tracker you rebuild every month is a tracker you'll abandon by March. Let's make it durable.

A monthly template you can copy

The easiest reusable pattern is one workbook with the whole year in it — because your SUMIFS rollups already slice by month, you never need a new file. But if you prefer a clean sheet per month, right-click the tab → Duplicate, or File → Make a copy of the whole workbook and clear the transactions. Keep a pristine, empty version named Budget Template and copy that each time, so your formulas and formatting come along for free.

Freeze, tidy, and protect the formulas

Freeze row 1 on every tab so headers stay visible. Then protect the parts that shouldn't be touched. On the Summary tab, the formulas are fragile — one stray edit and a total breaks. Select the formula ranges, then Data → Protect sheets and ranges, and set them to show a warning or restrict editing. This is the protection technique from Lesson 6.1, and it's the difference between a tool you trust and one you're afraid to click in.

The Excel angle

Everything here — SUMIF, SUMIFS, conditional formatting, charts — exists in Excel under the same names, so this build transfers cleanly. Where Sheets pulls ahead is the sharing: hand a household member a link and you're both entering transactions into the same live sheet, no emailing files back and forth. Where Excel can pull ahead is very large histories — years of daily transactions across many accounts — where a desktop app or a database starts to feel snappier. For a personal or household budget, Sheets is comfortably more than enough.

🎯 Project: Build the Full Tracker

Time to put it all together into one working workbook. Follow the steps in order; each one builds on the last. Use your own real categories and a handful of real (or realistic) transactions — a tracker built on your numbers is one you'll keep.

-- Summary: Actual spend per category (fill down)
=SUMIF(Transactions!$B$2:$B$1000, A2, Transactions!$D$2:$D$1000)

-- Summary: Remaining budget per category
=B2 - C2

-- Summary: percent of budget used (safe against zero budget)
=IFERROR(C2/B2, 0)

-- Summary: total income and total expenses
=SUMIF(Transactions!$E$2:$E$1000, "Income",  Transactions!$D$2:$D$1000)
=SUMIF(Transactions!$E$2:$E$1000, "Expense", Transactions!$D$2:$D$1000)

-- Summary: running balance
=TotalIncomeCell - TotalExpensesCell

-- Summary: January expenses (monthly rollup)
=SUMIFS(Transactions!$D$2:$D$1000,
        Transactions!$E$2:$E$1000, "Expense",
        Transactions!$A$2:$A$1000, ">="&DATE(2026,1,1),
        Transactions!$A$2:$A$1000, "<="&DATE(2026,1,31))

-- Conditional formatting rule on the Actual column (over budget)
=$C2 > $B2

Best Practices for a Budget Tracker

✅ Do's

• Keep raw data raw. Transactions in, nothing else. All math lives on Summary, reading the raw tab.
• Use dropdowns for anything you'll total. Consistent category text is what makes SUMIF trustworthy.
• Anchor your ranges with $. Absolute references let you fill formulas down without the ranges sliding.

❌ Don'ts

• Don't hand-total anything. A typed total is wrong the moment you add a row and never tells you.
• Don't wedge summaries into the Transactions tab. Spacer rows and side-totals break sorting, filtering, and SUMIF.
• Don't share a financial sheet with "anyone with the link." Name the people, and match access to role.

📓 Learning Journal

📝 Lesson Summary

🎉 What You've Accomplished

You built a complete, real-world tool from scratch — not a demo, a keeper. You combined validation, SUMIF/SUMIFS, absolute references, error handling, conditional formatting, and a chart into one workbook where a single data entry ripples through the whole dashboard. That's the Cells → Formulas → Views model at full strength, and it's exactly how professionals build spreadsheets that people actually rely on.

❓ 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 complete tool, this time for getting things done. You'll use COUNTIF and COUNTIFS with TODAY() to count tasks by status, flag overdue items, and compute percent complete — the counting cousins of the summing you just mastered.

✅ Before the Next Lesson

• Confirm your tracker fully self-updates: add one new transaction and watch every summary number react
• Save your Budget Template copy so it's ready to reuse
• Write your Learning Journal entry for this lesson

📚 Additional Resources

• sheets.google.com — open the app
• Google Sheets function list (SUMIF, SUMIFS, and more)
• Google Sheets Help Center (Google Support)