Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 29 ยท Projects, Practice and Interviews

Real-World Projects

Build four real Google Sheets projects: a budget tracker, a gradebook, an invoice generator and an inventory dashboard, with formulas and checks.

Knowing functions one by one is not the same as building a sheet people actually use. A real workbook needs a clear layout, inputs that are hard to break, formulas that survive new rows, and a summary you can read in five seconds. In this lesson you will design four small projects from start to finish: a budget tracker, a gradebook, an invoice generator and an inventory dashboard.

Google Sheets cannot run on this page, so the sheet layouts and formulas are shown read-only. The TryIt blocks are honest stand-ins: Python or JavaScript programs that compute the same results your formulas would, so you can check your thinking against real numbers.

Four projects

Budget Income, spending and what is left.
Gradebook Weighted scores and letter grades.
Invoice Lines, tax and a total.
Inventory Stock levels and reorder alerts.
1 / 2

Project 1: Budget tracker

Goal: record each transaction once and see monthly totals per category automatically.

Create two tabs. On Transactions, use one row per transaction with these columns:

A: Date B: Description C: Category D: Amount
2026-01-03 Salary Income 2400
2026-01-05 Rent Housing -900
2026-01-09 Groceries Food -86.40

Spending is stored as negative numbers, so a plain sum gives the balance. Add a dropdown for column C (Data Validation and Dropdowns) so categories are always spelled the same way.

On the Summary tab, list the categories in column A, put the month number in B1, and use one formula per row:

TEXT
=SUMPRODUCT((Transactions!$C$2:$C$1000=$A3) * (MONTH(Transactions!$A$2:$A$1000)=B$1) * Transactions!$D$2:$D$1000)

A shorter alternative with a helper column E (=TEXT(A2,"yyyy-mm")) is =SUMIFS(Transactions!$D:$D, Transactions!$C:$C, $A3, Transactions!$E:$E, B$1); see SUMIF, COUNTIF, AVERAGEIF and Friends. The $ signs matter: they let you copy the formula across and down without breaking the ranges (Relative, Absolute and Mixed References).

Finish with a balance cell, =SUM(Transactions!D2:D), and a conditional-formatting rule that turns it red when it is below zero (Conditional Formatting).

Python Stand-in: monthly totals per category

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Project 2: Gradebook

Goal: weighted scores, a final percentage and a letter grade per student.

Put the weights in their own cells, never inside formulas, so a teacher can change them:

B C D
Row 1 Homework Midterm Final
Row 2 (weights) 0.3 0.3 0.4

Students go in rows 4 and down. The weighted score in E4 is:

TEXT
=SUMPRODUCT(B4:D4, $B$2:$D$2)

For the letter, use a banded lookup on a small table (see VLOOKUP, HLOOKUP and Their Limits). Keep the table sorted ascending:

TEXT
=VLOOKUP(E4, $H$2:$I$6, 2, TRUE)

With H2:I6 holding 0 F, 60 D, 70 C, 80 B, 90 A. A class average per column is =AVERAGE(B4:B), and =ARRAYFORMULA(...) can fill the whole column from one cell if you prefer (Array Formulas and ARRAYFORMULA).

Python Stand-in: weighted grades and letters

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Project 3: Invoice generator

Goal: a printable invoice that calculates its own lines, tax and total.

Use one tab named Invoice. The header block holds the customer, invoice number and date. The line table has columns Item, Quantity, Unit price and Line total. The key formulas are:

TEXT
D12: =IF(B12="", "", B12*C12)
D30: =SUM(D12:D29)
D31: =D30*$B$4
D32: =D30+D31

where B4 is the tax rate formatted as a percentage. Wrapping a line in IF(B12="", "", โ€ฆ) keeps unused rows blank instead of showing zeros. Round money to cents with ROUND(โ€ฆ, 2) so printed totals match the sum of printed lines. If prices come from a separate Products tab, fetch them with XLOOKUP (XLOOKUP, INDEX and MATCH) and a dropdown on the item column.

Set the print area, hide gridlines and use File > Print to save a PDF per customer.

Python Stand-in: invoice lines, tax and rounding

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Project 4: Inventory dashboard

Goal: see stock levels at a glance and flag items to reorder.

The Stock tab lists SKU, Product, On hand, Reorder level and Unit cost. Add computed columns:

TEXT
F2: =C2*E2                      stock value
G2: =IF(C2<=D2, "REORDER", "OK")  status

The dashboard cells use a few summary formulas: =SUM(F2:F) for total value, =COUNTIF(G2:G, "REORDER") for items to reorder, and =QUERY(Stock!A:G, "select B, C where G = 'REORDER' order by C", 1) for a live reorder list (The QUERY Function). Add a rule that fills rows red when =$C2<=$D2, and a chart of value by product (Charts and Sparklines).

The JavaScript stand-in below mirrors these formulas.

JS Stand-in: reorder alerts and stock value

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Habits that make projects last

  • Separate inputs, calculations and output onto different tabs or clearly labelled areas.
  • Protect formula cells and leave only input cells editable (Sharing, Comments and Version History).
  • Use whole-column or open-ended ranges (D2:D) so new rows are included.
  • Keep assumptions in cells (tax rate, weights, thresholds) with labels.
  • Test with edge cases: an empty row, a zero, a text value where a number belongs.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why should the gradebook weights be stored in cells instead of typed inside the formula?

    Assumptions in labelled cells are visible and editable, and every formula that uses them updates automatically.
  2. 2. In the invoice, why wrap a line total as IF(B12="", "", B12*C12)?

    An empty quantity would otherwise multiply to 0; the IF returns an empty string for blank rows.
  3. 3. What makes a budget formula like SUMIFS(Transactions!$D:$D, ...) safe to copy across and down?

    Dollar signs pin the data ranges while the criteria references shift to the right row or column.
  4. 4. Which formula counts how many inventory items need reordering?

    COUNTIF counts the cells in the status column equal to REORDER.

Summary

  • Plan the layout first: separate input data, calculations and a summary.
  • Budget trackers lean on SUMIFS or SUMPRODUCT with absolute and mixed references.
  • Gradebooks combine SUMPRODUCT with weights in cells and a banded lookup for letters.
  • Invoices need blank-safe line formulas, a tax rate cell and rounding to cents.
  • Inventory dashboards mix status columns, COUNTIF, QUERY, conditional formatting and a chart.

Next: Practice Exercises with Solutions