Graphed LogoGraphed
Login

How to Create a Project Management Dashboard in Google Sheets

Cody Schneider•

A good project management dashboard can turn a chaotic mess of tasks, deadlines, and responsibilities into a clear, single view of your project's health. While dedicated software exists, you can build a surprisingly powerful and customized dashboard for free using Google Sheets. We'll show you exactly how to transform a simple task list into an interactive dashboard that provides at-a-glance insights.

Why Use a Google Sheets Dashboard?

Before diving into the steps, it’s worth asking: why Google Sheets? For many teams, especially those without a big budget for specialized software, it’s the perfect starting point.

  • It’s Free and Accessible: Anyone with a Google account can use it, and you can access your dashboard from any device.

  • Highly Collaborative: Real-time collaboration is built-in. Your entire team can update tasks simultaneously, and the dashboard will reflect those changes instantly.

  • Completely Customizable: You're not restricted by a software vendor's pre-built widgets. You decide what metrics matter most and how to display them.

  • Familiar Interface: Most people have some experience with spreadsheets, making the learning curve much less intimidating than a complex new business intelligence tool.

Step 1: Build Your Raw Data Foundation (The "Tasks" Tab)

Every great dashboard is built on a foundation of clean, organized data. You cannot have a functional dashboard without a reliable source of truth. The first step is to create a new sheet dedicated to tracking every single task.

Create a new tab and name it something like "Project Data" or "Tasks." This sheet is for data entry only - we won't have any charts here. Create columns for all the essential project details. Here’s a great starting point:

  • Task ID: A unique identifier for each task (e.g., A-01, A-02).

  • Task Description: What needs to be done?

  • Assigned To: Who is responsible for the task?

  • Status: The current stage of the task (e.g., Not Started, In Progress, Blocked, Completed).

  • Priority: The urgency (e.g., High, Medium, Low).

  • Start Date: When the task is scheduled to begin.

  • Due Date: When the task needs to be completed.

  • Completion Date: The actual date the task was finished.

Pro Tip: Use Data Validation for Clean Data

To keep your data consistent, use data validation to create dropdown lists for columns like "Status," "Priority," and "Assigned To." This prevents typos and variations (e.g., "Complete" vs. "Completed") that will break your dashboard formulas.

  1. Select the entire "Status" column.

  2. Go to Data > Data validation.

  3. Under "Criteria," choose a list from a range.

  4. Enter your statuses in a separate area (or just type them directly into the rule criteria: "Not Started, In Progress, Blocked, Completed").

  5. Click "Save."

Now, when you click a cell in that column, a dropdown menu will appear, ensuring standardized inputs.

Step 2: Design Your Dashboard Tab

Now for the fun part. Create a second tab and name it "Dashboard." This is where all your visualizations will live. Keep it clean and organized. The goal is to get a complete project overview in under 30 seconds.

What should a project management dashboard show you? Here are a few key components we will build:

  • Overall Project Progress: A summary card showing the percentage of completed tasks.

  • Tasks by Status: A pie or donut chart showing the distribution of tasks in each stage.

  • Team Workload: A bar chart showing how many tasks are assigned to each team member.

  • Upcoming & Overdue Tasks: A dynamic list that flags which tasks are falling behind.

Step 3: Build Your Dashboard with Formulas and Charts

This is where spreadsheet magic happens. We'll use a few key formulas to pull data from your "Project Data" tab and summarize it on your "Dashboard" tab.

Overall Project Progress Bar

First, we need to calculate the percentage of completed tasks. In a cell on your Dashboard sheet, you can calculate this using the COUNTIF function. Let's find out how many tasks are "Completed" versus the total number of tasks.

You’ll need two formulas:

  • To count completed tasks:

    =COUNTIF('Project Data'!D:D, "Completed")

  • To count total tasks:

    =COUNTA('Project Data'!B:B) (We use COUNTA on the Task Description column to count any row with a task).

To get the percentage, simply divide them:

=COUNTIF('Project Data'!D:D, "Completed") / COUNTA('Project Data'!B:B)

Format this cell as a percentage to see your progress. For a nice visual, you can use the SPARKLINE function to create a simple in-cell progress bar:

=SPARKLINE( A1 , {"charttype","bar","max",1, "color1", "#4caf50"})

(Assuming your percentage calculation is in cell A1. "max",1 tells the formula that the bar is full at 100%, or 1.)

Tasks by Status (Donut Chart)

A donut chart is perfect for visualizing how tasks are distributed across different statuses. First, we need to create a summary table on our Dashboard tab.

Set up a small table with the headers "Status" and "Count." Under "Status," list your categories: Not Started, In Progress, Completed, etc. In the "Count" column next to "Not Started," use this formula:

=COUNTIF('Project Data'!D:D, "Not Started")

Repeat this formula for each status type, updating the text inside the quotation marks.

Once your summary table is filled, highlight it, go to Insert > Chart, and select "Donut chart" from the chart editor. You'll instantly see a snapshot of your project's workflow.

Team Workload (Bar Chart)

Understanding who is doing what is critical for resource management. Similar to the status chart, we can create a bar chart to visualize task allocation.

Create another summary table for "Team Member" and "Tasks Assigned." Use the same COUNTIF pattern, but this time reference your "Assigned To" column:

=COUNTIF('Project Data'!C:C, "Sarah")

Repeat for each team member. Once done, highlight the data, go to Insert > Chart, and choose a "Bar chart" or "Column chart" to see the workload distribution.

Dynamic List of Overdue Tasks

This is one of the most powerful and action-oriented parts of the dashboard. We want a list that automatically shows any task that is past its due date and is not marked as "Completed." The FILTER function is perfect for this.

In a section of your dashboard, create headers for Task Description, Assigned To, and Due Date.

In the cell below "Task Description," enter this formula:

=FILTER('Project Data'!B:D, 'Project Data'!G:G < TODAY(), 'Project Data'!D:D <> "Completed")

Let’s break it down:

  • 'Project Data'!B:D: This is the data we want to display (Task, Owner, Status).

  • 'Project Data'!G:G < TODAY(): This is our first condition. It checks if the "Due Date" column is before today's date.

  • 'Project Data'!D:D <> "Completed": Our second condition. It checks that the "Status" column is not equal to "Completed."

This formula pulls a live list of only the tasks that need immediate attention. When a task is completed or its due date is changed, it will automatically disappear from this list.

Step 4: Refine and Automate

Once your components are built, spend some time on aesthetics. Use colors and spacing to make the dashboard easy to read. Move your summary tables for charts to a hidden area or another tab to keep the main view clean.

Some final tips:

  • Protect Ranges: Right-click on your Dashboard tab and select "Protect sheet." This prevents anyone from accidentally editing formulas, allowing changes only on the "Project Data" tab.

  • Use Conditional Formatting: Highlight overdue tasks in your "Project Data" tab by setting a rule that changes the cell color to red if the due date is in the past and the status isn't "Completed." This adds another layer of visibility.

  • Keep it Simple: The most common mistake is cramming too much information onto one dashboard. Focus only on the metrics that drive decisions. If a chart isn't telling you something actionable, it might just be noise.

Final Thoughts

Building a project management dashboard in Google Sheets is a fantastic way to take control of your projects without spending a dime on extra software. By structuring your data properly and using a handful of powerful formulas, you can transform a static spreadsheet into a dynamic, real-time command center for your entire team.

While Google Sheets is an amazing and flexible tool, we know that the initial setup - and the continuous manual logging of task statuses - can become a chore as projects grow. We built Graphed to solve this very problem. Instead of wrestling with formulas, you can connect directly to your data sources (like your project management tools, sales CRM, and ad platforms) and simply describe the dashboard you want in plain English. Graphed automatically builds and refreshes your reports, giving you back the hours you'd spend downloading CSVs and updating your dashboard by hand.