Hands-On: Build a Tracking Sheet
Apply your spreadsheet skills to build a functional office supply tracker from scratch.
📘 Reading Lesson
Lesson Notes
Read through the key concepts before you try the challenge.
Real-World Scenario
TOR Tech needs a way to track which office supplies have been ordered, when they were ordered, and how much was spent. You are asked to build the tracker. There is no template — just a blank spreadsheet and a clear goal.
Building a Functional Tracker
A good tracker captures the right data, makes it easy to update, and gives a clear status at a glance. Here is how to build one that actually gets used:
- Plan your tracker — decide what data you need to capture before you type anything. For supplies: item name, quantity, cost per unit, date ordered, and status.
- Build the column structure — label each column in Row 1 with a frozen header so it stays visible as the sheet grows
- Using dropdowns for status — use Data Validation to create a dropdown menu (e.g., Ordered, Received, Out of Stock) so status entries are consistent
- Adding totals with SUM — use =SUM() at the bottom of your cost column to get an automatic running total
- Color-coding with conditional formatting — highlight rows red for Out of Stock and green for Received so status is visible at a glance without reading every cell
AI Assist
💡 AI Task: Ask ChatGPT — "Build me a simple office supply tracking spreadsheet with columns for item, quantity, cost, date ordered, and status." Use the suggested structure as a starting point, then customize it for TOR Tech's needs.
Knowledge Check
What spreadsheet feature should you use to keep status entries consistent across all rows?
Challenge
Apply what you've learned in this lesson.
Build a completed Office Supply Tracker spreadsheet with at least 8 rows of data. Your tracker must include: item name, quantity, cost per unit, date ordered, and status columns. Add a total cost formula using SUM, apply conditional formatting to the Status column, and freeze the header row.