How to Create a Project Dashboard in Google Sheets

Tracking a project's moving parts can feel like juggling chainsaws. Your tasks are in one tool, deadlines are in a calendar, and team assignments are scattered across emails or Slack messages. This article will show you how to pull it all together by building a clean, automated project management dashboard right inside Google Sheets.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

First Things First: Why a Google Sheets Dashboard?

Before building, let's understand why a Google Sheets dashboard is such an effective tool for project management. While not as specialized as platforms like Asana or Trello, it offers a unique combination of flexibility, accessibility, and zero cost.

The main benefit is having a single source of truth. Instead of hunting for information, you get a high-level overview of key project metrics in one place. You can instantly see what's on track, what's falling behind, and who is responsible for what. Because Sheets is cloud-based and collaborative, your entire team can view and update the dashboard in real-time, keeping everyone aligned.

Step 1: Build Your Foundation with a Clean Data Tab

Every great dashboard starts with well-organized data. The biggest mistake people make is trying to build charts and summaries on a messy, unstructured sheet. The key is to separate your raw data from your dashboard presentation.

Let's set up the data source for our project management dashboard.

1. Create a new Google Sheet.
2. Rename the first tab at the bottom to "Dashboard." This will be our presentation layer.
3. Click the "+" icon in the bottom-left to add a new sheet. Rename this tab to "Project Data." This is where we'll log all project details.

On your "Project Data" tab, create a simple table structure. Every column is a piece of information you want to track, and every row will represent a single task. Here is a solid starting point for your headers in row 1:

• Task ID: A unique identifier for each task (e.g., A-01, A-02).
• Task Name: A brief description of the task.
• Project Phase: The larger project stage this task belongs to (e.g., Planning, Design, Execution, Launch).
• Assigned To: The name of the team member responsible.
• Start Date: The date the task is scheduled to begin.
• Due Date: The deadline for the task.
• Status: The current state of the task (e.g., Not Started, In Progress, Blocked, Completed).
• Priority: The urgency of the task (e.g., High, Medium, Low).

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Pro Tip: Use Data Validation for Consistency

To avoid typos and ensure consistent data entry (like "Complete" vs. "Completed"), use Data Validation to create dropdown menus for columns like "Status," "Assigned To," and "Priority."

1. On the "Project Data" sheet, highlight the entire "Status" column (by clicking the column letter, e.g., 'G').
2. Go to the menu and click Data > Data Validation.
3. Click "+ Add rule".
4. For "Criteria," choose "Dropdown".
5. Enter your status options into the dropdown fields: Not Started, In Progress, Blocked, Completed.
6. Click "Done". Now, anyone filling in that column will have to choose from your pre-defined list, keeping your data tidy and accurate.

Do this for any column where you want to enforce a standard list of options.

Step 2: Calculate Your Key Performance Indicators (KPIs)

Now, let's switch over to your "Dashboard" tab. This is where we will visualize the data from our "Project Data" tab. We'll start by creating a summary section for our most important top-line numbers.

Set aside a section at the top of your dashboard for these quick stats. For a project dashboard, you'll likely want to track:

• Total Tasks
• Completed Tasks
• Tasks In Progress
• Overdue Tasks

Against each of these labels, we will write a formula that automatically pulls and calculates the data from our "Project Data" tab.

Total Tasks: This formula counts all non-empty cells in your Task Name column.

=COUNTA('Project Data'!B2:B)

Completed Tasks: This formula counts the number of tasks where the status is "Completed".

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

Tasks In Progress: This counts tasks where the status is "In Progress".

=COUNTIF('Project Data'!G2:G,"In Progress")

Overdue Tasks: This one is a bit more advanced. It counts tasks that are not marked "Completed" and where the "Due Date" is in the past.

=COUNTIFS('Project Data'!F2:F,"<"&TODAY(),'Project Data'!G2:G,"<>Completed")

These formulas create a dynamic summary. As you add or update tasks on the "Project Data" tab, these numbers will refresh automatically.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Create Visualizations with Charts and Graphs

Numbers are great, but visuals are better for at-a-glance insights. Now we'll add some charts to our dashboard to help us quickly understand the project's health.

Tasks by Status (Pie Chart)

A pie chart (or a doughnut chart) is perfect for showing the composition of your project's tasks by their current status.

1. First, we need to create a small summary table on our dashboard sheet to power the chart. Create these headers: Status and Count.
2. Under Status, list out your possible statuses: Not Started, In Progress, Blocked, Completed.
3. Beside each status, use a COUNTIF formula to get the total count from your data tab. For example, next to "Not Started" you'd use:

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

1. Repeat this for all your statuses.
2. Now, highlight your new summary table (both the statuses and their counts).
3. Go to the menu and click Insert > Chart.
4. Google Sheets will likely default to a Pie Chart. If not, select it from the Chart editor on the right. You can also change it to a Doughnut chart for a cleaner look under Chart type > Pie > Doughnut chart.
5. Customize the colors and title to fit your dashboard's style.

Task Load by Team Member (Bar Chart)

Want to see if work is distributed evenly? A bar chart is ideal for comparing task totals across your team members.

1. Just like before, create a small helper table on your dashboard sheet. Use the headers Team Member and Task Count.
2. List your team members' names under the "Team Member" column.
3. Use a COUNTIF formula to count tasks assigned to each person, referencing the "Assigned To" column (column D) in your "Project Data" tab.

=COUNTIF('Project Data'!D2:D, "Jane Doe")

1. Highlight the summary data, click Insert > Chart, and select a Bar Chart from the Chart editor.

Project Progress with Sparklines

Sparklines are tiny, simple charts that live inside a single cell. They are fantastic for adding quick visual progress bars without cluttering your dashboard.

Let's create a progress bar for the entire project based on the percentage of completed tasks. First, we need to calculate that percentage. In an empty cell, use this formula:

=(CompletedTasksCell / TotalTasksCell)

Replace CompletedTasksCell and TotalTasksCell with the cell references for your KPI calculations (e.g., B2/B1). Then, format this cell as a percentage. In the cell next to it, we can insert the Sparkline formula:

=SPARKLINE(PercentCompleteCell, {"charttype","bar","max",1,"color1","green"})

This formula creates a simple green progress bar that fills up a cell as your completion percentage increases. A nice, simple visual cue for how close to "Done" you really are.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Make Your Dashboard Interactive with Slicers

A static dashboard is useful, but an interactive one is far more powerful. Slicers are Google Sheets' answer to interactive filters, letting you (and your team) easily drill down into the data without having to touch complex filter views.

Let's add a slicer to filter our entire dashboard by Team Member.

1. First, click on any of your charts. It's often helpful to first click on ONE chart before you insert the slicer, because this will connect the slicer to all charts created from the same data set.
2. From the menu, choose Data > Add a slicer.
3. A slicer element will appear on your dashboard. In the Slicer settings pane on the right, for the "Column" dropdown, choose "Assigned To".
4. You can move and resize the slicer to a convenient spot on your dashboard.

Now, you can use the slicer's dropdown menu to select a specific team member. All of your charts and (if set up correctly) your KPI numbers connected to that data range will automatically update to reflect only that person's tasks. Add another slicer for "Status" or "Priority" to give your team even more power to explore the project data.

Final Thoughts

By structuring your data properly, using a few key formulas, and adding charts and slicers, you can transform a simple spreadsheet into a dynamic and genuinely useful project dashboard. This central hub will save you countless hours searching for information and keep your entire team on the same page, empowering everyone to make smarter, data-driven decisions.

Manually building this out in Google Sheets is powerful, but you still have to set up all the formulas and bring in data from other platforms by hand. We wanted to make this process easier, which is why we built Graphed. Instead of building chart-by-chart, you can connect sources like Asana, Jira, or a Google Sheet and simply ask for what you want in plain English, like "Show me a dashboard of incomplete and overdue tasks by team member," and have a real-time dashboard built for you instantly. If that sounds interesting, you can see how it works at Graphed.