Hands-On: Build an Office Supply Tracker
Apply every Excel Essentials skill in one complete project — design, build, and finalize a professional office supply tracker for Lakeside Medical Associates from scratch.
Video
Watch the lesson video, then complete the reading and challenge.
Presentation Slides
Review the slides below, then complete the reading and challenge.
1 / 9
Lesson Notes
Read through the key concepts before you try the challenge.
Real-World Scenario
Your office manager at Lakeside Medical Associates has just promoted you from data entry to supply management. Your first project is to build the practice's supply tracker from scratch — the previous tracker was a messy, inconsistently formatted spreadsheet with no formulas, no conditional formatting, and no summary data. You have been given a list of 20 items currently in stock and asked to deliver a professional, fully functional tracker by end of day. Everything you learned in this module is required to make this happen.
Designing the Tracker Before You Build
Professional spreadsheet builders always plan their structure before typing a single cell — designing the columns, data types, and formula architecture in advance prevents rebuilds that waste time. For the Lakeside Medical Associates supply tracker, the design decisions are straightforward but important:
- Column layout and data types: the tracker needs seven core columns in this order — Item (text), Category (text), Quantity (number), Unit Cost (currency), Total Cost (currency formula), Reorder Level (number), Status (text formula). Adding an eighth column — Date Last Ordered (date) — provides valuable restocking history without cluttering the view. All column headers belong in row 1 in bold, frozen so they stay visible while scrolling.
- Formula architecture: Total Cost (column E) will always be =C*D (Quantity × Unit Cost) — a relative reference formula copied down all rows. Status (column G) will always be the nested IF formula =IF(C=0,'OUT',IF(C<=F,'LOW','OK')) — also copied down all rows. All other columns are manually entered data. No total row should use manually typed numbers — the summary section on a separate sheet will use SUM, AVERAGE, COUNT, and COUNTIF formulas referencing the main data table.
- Table vs plain range: converting to an Excel Table (Ctrl+T) is the right choice for a tracker that will be updated regularly by multiple staff members — it provides automatic filter dropdowns, the Totals Row feature, alternating row colors for readability, and automatic formula expansion as new items are added. The investment in table setup pays off with every future use.
- Color strategy for conditional formatting: use Excel's built-in color system rather than custom colors — red (standard Excel red) for OUT and below reorder level, yellow/orange for items within 2 of the reorder level, green for adequately stocked items. Stick to the standard colors because they are recognizable by all staff without needing a legend.
Building and Populating the Tracker
With the design planned, building the tracker is a systematic process — set up the structure, enter the data, write the formulas, apply the formatting, and convert to a table. Do these in order and the tracker builds cleanly without needing to go back and fix structural decisions mid-build:
- Set up the structure first: create a new workbook with two sheets — 'Supply Tracker' and 'Summary.' On the Supply Tracker sheet, type the 7 column headers in row 1, bold them, and freeze row 1. Set column widths so each header is fully visible without truncation — double-click the column border to auto-fit each column's width.
- Enter all 20 data rows before writing formulas — populate Item, Category, Quantity, Unit Cost, Reorder Level, and Date Last Ordered for all 20 items first. Include a realistic mix: at least 3 OUT items (Quantity = 0), at least 4 LOW items, and the rest adequately stocked. Use at least 3 categories (Medical, Office, Cleaning). Apply Currency formatting to Unit Cost immediately after entering all cost data.
- Write formulas after all data is entered — type =C2*D2 in E2, format E2 as Currency, then copy E2 down to E21 using Ctrl+D or the fill handle. Type the nested IF formula in G2 and copy down to G21. Verify that OUT items show 'OUT,' LOW items show 'LOW,' and stocked items show 'OK' — if any Status is wrong, check whether the Quantity or Reorder Level in that row was entered correctly.
- Apply conditional formatting after verifying formula accuracy — select the Quantity column (C2:C21) and apply a formula-based rule (=C2=0 → red fill; =AND(C2>0,C2<=F2) → yellow fill). This is more precise than simple value-based rules because it compares each quantity to that row's own reorder level. Apply a second rule to the Status column using 'Text Contains' rules for 'OUT' (red fill, bold) and 'LOW' (yellow fill).
- Convert to Excel Table and enable the Totals Row — select the data range A1:H21 and press Ctrl+T. Apply the 'Blue, Table Style Medium 2' or similar professional style. Enable the Totals Row via Table Design > Total Row. Set the Totals Row to show SUM for Total Cost, AVERAGE for Unit Cost, and COUNT for Item to give instant summary stats at the bottom of the table.
The Summary Sheet
The Summary sheet gives the office manager a one-stop view of the tracker's key metrics without needing to scroll through the full supply list. All values on the Summary sheet must come from formulas that reference the Supply Tracker sheet — never typed manually:
- The Summary sheet should include: Total Items Tracked (COUNTA referencing the Item column), Items OUT of Stock (COUNTIF referencing Status column for 'OUT'), Items LOW (COUNTIF for 'LOW'), Total Supply Value (SUM of Total Cost column), Average Unit Cost (AVERAGE of Unit Cost column), and Last Updated (TODAY() function). Label each metric clearly in column A and put the formula in column B.
- Cross-sheet references use the sheet name and an exclamation point — to reference cell E2 on the Supply Tracker sheet from the Summary sheet, the formula is ='Supply Tracker'!E2. To reference the entire Total Cost column, use =SUM('Supply Tracker'!E2:E21). When you click on a cell in the Summary sheet formula bar and then click over to the Supply Tracker sheet to select a range, Excel automatically builds the cross-sheet reference for you.
Quick Reference: Supply Tracker Build Checklist
Excel Essentials Capstone Quick Reference — building the supply tracker from scratch
Responsible Use
The supply tracker you build will be used to make real purchasing decisions at Lakeside Medical Associates — ordering too little of a critical supply (exam gloves, sterile gauze, blood draw supplies) because the tracker shows incorrect quantities could affect patient care. After building the tracker, physically verify at least 5 items by counting actual stock and comparing it to the tracker values. Document this verification with a date and your name in a note on the Summary sheet. A tracker that has never been physically verified against actual inventory is not trustworthy — and unverified data used for procurement decisions is a professional liability.
AI Assist
💡 AI Task: Ask ChatGPT — 'Generate a realistic list of 20 office supply items for a small medical practice, organized into 4 categories: Medical Supplies, Office Supplies, Cleaning Supplies, and Break Room Supplies. For each item, provide a realistic quantity on hand (some should be at zero for out of stock), a realistic unit cost in dollars, and a recommended reorder level. Format the response as a table I can copy directly into Excel.' Use the response to populate your supply tracker rather than inventing data, then make any adjustments to create the right mix of OUT, LOW, and OK items for testing your formulas.
Knowledge Check
In your supply tracker, you need a formula in the Status column that shows 'OUT' when Quantity is 0, 'LOW' when Quantity is at or below the Reorder Level, and 'OK' otherwise. Which formula is correct?
Challenge
Apply what you've learned in this lesson.
Build the complete Lakeside Medical Associates office supply tracker from scratch, incorporating every Excel Essentials skill from this module. Your finished tracker must be operationally ready — a real office could use it today.
- Create a new workbook with two sheets: 'Supply Tracker' (main data) and 'Summary' (dashboard). On the Supply Tracker sheet, set up 8 columns (Item, Category, Quantity, Unit Cost, Total Cost, Reorder Level, Status, Date Last Ordered) with bold headers in row 1, frozen so they stay visible. Enter 20 realistic supply items with a mix of Medical, Office, and Cleaning categories. Include at least 2 items at zero stock and at least 4 items below their reorder level.
- Write and copy down the Total Cost formula (=C2*D2) for all 20 rows. Write and copy down the nested IF Status formula (=IF(C2=0,'OUT',IF(C2<=F2,'LOW','OK'))) for all 20 rows. Apply Currency formatting to Unit Cost and Total Cost. Verify all Status values are correct by checking 3 OUT items, 3 LOW items, and 3 OK items manually.
- Apply conditional formatting: red fill to the Quantity column when at or below Reorder Level (use formula rule =C2<=F2); green fill when above Reorder Level. Apply a separate conditional formatting rule to the Status column highlighting 'OUT' in red and 'LOW' in yellow. Convert the data range to an Excel Table, apply a professional table style, and enable the Totals Row showing SUM for Total Cost.
- Sort the tracker by Status (custom order: OUT first, then LOW, then OK), then by Category (A→Z). Filter to show only LOW and OUT items. Take a screenshot of the filtered reorder list, then clear the filter.
- On the Summary sheet, build a 6-row dashboard using cross-sheet formulas: Total Items (COUNTA), Items OUT (COUNTIF), Items LOW (COUNTIF), Total Supply Value (SUM), Average Unit Cost (AVERAGE), Last Updated (TODAY()). Format all monetary values as Currency. Save the workbook as 'Supply Tracker – Lakeside Medical – Capstone.xlsx' and export the Supply Tracker sheet as PDF.