The Project Dashboard

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 then 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:

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 — 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.

1Project dropdown. Pick one project, or All Projects. 2Estimated Budget. The budget you set in Edit Project. 3Actual Cost. Real costs added up from the tasks. 4Complete donut. Progress for the selection, on that chart’s own setting. 5Summary panel. Lead, dates, work, and the task counts. 6Charts. Task Status and Task Priority. 7Slicers. Filter by Status, Priority, Resource or Task Due Date. 8Refresh. Rebuilds the whole Dashboard from your charts. 9Tasks – Costs. Baseline, estimated and actual cost side by side.

1. Project dropdown. Every chart in the workbook by name, plus All Projects.
2. Estimated Budget. The figure you entered for that project on the Edit Project form, shown with its currency symbol.
3. Actual Cost. The real costs recorded against the project’s tasks, added up.
4. Complete donut. Progress for the current selection, worked out the same way that chart works it out (section 05).
5. Summary panel. Project status, lead, start and end dates, total work and the five task counts.
6. Charts. Task Status and Task Priority, both drawn from the same consolidated data as the counts above.
7. Slicers. Status, Priority, Resource and Task Due Date — click a value to narrow what the charts show.
8. Refresh. Re-reads every chart and rebuilds the Dashboard.
9. Tasks – Costs. Baseline, estimated and actual cost side by side, in the selected project’s currency.

03 One project, or all of them

The dropdown at the top of the sheet is the only control you really need. 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 — Gantt Excel keeps a snapshot of every project in memory and redraws from that rather than re-reading your charts. There is one exception, and it 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 does pick up everything you have changed since; every switch after it is instant again.

04 The budget tiles

Three tiles sit across the top, and they answer the money question in one line.

• 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 — the gap between them, as a percentage of the budget.

On the kitchen refit, an estimated budget of £12,000.00 against an actual cost of £13,800.00 shows as 15% Over Target. Come in on or under budget and the tile is simply blank — there is no “under target” message, so an empty third tile is good news.

05 % Complete follows each project’s own setting

The donut in the corner shows one number, labelled COMPLETE, and it is the same number your chart shows. Each Gantt chart carries its own Percentage Complete setting — Weighted or Simple — and the Dashboard reads that chart’s setting rather than imposing one of its own.

On Weighted, the default, a long task counts for more than a short one:

On Simple, every task counts once, whatever its length — a plain average of the task percentages.

The reasoning behind weighting is worth a moment, because the difference 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% — which is the truth. Simple is there for plans where the durations are rough and every task really is a comparable piece of work; if that is not your plan, leave it on Weighted.

A task with no duration still counts, as one task, exactly as it does on the sheet. Milestones are different: they do not enter the sum at all. The Dashboard sorts every row by what it is before it weights anything, and a milestone is not a task row (section 07 has the full list) — so it is left out rather than counted as a zero. A project with no tasks shows 0%.

For All Projects the arithmetic is always duration-weighted across every chart at once, whatever the individual charts are set to — so a 200-task programme counts for more than a 3-task job, which is what you would expect. That is deliberate: “Simple” has no meaning across projects, because averaging each project’s own average would let a 3-task job weigh as much as a 300-task one, and honouring a per-chart setting would build one combined figure out of two different rules. It is the only roll-up that stays honest when a workbook mixes the two settings. 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:

The four counts always add up to Total Tasks, because every task row lands in exactly one of them. Total Work is in the chart’s own unit — days on a daily chart, hours on an hourly one — and 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 overdue as a whole — individual tasks are, on the row below.

07 How a task gets its status

This is the part that surprises people, so it is worth stating plainly: the status counted on the Dashboard is calculated, not the Status you chose on the task. Gantt Excel works it out fresh for every row, from two things only — the task’s % complete and its estimated end date:

The order matters. A task that is 40% done but whose end date passed last Friday counts as Overdue, not In Progress — the overdue test is applied first, on purpose, because a late task is the more useful thing to know. A task ending today is not overdue.

Which rows are counted

Not every row on your chart is a task. The Dashboard sorts them out before counting:

• Task counts and % complete use the rows that hold real work — tasks and the child tasks under a summary. Summary (parent) rows are excluded, because their dates and progress are only a roll-up of the children beneath them; counting both would double-count the same work. Milestones are excluded too — they are moments, not work.
• Costs are added up from the top level of the outline only — a task that sits at the top level, or a top-level summary row, which already carries the total of everything nested beneath it. That is what stops the same pound being counted twice.

So a chart with 3 summary rows, 18 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, and each is drawn from the same consolidated data as the counts above — so a chart can never disagree with a tile.

• TASK STATUS — the split across Completed, In Progress, Not Started and Overdue, using the same rule as section 07.
• TASK PRIORITY — High, Normal and Low, using the priority you set on each task.
• TASKS – COSTS — baseline, estimated and actual cost side by side, 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, there is a tiny red square that re-applies them — it is not down 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 — they are fed by pivot tables on a hidden sheet, and it is those 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 it is the Dashboard only — nothing on your Gantt chart is hidden or changed.

These are ordinary Excel slicers, so they behave the way slicers do anywhere in Excel rather than in some Gantt-specific way. 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.

09 What language the Dashboard speaks

Every chart in Gantt Excel can be in its own language. The Dashboard is the one place that 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. Normalising everything to one language is what lets projects in different languages be added up at all.

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:

11 What you need to use it

One requirement, worth knowing before you go looking for the button.

Excel 2007 cannot run it. The Dashboard is built on pivot tables and slicers that version does not support, and it says so rather than leaving you guessing: 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

Related: The Web Report · Understanding % Complete · Work, costs & budget · Priority, status & task colours