📊 Lesson 7.2: Project — A Task Tracker with a Mini-Dashboard

Design the Tasks Table

Every good tracker starts with the right columns. Plan the table before you type — it's the raw data everything else reads. A solid set of fields for a Tasks tab:

Keep this table pristine and let the Dashboard tab do the analysis — the "keep raw data raw" habit from Module 3. Freeze the header row so it stays visible as the list grows.

Status, Priority & the Done Checkbox

Consistency is what makes the dashboard work, so use data validation dropdowns (Lesson 3.2) for Status, Priority, and Owner. If someone types "in-progress", "In Progress", and "WIP" freely, your COUNTIF for "In progress" will miss rows. A dropdown forces one exact spelling every time.

For the Done column, insert a checkbox (Insert → Checkbox). A checkbox is really a TRUE/FALSE value with a friendly UI, which makes it perfect for counting and for conditional formatting. Store your dropdown option lists on a Lists tab and point validation at those ranges (or named ranges), so updating the choices is a one-place edit.

The Dashboard Metrics

On a Dashboard tab, build the numbers with the counting functions from Lesson 4.3. Assuming Status is in column C and Due date in column E on the Tasks tab:

Total tasks:        =COUNTA(Tasks!A2:A)
Not started:        =COUNTIF(Tasks!C2:C, "Not started")
In progress:        =COUNTIF(Tasks!C2:C, "In progress")
Done:               =COUNTIF(Tasks!C2:C, "Done")
High priority open: =COUNTIFS(Tasks!D2:D, "High", Tasks!C2:C, "<>Done")

Notice "<>Done" in the last one — that's "not equal to Done", so it counts high-priority tasks that still need work. These metrics recalculate instantly as tasks change, which is the whole point of a live dashboard.

Overdue & Percent Complete

Two of the most useful metrics need a little date logic.

Overdue

An overdue task is one whose due date is before today and that isn't Done. That's a two-condition COUNTIFS, comparing the due-date column to TODAY():

Overdue:  =COUNTIFS(Tasks!E2:E, "<"&TODAY(), Tasks!C2:C, "<>Done")

The trick is "<"&TODAY() — you build the criterion by joining the "less than" operator to today's date with &. Because it uses TODAY(), the overdue count is always correct with zero maintenance.

Percent complete & due this week

% complete:    =Done_count / Total_count      (format the cell as a percent)
Due this week: =COUNTIFS(Tasks!E2:E, ">="&TODAY(), Tasks!E2:E, "<="&(TODAY()+7), Tasks!C2:C, "<>Done")

"Due this week" counts tasks due between today and seven days out that aren't done — a gentle look-ahead. Wrap any division in IFERROR so an empty tracker shows a clean 0% instead of a #DIV/0!.

Visuals & Collaboration

A status chart

Put your three status counts (Not started / In progress / Done) in a little three-row table and insert a column or pie chart from it (Lesson 5.2). Now progress is visible in a glance, and the chart updates live as the counts change.

Conditional formatting

Bring the tracker to life with rules from Lesson 5.3, applied to the whole Tasks range:

Overdue rows red:   =AND($E2<>"", $E2<TODAY(), $C2<>"Done")
High priority flag: =$D2="High"
Done rows grayed:   =$F2=TRUE

The $E2<>"" test matters: in a custom formula a blank due date counts as zero — "earlier than today" — so without it every empty row below your tasks would turn red.

Collaboration touches

Because a tracker is usually shared (Lesson 6.1), add the finishing touches: each teammate can make a Filter View for just their own tasks without disturbing others; use comments and @-mentions to assign or discuss a task; and protect the Dashboard formulas so a stray edit doesn't break the metrics while people still enter tasks freely.

🎯 Project: Build It End to End

Build the whole tracker now, in your own sheet. Follow the steps and you'll finish with a real tool.

Done:          =COUNTIF(Tasks!C2:C,"Done")
Overdue:       =COUNTIFS(Tasks!E2:E,"<"&TODAY(),Tasks!C2:C,"<>Done")
% complete:    =IFERROR(COUNTIF(Tasks!C2:C,"Done")/COUNTA(Tasks!A2:A),0)
Due this week: =COUNTIFS(Tasks!E2:E,">="&TODAY(),Tasks!E2:E,"<="&(TODAY()+7),Tasks!C2:C,"<>Done")
Overdue rows (conditional format, whole range):
               =AND($E2<>"",$E2<TODAY(),$C2<>"Done")

Best Practices for Trackers

✅ Do's

• Use dropdowns for any column you'll count or filter by.
• Pick one "done" signal and be consistent.
• Base overdue on TODAY() so it self-updates.

❌ Don'ts

• Don't calculate on the raw Tasks tab — keep metrics on the Dashboard.
• Don't let #DIV/0! show — wrap percentages in IFERROR.
• Don't leave dates as text — overdue math needs real dates.

📓 Learning Journal

📝 Lesson Summary

🎉 What You've Accomplished

You've built a second real, professional-grade tool — one that any team would be glad to use. More importantly, you're seeing how the pieces from every module combine: validation, counting logic, dates, charts, formatting, and sharing all working together. That integration is spreadsheet mastery.

❓ Common Questions at This Stage

🔭 Looking Ahead

Next — Lesson 7.3: Project — A Data Cleanup & Analysis Workflow — the final project before the capstone. You'll take genuinely messy data and run a full clean-then-analyze workflow, combining your text tools with pivots and QUERY.

✅ Before the Next Lesson

• Finish and keep your task tracker — it's a portfolio piece
• Note which formulas you had to look up; those are worth a journal entry
• Write your Learning Journal entry for this lesson

📚 Additional Resources

• COUNTIFS function (Google Support)
• Google Sheets Help Center