The Project Dashboard
The Dashboard is a single sheet that answers “how is this project doing?”: budget against actual cost, how much is finished, what is late, and how the work splits by status, priority and person. It can show one project or every chart in the workbook at once.
Everything on the Dashboard is read from your Gantt charts; you never type into it. Gantt Excel gathers every task row from every chart in the workbook into one hidden table, works out each task’s schedule status, and draws the tiles, the summary panel, the charts and the slicers from that. Change a task on a chart, refresh the Dashboard, and the numbers move.
01 Opening the Dashboard
The Dashboard lives on the ribbon, in the first section:
Click any task row on the Gantt chart you want to look at, so that chart is the active sheet.
Go to Gantt Excel → Gantt Charts → Dashboard. It is a small menu rather than a plain button, so it opens a list of two items.
Choose Project Dashboard. A Creating dashboard…
message appears while Gantt Excel reads every chart in the workbook, and then the Dashboard sheet opens with your chart already selected.
The first time you do this, Gantt Excel creates a new worksheet called Project Dashboard in the workbook. After that the same sheet is reused and rebuilt, so you never end up with a pile of dashboards.
02 A tour of the sheet
Here is the whole Dashboard at a glance. Hover or tap a pin to see what each area does; the numbered key below lists them all.
- Project dropdown. Every chart in the workbook by name, plus All Projects.
- Estimated Budget. The figure you entered for that project on the Edit Project form, shown with its currency symbol.
- Actual Cost. The real costs recorded against the project’s tasks, added up.
- Complete donut. Progress for the current selection, weighted by task duration (section 05).
- Summary panel. Project status, lead, start and end dates, total work, the task count, the three statuses and the Late figure.
- Charts. Task Status and Task Priority, both drawn from the same consolidated data as the counts above.
- Slicers. Status, Priority, Resource and Task Due Date. Click a value to narrow what the charts show.
- Refresh. Re-reads every chart and rebuilds the Dashboard.
- Tasks – Costs. Baseline, planned and actual cost side by side, in the selected project’s currency (section 07).
03 One project, or all of them
The dropdown at the top of the sheet chooses what the Dashboard shows. It lists every Gantt chart in the workbook by its project name, plus one extra entry at the top: All Projects.
Open the Dashboard from the ribbon and it starts on the chart you were standing on, so if you were editing Kitchen Refit, that is what you see. Choose All Projects and every tile, count, chart and slicer switches to the whole portfolio at once: budgets summed, tasks counted across all charts, the earliest start and the latest end of anything in the workbook.
Selecting from the dropdown is usually instant, because Gantt Excel keeps a snapshot of every project in memory and redraws from that rather than re-reading your charts. The exception is the one people meet first. The snapshot does not survive closing the workbook, so the first switch after you reopen the file rebuilds it from scratch. That switch takes a few seconds and picks up everything you have changed since; every switch after it is instant again.
04 The budget tiles
Three tiles across the top compare the budget with the plan and with what has been spent.
- ESTIMATED BUDGET: the figure you typed into Estimated Budget when you set the project up. It is a number you set, not one Gantt Excel works out. To change it, go to Gantt Excel → Gantt Charts → Edit Project.
- ACTUAL COST: the real costs recorded against the project’s tasks, added up for you.
- Over Target: how far the planned cost of the tasks runs over the budget, as a percentage of the budget.
The planned cost is worked out task by task: a task’s Est. Cost where you have typed one, otherwise its Resource Cost, the cost of the people on it at their rates. Summary rows are never added, because their figures are already the sum of the tasks beneath them.
On the kitchen refit, an estimated budget of £12,000.00 against a planned cost of £13,800.00 shows as 15% Over Target, whatever has been spent so far. Plan on or under budget and the tile is blank. There is no “under target” message, so an empty third tile is good news.
05 % Complete is weighted by duration
The donut in the corner shows one number, labelled COMPLETE. Each task’s percentage is weighted by its own estimated duration, so a long task counts for more than a short one. Your summary rows weigh a phase by its length on the calendar instead, so when tasks in a phase run side by side the donut can show a different figure from the top summary row.
The difference weighting makes is large. Imagine a project with two tasks: “Order worktops” takes 1 day and is finished, and “Fit kitchen units” takes 30 days and has not started. A plain average of 100% and 0% reports the project as 50% complete, which flatters it badly. Weighted by duration it reports about 3%. There is no option to switch to a plain average.
A milestone, or any task with no duration, still counts, as one working day. A project with no tasks shows 0%.
For All Projects the same weighting runs across every chart at once, so a 200-task programme counts for more than a 3-task job, and a milestone weighs one working day of its own chart. Averaging each project’s own figure instead would let a 3-task job weigh as much as a 300-task one. The task percentages themselves are a topic of their own: see Understanding % Complete.
06 The project summary panel
Ten rows of plain facts sit beside the tiles. For the kitchen refit they read like this:
| Row | Example | Where it comes from |
|---|---|---|
| Project Status | In Progress | Worked out from the % complete figure above |
| Lead | Priya Raman | The project lead you set on the Edit Project form |
| Start Date | Mon 6 July | The earliest estimated start of any row on the chart |
| End Date | Fri 28 August | The latest estimated end of any row on the chart |
| Total Work | 96 | The Work field added up across the task rows |
| Total Tasks | 18 | Tasks and milestones, no summary rows (see the next section) |
| Completed | 6 | Of those 18 |
| In Progress | 7 | Of those 18 |
| Not Started | 5 | Of those 18 |
| Late | 2 | Of the 12 not finished |
Completed, In Progress and Not Started always add up to Total Tasks, because every task lands in exactly one of them. Late is counted separately: a late task keeps its status as well. Total Work is in the chart’s own unit, days on a daily chart and hours on an hourly one. On All Projects it is converted to hours so the two kinds can be added together.
Project Status uses a deliberately simple rule: 100% is Completed, anything above zero is In Progress, and zero is Not Started. A project is never reported as late as a whole. Its tasks are counted in the Late row.
07 How a task gets its status
The status counted on the Dashboard is calculated, not the Status you chose on the task. Gantt Excel works it out fresh for every task from its % complete alone:
Whether a task is late is a separate question, so a late task keeps its status and is counted in the Late row as well. A task is late when it is under 100% and its finish has passed. On an hourly chart that is the minute its end goes by. On a daily chart it is the end of its last working hour on its end date, so a task due today on a 09:00–17:00 day is late from 17:00, not from midnight.
On the kitchen refit, “First-fix plumbing” is 60% done and was due last Friday. It counts as In Progress and is one of the 2 in the Late row.
Which rows are counted
Not every row on your chart is a task. The Dashboard sorts them out before counting:
- Task counts, % complete and costs all use the same rows: tasks, the child tasks under a summary, and milestones, which count as tasks. Summary (parent) rows are excluded, because their dates, progress and costs are only a roll-up of the children beneath them, and counting both would count the same work twice.
- Costs are added up task by task. The planned cost of each is its Est. Cost, or its Resource Cost when no Est. Cost is typed (section 04). The baseline cost is the Baseline Cost recorded when the baseline was taken, and it does not change when rates change later; see Baselines & actuals.
So a chart with 3 summary rows, 16 tasks beneath them and 2 milestones reports Total Tasks: 18. If that number looks low, count your parent rows; they are almost always the reason.
08 The charts and the slicers
Three charts sit below the tiles, each drawn from the same consolidated data as the counts above, so until you use a slicer the charts and the tiles agree.
- TASK STATUS: the split across Completed, In Progress and Not Started, using the same rule as section 07.
- TASK PRIORITY: High, Normal and Low, using the priority you set on each task.
- TASKS – COSTS: baseline, planned and actual cost side by side, added up from the tasks as in section 07 and formatted in the selected project’s currency.
The two doughnuts are coloured from a fixed set of swatches held on the Dashboard sheet itself, one per status and one per priority, so the same status is always the same colour on every project. These are the Dashboard’s own colours and are separate from your chart’s bar colours, which the Colours & themes guide covers. If the colours ever look stale after a lot of switching about, a tiny red square re-applies them. It is not beside the charts but up in the band of tiles at the top of the sheet, to the right of them.
Four slicers sit alongside: Status, Priority, Resource and Task Due Date. Click a value and the charts narrow to match. The charts are fed by pivot tables on a hidden sheet, and those are what the slicers act on. The tiles, the donut and the summary panel are written straight from the project snapshot instead, so they hold still: a slicer changes what the charts show you, not the headline numbers. Either way only the Dashboard changes; nothing on your Gantt chart is hidden or altered.
These are ordinary Excel slicers, so they behave the way slicers do anywhere in Excel. Clicking a second value replaces your selection rather than adding to it; Ctrl-click to add or remove values one at a time; and to clear a slicer entirely, use the small funnel-with-a-cross button in its own top-right corner.
Task Due Date is the odd one of the four. Instead of a list of values it is a scrolling band of dates, so you drag across it to pick a stretch of time rather than clicking and Ctrl-clicking. Use the MONTHS dropdown in its top-right corner to switch the band between years, quarters, months and days. It opens on the dates of whichever project you have selected, so in a workbook holding several Gantt charts you are not left scrolling to find your own project among everyone else’s. Switch to All Projects and it clears back to All Periods, covering everything in the workbook.
09 What language the Dashboard speaks
Every chart in Gantt Excel can be in its own language. The Dashboard deliberately does not follow suit: it always renders in the App Language, the workbook-wide language setting, whichever project you pick in the dropdown.
That covers all of it: the tile captions, the summary labels, the chart titles, the slicer headings, and the status and priority values inside the charts. Choosing a Spanish project from the dropdown does not switch the Dashboard into Spanish.
The reason is All Projects. If each project brought its own labels, a workbook holding an English chart and a Spanish one would show High and Alta as two separate slices of the same priority chart, and the totals would split in half. Putting everything into one language is what lets projects in different languages be added up.
To change what the Dashboard reads, change the App Language and rebuild it. See Working in another language.
10 Keeping it up to date
The Dashboard is a snapshot, not a live link. Edit a task on a chart and the Dashboard keeps showing the numbers it was built with until you rebuild it. There are two ways to do that, and they are not the same:
“Re-read everything and rebuild”
The Refresh button on the Dashboard sheet and the ribbon’s Project Dashboard item both do the full job: every chart in the workbook is read again, the underlying table is rebuilt, and every tile, count and chart is redrawn.
Use this after you have changed anything on a Gantt chart. On a large workbook it takes a few seconds, which is why it is not automatic.
“Show me a different project”
Choosing a different entry in the dropdown redraws the Dashboard from the snapshot already held in memory. It is instant, it does not re-read your charts, and so it will not pick up an edit you made a minute ago. Press Refresh for that.
There is one exception. The snapshot is not saved with the workbook, so the first time you switch project after reopening the file there is nothing in memory to draw from and Gantt Excel rebuilds it. That one switch takes a few seconds and does show your latest edits.
11 What you need to use it
Excel 2007 cannot run it. The Dashboard is built on pivot tables and slicers that version does not support, and it says so: Your Excel version – 2007 does not support Dashboard as the required pivots and slicers do not work on Excel 2007.
Everything else is ordinary: the Dashboard is a normal worksheet in your workbook, it saves with the file, and you can rename it without breaking anything.
12 Common questions
Why is Total Tasks lower than the number of rows on my chart?
I marked a task In Progress, so why is the Dashboard calling it Not Started?
I picked a Spanish project and the Dashboard stayed in English. Is it broken?
I changed some tasks but the Dashboard has not moved.
Why does the % Complete donut disagree with my own arithmetic?
Can I edit or reformat the Dashboard sheet?
Related: The Web Report · Understanding % Complete · Work, costs & budget · Priority, status & task colours