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

What a Dashboard Really Is

A dashboard is not a fancy chart or a colorful sheet. It's an answer to a question, delivered in the time it takes to glance. A car's dashboard doesn't show you the engine — it shows you speed, fuel, and warning lights, because those are the things you need to decide what to do next. A good spreadsheet dashboard is the same: it hides the raw rows and surfaces the handful of numbers and pictures that let someone act. For a task tracker, the questions are obvious — How many tasks are left? How many are overdue? Who's overloaded? Are we on track?

Here's the key insight that separates a dashboard from a data dump: a dashboard reads from your data, it doesn't contain it. You keep one tidy task Table as the single source of truth, and the dashboard is a collection of formulas, a PivotTable, and charts that all point back at that table. Change a task's status and every dashboard number and chart updates itself — because none of them hold a copy of the data, they just summarize it live. That's the whole idea, and it's exactly the pattern the capstone will scale up.

Notice every arrow points from the task Table outward. That one-way flow — data in one place, summaries and visuals reading from it — is the design principle behind every dashboard you'll ever build. Get it right here on a small scale and the capstone in Lesson 8.3 is just "more of the same, wired to a control cell."

Step 1 — The Task Table with Dropdowns

Open a new workbook and make two worksheets: Tasks and Dashboard. On the Tasks sheet, set up the columns and enter this starter data beginning at A1. Today's date for this example is assumed to be around 2026-09-15, so some tasks are past due on purpose — that's what makes the overdue logic worth building:

Tasks sheet
      A                          B         C            D             E
1   Task                       Owner     Due Date     Status        Priority
2   Draft Q3 report            Ana       2026-09-10   In Progress   High
3   Book venue for offsite     Ben       2026-09-12   Not Started   Medium
4   Fix login bug              Carla     2026-09-14   In Progress   High
5   Update onboarding docs     Ben       2026-09-20   Not Started   Low
6   Review budget numbers      Ana       2026-09-16   Not Started   High
7   Ship newsletter            Carla     2026-09-08   Done          Medium
8   Prepare demo               Ben       2026-09-18   In Progress   High
9   Archive old files          Ana       2026-09-30   Not Started   Low
10  Renew SSL certificate      Carla     2026-09-13   Not Started   High
11  Plan team lunch            Ben       2026-09-25   Not Started   Low

Convert the range to a Table with Ctrl+T and name it tblTasks on the Table Design tab. Make sure Due Date is formatted as a real date, not text — the overdue logic depends on Excel treating those values as dates it can compare against today.

Add the Status and Priority dropdowns

Just like the budget tracker, consistency matters: "Done," "Done " (with a trailing space), and "Complete" would each be counted separately by COUNTIFS (capitalization alone is forgiven — "done" still counts as "Done" — but stray spaces and synonyms are not). Lock the values down with data validation (Lesson 3.2). Because these lists are short and fixed, you can type the choices straight into the Source box — no separate list needed:

Select the Status column ▸ Data ▸ Data Validation ▸ Allow: List
   Source:  Not Started,In Progress,Done

Select the Priority column ▸ Data ▸ Data Validation ▸ Allow: List
   Source:  High,Medium,Low

Now Status and Priority can only be one of the allowed words, which means every summary you build on top will be accurate. This is the same "clean data first" principle from the budget project — a dashboard is only as trustworthy as the table it reads from, and dropdowns are the cheapest possible insurance against messy data.

Step 2 — Overdue Logic with IF & TODAY

The single most useful thing a task tracker can do is tell you what's late without you checking dates by hand. TODAY() returns the current date and re-evaluates every day the file opens, so any formula built on it stays current forever. Add a calculated column to the Table — put the header Flag in column F and enter this in the first data row (Table structured references fill the whole column automatically):

Tasks sheet — Flag column (F)
F2  =IF([@Status]="Done", "Done",
        IF([@Due Date]<TODAY(), "OVERDUE",
           IF([@Due Date]<=TODAY()+3, "Due soon", "On track")))

Read it as a little decision tree, top to bottom: if the task is already Done, say so and stop. Otherwise, if its due date is before today, it's OVERDUE. Otherwise, if it's due within the next three days, it's "Due soon." Everything else is "On track." Nested IF works through the conditions in order and stops at the first one that's true — so order matters (we check Done first so a finished-but-late task doesn't scream OVERDUE).

You could write the same thing more readably with IFS (Lesson 4.3), which avoids the pile of closing parentheses:

F2 (same logic, IFS style — Microsoft 365 / web)
   =IFS([@Status]="Done", "Done",
        [@Due Date]<TODAY(), "OVERDUE",
        [@Due Date]<=TODAY()+3, "Due soon",
        TRUE, "On track")

Light it up with conditional formatting

Now make the flags impossible to miss. Select the Flag column and add conditional formatting rules based on the text:

Use Home → Conditional Formatting → Highlight Cells Rules → Text that Contains for each word. You can also add a rule to the whole row: select the Table body, add a formula rule =$F2="OVERDUE", and give it a red fill so the entire row of an overdue task glows. Row-level formatting like that is a small touch that makes a tracker feel professional.

Step 3 — Summaries with COUNTIFS

Time to start the dashboard. Switch to the Dashboard sheet and build a small block of headline numbers using COUNTIFS — the counting cousin of SUMIFS. It counts rows that match one or more criteria. Enter these:

Dashboard sheet — status summary (starting A1)
      A                B
1   STATUS           Count
2   Not Started      =COUNTIFS(tblTasks[Status], "Not Started")
3   In Progress      =COUNTIFS(tblTasks[Status], "In Progress")
4   Done             =COUNTIFS(tblTasks[Status], "Done")
5   TOTAL            =COUNTA(tblTasks[Task])

Key numbers (starting A7)
      A                B
7   Overdue          =COUNTIFS(tblTasks[Flag], "OVERDUE")
8   Due soon         =COUNTIFS(tblTasks[Flag], "Due soon")
9   High priority    =COUNTIFS(tblTasks[Priority], "High")
10  Open & High     =COUNTIFS(tblTasks[Priority], "High", tblTasks[Status], "<>Done")

A couple of things worth noticing. COUNTA(tblTasks[Task]) counts non-empty task names for the total, which grows automatically as you add rows. And the last one — "<>Done" — uses the "not equal to" operator as a criterion, counting High-priority tasks that aren't finished. Combining criteria like that is where COUNTIFS earns its keep: "Open AND High" is precisely the number a manager cares about most.

These few cells are already a legitimate mini-dashboard: someone can glance at "4 overdue, 5 high-priority open" and know exactly where to look. Everything else we add — the PivotTable and charts — makes the same information easier to see, but the honest, decision-ready numbers come from these COUNTIFS formulas.

Step 4 — A PivotTable by Owner & Status

COUNTIFS is perfect when you know exactly which questions you'll ask. But when you want to slice the data every which way — by owner, then by status, then re-arranged in seconds — a PivotTable (Lesson 5.1) is the right tool. It answers "who has how many of what?" without a single formula.

Click any cell in tblTasks, then Insert → PivotTable. Place it on the Dashboard sheet (or a new sheet). In the PivotTable Fields pane, drag fields into these areas:

In a few clicks you get a grid showing exactly how the workload is distributed — who has the most open tasks, who's cleared their plate. Drag Priority into the Filters area and you can instantly narrow the whole pivot to just High-priority work. That fluid rearranging is the PivotTable's superpower: the same underlying table, reshaped to answer whatever question comes up next.

Step 5 — Charts, Sparklines & the Dashboard

Numbers tell; pictures show. Now turn your summaries into visuals and arrange everything into one tidy dashboard area. A picture isn't decoration here — a bar of "tasks per owner" reveals an overloaded teammate faster than any table.

Add a couple of charts

Charts (Lesson 5.2) read from a range, so point them at your summary blocks:

• Status doughnut or bar: select the STATUS summary (A1:B4) and Insert → Chart. A doughnut chart of Not Started / In Progress / Done gives an instant sense of overall progress.
• Tasks per owner column chart: select your PivotTable (or a small owner-count block) and insert a clustered column chart. This is the "who's overloaded?" picture.

A chart built from a PivotTable is called a PivotChart and updates with the pivot when you refresh. Keep charts small and clean — a clear title, no chart-junk. On a dashboard, restraint reads as professionalism.

Add sparklines

Sparklines are tiny in-cell charts (Lesson 5.2) — perfect for a dashboard because they fit right next to a number. Suppose you keep a small block of "tasks completed per week":

Dashboard sheet — mini trend for a sparkline (starting D1)
      D          E    F    G    H    I
1   Completed   1    3    2    4    5

Select a cell (e.g. D2) ▸ Insert ▸ Sparklines ▸ Line
   Data Range:  E1:I1
   → a tiny trend line appears inside D2

One cell, one glanceable trend — "completions are climbing." Sparklines are fully supported in the desktop app; on the web, sparkline support has varied over time (as Lesson 5.2 noted), so if you don't see the option, add it on the desktop or skip it. A Line sparkline for trends and a Column sparkline for counts are the two you'll reach for most.

Assemble the dashboard area

Finally, arrange it. Put the headline numbers (Total, Overdue, High-priority open) in big, bold cells across the top — these are the warning lights. Place the status chart and the tasks-per-owner chart below them, the PivotTable to the side, and a sparkline next to a trend number. Give the area a title, hide gridlines (View → Gridlines off) for a clean look, and you have a real mini-dashboard: one screen that answers "how's the work going?" at a glance, all of it reading live from tblTasks.

🎯 Project: Build the Task Tracker

Build the whole tracker end to end using the starter data (or your own real tasks — even better). Work top to bottom and check each piece before moving on. Aim for a dashboard someone could actually run their week from.

-- Tasks: Ctrl+T --> name tblTasks
Status dropdown  ▸ Source:  Not Started,In Progress,Done
Priority dropdown ▸ Source: High,Medium,Low

Flag column (F2, fills down):
   =IF([@Status]="Done","Done",
       IF([@Due Date]<TODAY(),"OVERDUE",
          IF([@Due Date]<=TODAY()+3,"Due soon","On track")))

Dashboard COUNTIFS:
   Not Started   =COUNTIFS(tblTasks[Status],"Not Started")
   In Progress   =COUNTIFS(tblTasks[Status],"In Progress")
   Done          =COUNTIFS(tblTasks[Status],"Done")
   Total         =COUNTA(tblTasks[Task])
   Overdue       =COUNTIFS(tblTasks[Flag],"OVERDUE")
   Due soon      =COUNTIFS(tblTasks[Flag],"Due soon")
   High & open   =COUNTIFS(tblTasks[Priority],"High", tblTasks[Status],"<>Done")

PivotTable: Rows=Owner, Columns=Status, Values=Count of Task
Sparkline: Insert ▸ Sparklines ▸ Line, data = your weekly-completed row

Best Practices for Dashboards

✅ Do's

• Keep one source table and make every summary, pivot, and chart read from it. Never keep a second copy of the data.
• Put the warning lights on top. Overdue count and high-priority-open belong where the eye lands first.
• Refresh PivotTables after editing data — or set them to refresh on open (desktop: PivotTable Options → Refresh data when opening the file).
• Use dropdowns for anything you'll count. Consistent Status/Priority text is what keeps COUNTIFS and the pivot honest.

❌ Don'ts

• Don't crowd the dashboard. If a chart doesn't help a decision, cut it. Restraint is the skill.
• Don't hard-code counts like "5 overdue" in a cell — it goes stale instantly. Always use COUNTIFS or the pivot.
• Don't forget dates can be text. A Due Date typed as text won't compare against TODAY() — check the column is truly a date.

📓 Learning Journal

📝 Lesson Summary

🎉 What You've Accomplished

You built a task tracker that flags its own overdue work, summarizes itself with formulas and a PivotTable, and shows the whole picture in charts and sparklines arranged as a dashboard. More importantly, you internalized the dashboard mindset: one source of truth, summaries that read from it, and a layout built around the decisions people actually make. That mindset is the through-line into the capstone.

❓ Common Questions at This Stage

🔭 Looking Ahead

In the next lesson — Lesson 7.3: Project — A Data Cleanup & Analysis Workflow — we tackle the messy reality of real-world data. You'll start from an imported dataset full of inconsistent case, extra spaces, split names, and duplicates, clean it with TRIM, PROPER, TEXT, and Flash Fill (with Power Query as the repeatable option), then structure and analyze it with a PivotTable and dynamic arrays — the true analyst workflow of clean → structure → analyze → conclude.

✅ Before the Next Lesson

• Finish your Task Tracker and confirm the dashboard updates when you change a task
• Try adding an overdue task and a due-soon task, and watch the flags and counts react
• Write your Learning Journal entry for this lesson

📚 Additional Resources

• Microsoft Excel Help & Learning (Microsoft Support)
• Microsoft to-do & planner templates (create.microsoft.com)
• microsoft365.com — open Excel for the web