Coursya originals · Free PDF ebook

How to Build a Project Dashboard in Excel

Build a project dashboard in Excel with a reproducible eight-task example, structured-reference formulas, overdue logic, cost totals, PivotTable checks and a worked chart.

Project Monitoring & KPIs

Build a project dashboard in Excel with a reproducible eight-task example, structured-reference formulas, overdue logic, cost totals, PivotTable checks and a worked chart.

The tutorial separates inputs, calculations and display, then verifies every sample result. It uses English Excel formulas and an explicit report date, with notes on locale and version differences that affect the steps.

Build a dashboard that supports a decision

Start with the project question before choosing charts. A simple workbook structure makes the data easier to maintain and check. A consistent table gives formulas a reliable structure. Use the same data to check your results later.

The table gives the sample status and due date for each ID. Enter dates as actual date values, using an unambiguous display such as 20-Oct-2026.

Enter the sample cost data

Keep task budgets and actual costs on a consistent basis. Structured references make the formulas easier to extend and read. A correct formula can still give a misleading result from inconsistent data. Use one explicit date so every overdue calculation uses the same cutoff.

A task due on the reporting date is not overdue under this tutorial’s rule. Open work due before the date is overdue; Done items are excluded.

Count the task population

Every percentage needs a defined denominator. Keep the count and the percentage definition visible. Use a rule that excludes missing dates and completed tasks. Show clear cost labels rather than implying a forecast from spending alone.

=SUM(Tasks[Budget]) – SUM(Tasks[Actual]) gives $1,750. Label it Budget less actual, or another accurate label under your reporting convention.

Summarise cost by owner

Grouped totals help the reader see where the recorded cost sits. Charts work best from a small, checkable summary. Choose a visual that makes the comparison easy to read. A PivotTable can summarise the same source for additional views.

The sample should again show 4, 3 and 1. An owner-cost view can use Owner in Rows and Actual in Values as a Sum.

Highlight overdue rows

A visual rule should implement the same logic as the metric. A compact view should show the status date and the decisions it supports. Check both arithmetic and behaviour when the data changes. A dashboard needs a repeatable source and review process.

Update the input, validate records, set ReportDate, recalculate as required, refresh summaries and inspect the exception list before distribution.

The worked dashboard result

Use this page to reconcile the finished view with the tutorial data. A useful dashboard combines reliable input, correct calculation and clear meaning.

Do formula totals, grouped summaries, PivotTables, charts and formatting reconcile? Are refresh and data-quality responsibilities clear?

Contents

  1. Build a dashboard that supports a decision
  2. Separate input, calculations and display
  3. Use one row per task
  4. Enter the sample task states
  5. Enter the sample cost data
  6. Create an Excel Table
  7. Validate the input
  8. Set a reproducible reporting date
  9. Count the task population
  10. Calculate Done and completion rate
  11. Count overdue open work
  12. Calculate budget and actual totals
  13. Summarise cost by owner
  14. Create a status summary
  15. Add a simple chart
  16. Use a PivotTable where helpful
  17. Highlight overdue rows
  18. Arrange a readable dashboard
  19. Test before sharing
  20. Define the update routine
  21. The worked dashboard result
  22. Your Excel-dashboard desk check

Using the PDF

Each numbered page contains an explanation, an original worked situation and a practical action. The bookmarks navigate the learning pages. A templates advertisement follows content page 5 and a Coursera Plus advertisement follows content page 15. The entire advertisement image is linked. Promotional links may earn Coursya a commission. A purchase is optional.

Frequently asked questions

Who is this guide for?

Build a project dashboard in Excel with a reproducible eight-task example, structured-reference formulas, overdue logic, cost totals, PivotTable checks and a worked chart.

How can I practise the ideas?

Follow the running case, work through the page actions and use the PDF bookmarks to revisit a topic. The final learning pages help you apply the decisions to your own project.

What does the download contain?

The PDF contains 25 pages, including 22 numbered learning pages, a cover and two linked advertisements. The book is written in English.

Share this article