Requirements

• Excel 2016, 2019, 2021, 2024 or Microsoft 365 on Windows or Mac. Google Sheets, Apple Numbers and Excel Online in a browser are not supported.
• A standard .xlsx workbook – no macros, no add-ins. Every formula is visible and editable.
• Calculation mode must be Automatic (Formulas › Calculation Options). If numbers look stale, press F9.
• You receive two files: a Sample Data edition (15 projects, 20 people, 250+ tasks) to explore every feature and a Blank edition to start your own portfolio.

Quick start – 6 steps

1. 1Set your calendarSettings sheet: tick your weekend days and enter company holidays (US 2026–27 holidays are pre-filled – edit or replace them). Every end date and the Gantt skip these days.
2. 2Check the optionsSettings › Template Options: Edit Mode highlights input cells; Progress Display chooses traffic-light icons or data bars. Status Thresholds set when a project turns Amber or Red, the upcoming-deadline window and workload limits.
3. 3Add your teamResource sheet: enter each person's Employee ID and Name. Workload, task counts and progress are calculated for you.
4. 4Add your projectsProjects sheet: enter Project ID and Project Name. Start, end, duration, progress and RAG status fill in automatically from the tasks.
5. 5List your tasksTasks sheet: Task ID, Project, WBS, Task, Responsible, Start Date, Days Required (working days – enter 0 for a milestone), Status and Progress %. End Date and Planned Progress calculate themselves.
6. 6Open the DashboardUse the Project, Team Member and Task Status filters, switch the Gantt between Day, Week and Month, and tick 🌙 Dark for the dark theme.

How the data flows

You only type in the Settings, Resource, Projects and Tasks sheets. The formula engine calculates end dates, progress, RAG, workload and deadlines, and the Dashboard, alerts and Gantt chart update automatically.

Sheet guide

Dashboard

The Dashboard is the only sheet you need day to day. It reads the three data tables and updates as you type.

Header & filters

• Project Name, Team Member, Task Status – dropdowns at the top; each starts with an All option. A filter cell turns cream when a real filter is applied.
• The title changes to Dashboard: <Project> plus its RAG colour when one project is selected.
• The summary line shows total projects, tasks shown and resources. Tick 🌙 Dark in the title bar to switch theme.

KPI cards & charts

• KPI cards – Projects, Resources, Tasks and total Days for the current filter.
• Project Status – planned vs actual % for every project, with a Red / Amber / Green dot.
• Tasks by Status – Not Started / In Progress / Completed doughnut and an actual vs planned progress ring.
• Timelines – start date, end date, days until launch and total duration.
• Tasks by Progress – how many tasks are at 0%, 1–25%, 26–50%, 51–75%, 76–99% and 100%.
• Resource Workload – completed, in-progress and not-started tasks per person, with a workload dot.

Alerts

• 🚨 Overdue Tasks – unfinished tasks past their end date, sorted by due date, with days late and owner (first 8 shown, “+ n more” note).
• ⏰ Upcoming Deadlines – unfinished tasks due in the next 14 days (set in Settings), soonest first.

Task list

• Shows 60 tasks at a time – drag Slide to Scroll Tasks to move through all of them. A caption shows “Showing a–b of n tasks”.
• The third column can show Responsible, Project or Status (dropdown in its header). Status shows coloured dots: ● Completed, ● In Progress, ● Not Started.
• %Completed shows traffic-light icons or green data bars (Settings › Progress Display). Milestones show ★ in Duration.
• Overdue rows are highlighted; a line separates each project.

Gantt chart

Entering data

Tasks sheet

Projects sheet

• Enter Project ID and Project Name only.
• Calculated: number of tasks, start, end, total days, actual progress, planned progress, overdue count and RAG status.

Resource sheet

• Enter Employee ID and Employee Name only.
• Calculated: tasks, start, end, days, progress, open-task workload and Workload Status – Free, Light, Normal or Overloaded.

Settings

Light & Dark mode

• Tick 🌙 Dark in the Dashboard title bar to switch the whole dashboard to the dark theme; untick for Light.
• In Excel 2016–2024 (no in-cell checkboxes) the cell shows TRUE / FALSE with a dropdown – choose TRUE for Dark.
• Light is best for printing and sharing; Dark is great for big screens and presentations.

Troubleshooting

FAQ

Can I see and change the formulas?

Yes. Nothing is password-locked and there are no macros. Unprotect the Dashboard to edit it.

How many projects, people and tasks can it handle?

Up to 500 task rows and about 50 projects and 50 people. Charts are laid out for around 15 projects and 20 people – more will make them busier.

Does it work on Mac?

Yes – Excel 2016 or later for Mac, including Microsoft 365.

Can I change the colours or labels?

Yes. Edit header text directly; RAG and workload limits are in Settings › Status Thresholds.

What's the difference between the Sample Data and Blank editions?

They are identical; the Blank edition simply has empty Projects, Resource and Tasks tables.

Upgrading from v2

• v3 replaces the separate Dark edition – one file now has a Light / Dark switch.
• Copy your v2 data into the v3 Blank edition: Projects (ID, Name), Resource (ID, Name) and Tasks. Add a Task ID for each task (new first column).
• The Lists sheet is now Settings – re-enter your weekends and holidays there.
• Review Status Thresholds – v3 makes the RAG and workload limits editable.

Version history

See every change in the full Simple Multiple Project Tracking Template changelog ↗.

Questions? Email info@analysistabs.org.