Custom Project Status Dashboard in Google Sheets

Published on

This tutorial walks you through the features and functionality of a custom-built Project Status Dashboard in Google Sheets. This dashboard provides a comprehensive overview of your project’s progress with three key visualizations and supporting data tabs.

You can find the download link for the template at the end of this tutorial.

Key Features of the Project Status Dashboard

  1. Scorecard Chart: Displays the total percentage of project completion out of 100 weightage.
  2. Pie Chart: Shows the percentage of tasks categorized by their status (e.g., “In Progress,” “Completed,” “Not Started”).
  3. Combo Chart: Provides a detailed comparison of scheduled vs. actual weightage, along with cumulative schedule and cumulative percentage progress.
Example of a custom-built project status dashboard in Google Sheets

In addition to the “Dashboard” tab, there are two other tabs in the sheet named “Schedule” and “Timeline.” The dashboard uses the data from these tabs to plot the charts. Let’s understand how to use these tabs.

Schedule Tab for Weekly Progress Tracking Table

The Schedule tab contains the following table:

Construction project progress tracking table in Google Sheets

The purpose of this table is to help project managers compare planned (scheduled) progress against the actual progress achieved, providing both weekly and cumulative insights. Below is a breakdown of the columns and their significance:

  1. Week (Column A): Represents the sequential week number in the project schedule.
  2. Weekly Sch. (Column B): Indicates the percentage of the total work planned for completion during the respective week. This value is derived from the project’s weightage distribution.
  3. Weekly Actual (Column C): Shows the actual percentage of work completed during the respective week.
  4. Cumulative Sch. (Column D): Represents the cumulative percentage of work planned to be completed by the end of each week.
  5. Cumulative Actual (Column E): Reflects the cumulative percentage of actual work completed by the end of each week.

Instructions:

  • Enter weightage values in Column B (B6:B) corresponding to each week. Total weightage is distributed among tasks based on their importance or effort.
  • Record your actual percentage completion in Column C (C6:C).
  • Columns D and E will be calculated automatically.

The data in this table will be used to create the Scorecard Chart and the Combo Chart in the Project Status Dashboard sheet.

To add more records to the table, hover over the last cell in the first column. A + button will appear. Click it to insert a new row with the formulas in Columns D and E automatically filled.

What If I Have a Monthly Schedule?

If you want to create a project dashboard to monitor monthly progress:

  • Replace week numbers in Column A with month names.
  • Enter the monthly schedule (% weightage distribution) in Column B.
  • Record the monthly progress in Column C.
  • Update table titles accordingly.

Using the Timeline Tab for Task Breakdown

The Timeline tab provides a breakdown of tasks for the Gantt chart and Pie Chart.

Timeline view in the project status dashboard report in Google Sheets

Instructions:

  1. Project Dates: Enter your project start and end dates in C6 and D6, respectively.
  2. Task Details:
    • Enter task names in A7:A, leaving one empty cell between each task, as two rows are dedicated to each task (one for scheduled start and end, and another for actual start and end).
    • Input the scheduled and actual task start and end dates in Columns C7:C and D7:D.
    • Select the status of each task in F7:F (e.g., “Not Started,” “In Progress,” “Completed”).
  3. Adjust Rows:
    • Check row #6 for the last non-empty column with a date.
    • Adjust the merging of rows below accordingly.
  4. Add/Delete Rows:
    • If you have fewer tasks, delete unnecessary rows. If you have more tasks, copy the formulas in Column G down to accommodate additional tasks.

This tab not only supports the Project Status Dashboard but also provides a clear picture of task progress.

Downloading the Project Status Dashboard

To get the free Project Status Dashboard, click the button below:

Download Dashaboard Template

This will open a preview of the template. Make a copy of it to start using it.

Resources

Here are some additional resources to help you enhance your project tracking in Google Sheets:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

How to Filter by Total in Google Sheets Pivot Tables

Google Sheets offers many tools to summarize and analyze data, but Pivot Tables are...

More like this

Cycle Highlights in Google Sheets – Rotate Highlights Daily

Want to cycle highlights in Google Sheets every day? Whether you're rotating a meal...

Filter Rows Containing Multiple Selected Values in Google Sheets

This tutorial explains how to filter rows in a column containing multiple selected drop-down...

Two-Way Lookup with XLOOKUP in Google Sheets

When you need to look up one search key vertically and another horizontally, you...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.