Course: Creating a Project Management Tracker in Excel
The Introduction chapter establishes a clear foundation for modern project management by emphasizing Agile and Scrum as adaptable, team-centered methodologies suited to fast-changing environments. Agile promotes iterative delivery, customer collaboration, and responsiveness to change, enabling teams to prioritize working outcomes over exhaustive documentation. Scrum provides a concrete framework for Agile practice through defined roles, time-boxed sprints, daily stand-ups, and regular review cycles that drive continuous improvement. Effective timeline and stakeholder management are foregrounded with practical techniques: setting clear goals, prioritizing tasks (e.g., MoSCoW), maintaining transparent communication, and leveraging tools like Jira, Trello, or Excel trackers. The course highlights how project tracker files centralize task data, visualize progress, and support accountability across sprints. Practical Excel skills — from dropdown lists and conditional formatting to Gantt charts and dynamic dependencies — are introduced as tangible ways to implement Agile artifacts and monitor work. Visual tools such as color coding, burndown charts, Kanban boards, and flowcharts improve clarity, speed decision-making, and engage stakeholders. Building data hierarchies and documenting dependencies ensures information is reliable and easier to maintain as projects evolve. Iterative development, stakeholder feedback loops, and retrospectives are presented as cultural practices that sustain long-term improvement. The course also stresses training, documentation, and stakeholder engagement as necessary complements to technical templates and dashboards. Overall, the material ties conceptual frameworks to hands-on Excel techniques so learners can both plan strategically and execute operationally. By blending methodology with tools and best practices, participants are prepared to deliver projects more predictably while remaining responsive to change.Lesson
Topic: Introduction to Agile, Scrum, and Excel-based Project Tracking
Learning Outcomes
- Explain core Agile principles and the Scrum framework.
- Set up and maintain a project tracker in Excel (tasks, status, priority).
- Create dynamic dropdowns, conditional formatting, and a simple Gantt chart.
- Use visualization techniques (burndown, Kanban, color coding) to communicate progress.
- Document and manage data dependencies and stakeholder communication.
Conceptual Explanation
Project management balances scope, time, and resources to deliver agreed outcomes. Agile is an iterative paradigm that privileges customer collaboration, rapid feedback, and the ability to respond to change. Scrum operationalizes Agile using sprints (short, fixed-length iterations), explicit roles (Product Owner, Scrum Master, Development Team), and ceremonies (daily stand-ups, sprint review, retrospective).
Project tracker files—spreadsheets or PM tools—act as the single source of truth for tasks, owners, dates, statuses, and priorities. In Excel, data validation and named ranges create consistent inputs; conditional formatting and color legends improve rapid assessment; and stacked-bar charts can be formatted as Gantt charts to display timelines visually.
Good practice includes breaking large work into smaller tasks, prioritizing with MoSCoW, updating trackers frequently, engaging stakeholders with regular reports, and running retrospectives to learn and adjust. Technical techniques (VLOOKUP/INDEX+MATCH, INDIRECT, named ranges) help link data across sheets so changes cascade correctly while avoiding circular references.
Bilingual (EN → বাংলা)
| English | বাংলা (Bangla) |
|---|---|
| Agile | অ্যাজাইল |
| Scrum | স্ক্রাম |
| Sprint | স্প্রিন্ট (স্বল্পকালীন পুনরাবৃত্তি) |
| Product Owner | প্রোডাক্ট ওনার (পণ্যের মালিক) |
| Gantt Chart | গ্যান্ট চার্ট (সময়সূচক বার চার্ট) |
| Dependency | নির্ভরতা |
Answer
Use the table to reference common terms during bilingual discussions and training materials. Pronunciations may vary; these translations prioritize clarity for Bengali-speaking learners.
Important Notes
- Keep project trackers single-sourced and access-controlled when shared.
- Prefer named ranges and structured tables over hard-coded cell references.
- Avoid circular references; use iterative recalculation only with caution.
- Align sprint scope with team capacity; undercommit and deliver consistently.
- Document conventions (status values, priority levels, color legend) in a README sheet.
Exercises — True / False / Fill in the Blanks / MCQ / Matching / Descriptive / Creative
True / False
- Agile requires comprehensive up-front documentation before any development begins.
- Daily stand-ups are typically long status meetings lasting over an hour.
- Conditional formatting in Excel can highlight overdue tasks automatically.
Answer
False — Agile values working deliverables and embraces changing requirements over exhaustive up-front documentation.
Answer
False — Daily stand-ups are short (often 15 minutes) to synchronize the team and surface impediments.
Answer
True — Conditional formatting rules can use date comparisons to flag overdue items.
Fill in the Blanks
- MoSCoW stands for Must have, Should have, Could have, and _____.
- The Excel function often used to create a dependent dropdown by referencing a named range is _____.
- A visual chart that shows task durations across calendar time is called a _____ chart.
Answer
Won't have (or Wouldn't have this time).
Answer
INDIRECT.
Answer
Gantt chart.
Multiple Choice (MCQ)
- Which Scrum role is primarily responsible for maximizing product value?
- A. Scrum Master
- B. Product Owner
- C. Development Team
- D. Stakeholder
Answer
B. Product Owner — the Product Owner prioritizes the backlog and represents stakeholder value.
- Which Excel feature helps prevent inconsistent status entries?
- A. Conditional Formatting
- B. Data Validation (dropdown lists)
- C. PivotTable
- D. VLOOKUP
Answer
B. Data Validation (dropdown lists) — establishes a controlled set of allowed values.
Matching
Match the left column (A) with the correct right column (B):
| A | B |
|---|---|
| 1. Burndown Chart | a. Shows workflow columns (To Do / Doing / Done) |
| 2. Kanban | b. Shows remaining work over time |
| 3. Gantt Chart | c. Displays tasks along a calendar timeline |
Answer
1 → b (Burndown chart = remaining work over time), 2 → a (Kanban = workflow columns), 3 → c (Gantt = tasks on calendar timeline).
Descriptive
- Explain how you would set up a simple Gantt chart in Excel from a task table.
- Describe an approach to engage stakeholders using the project tracker.
Answer
Steps: (1) Prepare a table with Task, Start Date, Duration (days). (2) Insert a stacked bar chart using Start Date and Duration as series. (3) Format the Start Date series to be invisible (no fill) so Duration bars appear aligned to calendar dates. (4) Reverse the vertical axis to show tasks top-to-bottom. (5) Adjust axis date scale and add milestones as needed.
Answer
Provide a concise dashboard and periodic exports (weekly PDF or shared workbook), schedule short reviews, highlight risks and decisions needed, and maintain a feedback loop so stakeholder requests are recorded in the backlog and prioritized transparently.
Creative
Design a one-week sprint plan for a three-person team preparing an Excel-based prototype that includes: backlog items, sprint goal, and daily checkpoints.
Answer
Example sprint plan: Sprint Goal — "Deliver a usable Project Tracker prototype with task table, dropdowns, conditional formatting, and a Gantt view." Backlog items — (1) Build task table and named ranges, (2) Add status & priority dropdowns, (3) Implement conditional formatting and color legend, (4) Create Gantt sheet, (5) Prepare README & stakeholder report. Assign: Dev A — tasks 1 & 4, Dev B — tasks 2 & 3, PO/Dev C — documentation, exports, stakeholder demo. Daily checkpoints: 15-minute stand-ups at 10:00 to review progress, blockers, and plan for the day. End of sprint: demo to stakeholders and a 30-minute retrospective to capture improvements.
Activities, Vocabulary, Homework, Practice Task
Activities
- Hands-on lab: Build a 5-task project tracker in Excel with dropdowns and conditional formatting.
- Pair exercise: Convert the task list into a basic Gantt chart and present to a peer.
Answer
Expected outputs: a shared workbook with Task, Assigned To, Start, Due, Status (dropdown), Priority (dropdown), conditional colors, and a Gantt sheet with visible timelines.
Vocabulary
- Backlog
- Sprint
- Burndown
- Named Range
- Conditional Formatting
Answer
Definitions: Backlog = prioritized list of work; Sprint = time-boxed iteration; Burndown = chart of work remaining; Named Range = label for cell range; Conditional Formatting = dynamic cell styling rules.
Homework
- Create a one-sheet Excel tracker for a hypothetical project with at least 6 tasks, using dropdowns and conditional formatting. Save and submit.
- Write a 200-word reflection on how Agile principles changed your approach to planning the tracker.
Answer
Submission should include the workbook (with a README sheet describing columns and color legend) and the reflection document describing choices for prioritization, sprinting, and stakeholder updates.
Practice Task
Create dependent dropdowns: Country → City, using named ranges and INDIRECT. Test with at least three countries and two cities each.
Answer
Implementation tip: list countries in one range and for each country create a same-named range containing its cities. In the city cell use Data Validation with =INDIRECT(A2) where A2 holds the selected country. Validate functionality and handle spaces (use underscores in named ranges or use a lookup table with INDEX/MATCH if names contain spaces).
End of lecture sheet — use this as a compact reference while completing hands-on exercises and home assignments.