How to Create a Project Management Dashboard in Google Sheets with ChatGPT
Wrangling projects in spreadsheets often feels like a necessary evil, but it doesn’t have to be. You can turn a standard Google Sheet into a dynamic, automated project management dashboard that keeps everyone on the same page. This guide will walk you through exactly how to build one from the ground up, using ChatGPT as your personal formula expert to make the process faster and easier.
Why Use Google Sheets for Project Management?
While dedicated tools like Asana and Trello are fantastic, they come with a learning curve and a price tag. Google Sheets, on the other hand, is free, infinitely flexible, and built for collaboration. It's the perfect place to start, especially when you can level up its capabilities without knowing complex formulas yourself.
Here’s the simple truth: your dashboard will only be as good as the data you put into it. Keeping your raw data clean and organized is the most important step. When you add ChatGPT to the mix, you effectively have a data analyst on-call who can generate the exact formulas you need - no more digging through help articles or trying to remember obscure functions.
Step 1: Set Up Your Core Project Data Sheet
First things first, let's build the foundation. All your dashboard's charts and stats will pull from a single, well-structured data tab. Open a new Google Sheet and create a tab named "Project Tracker" (or something similar). Here are the essential columns we recommend setting up:
Task Name: A clear, concise description of the task. (e.g., “Draft Q3 Marketing Blog Post”).
Project/Category: Which larger project or initiative this task belongs to. (e.g., “Content Marketing,” “Website Redesign”).
Assigned To: The name of the person responsible for the task.
Start Date: When the task is scheduled to begin.
Due Date: The deadline for task completion.
Status: The current state of the task. Using a dropdown menu here is critical for data consistency. Set this up by clicking on the Status column header, going to Data >, Data validation, and adding items like "Not Started," "In Progress," "Blocked," and "Completed."
% Complete: A numerical value from 0 to 100 representing how far along the task is. Format this column as a percentage.
Priority: The urgency of the task. Like the Status column, use data validation to create a dropdown menu with options like "High," "Medium," and "Low."
Go ahead and populate this sheet with a few example tasks. Having some sample data will make it much easier to build and test your dashboard visuals in the next steps.
Step 2: Use ChatGPT to Generate Your Key Formulas
Now for the fun part. Instead of wrestling with formulas, we'll ask ChatGPT to write them for us. Create a new tab in your Google Sheet and name it "Dashboard." This is where we'll build the visual components.
The trick to getting great results from ChatGPT is to be specific in your prompts. Mention your sheet name ('Project Tracker') and the exact columns you're referencing. Here are a few examples to create the main KPIs for our dashboard.
Example 1: Counting Tasks by Status
First, we want an at-a-glance summary of our task statuses. On your Dashboard sheet, create labels for "Completed Tasks," "In Progress," etc. Now, let’s ask ChatGPT for the formulas.
Your prompt to ChatGPT:"I have a Google Sheet with a tab named 'Project Tracker'. Task statuses (like 'Completed', 'In Progress', 'Not Started') are in column F. I need a formula to count how many tasks are 'Completed'."
ChatGPT's likely response:"Of course! You can use the COUNTIF function. Place this formula in the cell next to your 'Completed Tasks' label:"
You can repeat this process for your other statuses, simply by swapping out "Completed" in the formula for "In Progress," "Not Started," or "Blocked." This immediately gives you a high-level overview of project health.
Example 2: Calculating Overall Project Progress
Next, let's find out the average completion of all tasks combined. This is a powerful metric to see how far the entire project has come.
Your prompt to ChatGPT:"In my 'Project Tracker' sheet, I have a column G containing '% Complete' for each task. Write me a Google Sheets formula to calculate the average percentage of all tasks."
ChatGPT's likely response:"You can use the AVERAGE function for that. This formula will give you the average completion percentage:"
Place this formula in your dashboard and make sure to format the cell as a percentage (Format >, Number >, Percent).
Example 3: Counting Overdue Tasks
Knowing what's overdue is crucial for getting projects back on track. This formula is a bit more complex, making it a perfect job for ChatGPT.
Your prompt to ChatGPT:"I need a Google Sheets formula that checks my 'Project Tracker' tab. It should count the number of tasks where the 'Due Date' in column E has already passed AND the 'Status' in column F is NOT 'Completed'."
ChatGPT's likely response:"This requires a COUNTIFS function to check multiple criteria. Here is the formula:"
This single formula instantly surfaces one of the most important project metrics without you having to manually sift through tasks.
Step 3: Design Your Dashboard and Visualize the Data
With our key metrics calculated, it's time to make your dashboard easy to read and visually appealing. Here’s how to structure it.
Create a KPI Summary Section
At the top of your Dashboard tab, arrange the metrics you just created. Lay them out clearly with bold labels so anyone can understand the project's status in five seconds.
Total Tasks
Completed
In Progress
Overdue
Overall Progress %
Build a Task Status Chart
Humans are visual creatures. A chart can communicate the task breakdown much faster than raw numbers. A donut chart is perfect for this.
On your Dashboard sheet, create a small summary table using the status counts you already generated (e.g., a "Status" column with your four statuses and a "Count" column with the formulas).
Select this data range (your status labels and their counts).
Go to Insert >, Chart. Google Sheets will likely recommend a pie chart. In the Chart editor on the right, under "Chart type," you can select "Donut chart" for a more modern look.
Add a Progress Bar for Overall Completion
While a percentage figure is good, a progress bar is even better for quick comprehension. We can use the awesome SPARKLINE function to create one inside a single cell. Let's ask ChatGPT how.
Your prompt to ChatGPT:"How do I create an in-cell progress bar in Google Sheets? The percentage value I want to visualize is in cell B5 on my 'Dashboard' sheet."
ChatGPT's likely response:"You can use the SPARKLINE function. This formula will create a progress bar based on the value in B5:"
Pop this formula next to your "Overall Progress %" KPI. The 1 in the formula represents 100%.
Show Workload by Team Member with a Pivot Table
Want to see if work is distributed fairly? A pivot table is the perfect tool.
Go to Insert >, Pivot table.
For the "Data range," select all the columns in your 'Project Tracker' sheet (e.g., 'Project Tracker'!A:H). Make sure you choose "New sheet" to start.
In the Pivot table editor
For "Rows," add the 'Assigned To' field.
For "Values," add the 'Task Name' field and make sure it is summarized by "COUNTA".
For "Columns," add the 'Status' field.
This instantly creates a table showing you not just how many total tasks each person has but a breakdown by status. You can copy and paste this simple summary into your main dashboard for a complete view of team workload.
Step 4: Add Some Professional Polish
To make your dashboard even more useful, let's add some automated visual cues in your main 'Project Tracker' sheet.
Use Conditional Formatting
Conditional formatting changes a cell's color based on its content, making it easy to spot important items.
Highlight Completed Tasks: Select your entire data range (A2:H100, for example). Go to Format >, Conditional formatting. Set the rule to "Custom formula is" and enter
=$F2="Completed". Set the formatting to a light green background and turn the text a darker green.Flag Overdue Tasks: Add another rule for the same data. Use the custom formula
=AND($E2<,TODAY(), $F2<,>,"Completed")and set the formatting to a light red background fill.
Now, as statuses change and deadlines pass, your sheet will automatically update, drawing your team's attention to the right places.
Final Thoughts
You've successfully built a fully functional project management dashboard in Google Sheets. By starting with a clean data structure and leveraging ChatGPT to produce formulas, you created a powerful, automated tool that can replace expensive software for many teams, all without needing to be a spreadsheet expert yourself.
While DIY dashboards in Sheets are amazing, things can get complicated when you need to pull in data from other tools like your CRM or ad platforms. To solve this, we built Graphed It allows you to connect all your data sources in one click and then build real-time dashboards just by describing what you want to see. Instead of handcrafting pivot tables and formulas, you can simply ask, "show me this quarter's sales pipeline from HubSpot" or "compare Facebook Ads spend vs. Shopify revenue," and the dashboard gets built for you in seconds.