Project Management Tracker with Excel Automation Course

Lesson 1 • Organizing Data with Tables

Introduces Excel Tables as structured containers for project data. Tables auto-expand and simplify formula references throughout the tracker.

Lesson 2 • Sorting, Filtering, and Basic Search

Demonstrates sorting and filtering to locate project records quickly. These skills underpin dashboard interactivity built in later chapters.

Lesson 3 • Navigating the Excel Interface

Covers ribbons, quick access toolbar, and workbook structure. Establishes the workspace familiarity needed for all subsequent tracker-building tasks.

Lesson 1 • Creating Supporting Reference Sheets

Adds team roster, milestone, and risk register sheets that feed the task register. Separation of concerns keeps data clean and formulas maintainable.

Lesson 2 • Protecting and Sharing the Workbook

Locks formula cells and sets sheet-level permissions to prevent accidental edits. Sharing settings control who can view or modify tracker data.

Lesson 3 • Applying Data Validation Rules

Enforces consistent input using dropdown lists, date constraints, and numeric limits. Validation reduces manual errors that corrupt tracker integrity.

Lesson 4 • Designing the Task Register Sheet

Builds the central task list with ID, owner, dates, status, and priority columns. This sheet becomes the data source for all dashboards and reports.

Lesson 1 • Logical Formulas for Status Automation

Applies IF, AND, OR, and nested logic to auto-generate status labels and alerts. Logic formulas eliminate manual status updates and reduce human error.

Lesson 2 • Lookup and Reference Functions

Teaches VLOOKUP, XLOOKUP, and INDEX-MATCH to pull data across sheets. Cross-sheet lookups connect the task register to roster and milestone sheets.

Lesson 3 • Text and Data Cleaning Functions

Applies TRIM, CONCATENATE, TEXT, and LEFT/RIGHT to standardize imported data. Clean text prevents lookup failures and inconsistent dashboard labels.

Lesson 4 • Aggregation and Summary Formulas

Uses SUMIF, COUNTIF, AVERAGEIF, and SUMPRODUCT to summarize project data by category. These formulas feed KPI metrics displayed on the dashboard.

Lesson 1 • Conditional Formatting Fundamentals

Introduces rule types, priority order, and the formatting rules manager. Understanding rule hierarchy prevents conflicts that produce incorrect highlights.

Lesson 2 • Gantt-Style Timeline Highlighting

Creates a Gantt-like bar chart using conditional formatting on a date grid. This technique produces a lightweight schedule view without external tools.

Lesson 3 • Formula-Based Conditional Rules

Builds custom rules using formulas to flag overdue tasks, budget overruns, and at-risk items. Formula-driven rules adapt dynamically as tracker data changes.

Lesson 1 • Designing the Project Dashboard Layout

Arranges KPI tiles, charts, and summary tables on a dedicated dashboard sheet. Layout principles ensure the dashboard is readable at a glance for executives.

Lesson 2 • Dynamic Chart Ranges with Named Ranges

Uses named ranges and OFFSET to make charts update automatically as data grows. Dynamic ranges eliminate manual chart editing after each project update.

Lesson 3 • Slicers and Timeline Controls

Adds slicers and timeline filters to enable one-click dashboard filtering. Interactive controls let stakeholders explore data without touching raw sheets.

Lesson 4 • Choosing the Right Chart Type

Matches project metrics to appropriate chart types such as bar, line, and pie. Correct chart selection ensures stakeholders interpret data accurately.

Lesson 1 • Calculated Fields and Items

Creates custom metrics such as variance and completion rate inside the PivotTable. Calculated fields extend reporting without altering the source data.

Lesson 2 • PivotTable Fundamentals

Explains the field list, rows, columns, values, and filters areas. Mastering the layout panel is the prerequisite for all advanced PivotTable reporting.

Lesson 3 • Summarizing Project Data by Category

Groups tasks by owner, status, phase, and priority to reveal workload distribution. Category summaries expose imbalances that threaten project delivery.

Lesson 1 • Recording and Running Macros

Uses the macro recorder to capture formatting, filtering, and copy-paste sequences. Recorded macros provide a safe entry point before writing VBA code.

Lesson 2 • Error Handling and Macro Maintenance

Implements On Error statements and debugging tools to make macros robust. Reliable error handling prevents tracker corruption when unexpected data appears.

Lesson 3 • Writing VBA Procedures for the Tracker

Writes Sub procedures to auto-populate dates, clear completed tasks, and send status emails. Practical procedures deliver immediate time savings on real projects.

Lesson 4 • Automating Reports with VBA

Builds a macro that copies filtered data to a new sheet and formats a printable report. Automated reporting eliminates manual copy-paste before every status meeting.

Lesson 1 • Workbook Performance Optimization

Identifies and fixes slow calculation, excessive volatility, and bloated file size. A fast workbook sustains adoption when project data scales to hundreds of tasks.

Lesson 2 • Power Query for Data Import and Cleaning

Uses Power Query to import, merge, and clean data from external sources automatically. Automated data refresh eliminates manual copy-paste from source systems.

Lesson 3 • Scalable Multi-Project Architecture

Designs a master workbook that consolidates data from multiple project tracker files. Centralized consolidation gives portfolio managers a single source of truth.

Lesson 4 • Tracker Handover and Documentation

Creates user guides, in-cell comments, and a change log to support tracker handover. Thorough documentation ensures continuity when project ownership transfers.