Graphed LogoGraphed
Login

How to Create a Project Dashboard in Google Sheets with ChatGPT

Cody Schneider•

Tired of tracking project progress across scattered emails, Slack threads, and forgotten sticky notes? Creating a centralized dashboard is the answer, but the thought of wrestling with complex spreadsheet formulas can be intimidating. Luckily, you now have an AI assistant who can do the hard work for you. This tutorial will walk you through building a powerful, visual project management dashboard in Google Sheets, using ChatGPT to generate all the necessary formulas along the way.

You don't need to be a spreadsheet expert. You just need to know what you want to track and how to ask for it.

Why a Google Sheets Dashboard (and Where ChatGPT Fits In)

Google Sheets is a fantastic, free, and collaborative tool for project management. It's accessible from anywhere and easy to share with your team. The only real barrier has been the learning curve for functions and formulas needed to bring your data to life. That barrier is now gone.

Think of ChatGPT as your personal data analyst intern. Its job is to listen to your plain-English requests and translate them into a perfectly formatted Google Sheets formula. Instead of spending hours searching for tutorials on VLOOKUP or QUERY, you can simply describe what you want, and ChatGPT will write the code. This lets you focus on the most important part: deciding what metrics matter for your project.

Step 1: Laying the Foundation - Structuring Your Project Data

Before you can build a dashboard, you need a clean, organized source of data. A good dashboard is built on a foundation of well-structured information. The rule of "garbage in, garbage out" absolutely applies here.

Create a new Google Sheet. Rename the first tab to something like "ProjectData" or "Tasks". This sheet will be your single source of truth for all project-related tasks. Set up columns for all the key information you need to track. Here's a simple but effective structure for a typical marketing campaign project:

  • Task ID: A unique identifier (e.g., MKT-001, MKT-002).

  • Task Name: A clear, concise description of the task (e.g., "Draft Blog Post on Q2 Features").

  • Owner: The team member responsible for the task.

  • Category: The type of work involved (e.g., Content, Design, Social Media, SEO).

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

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

  • Due Date: The deadline for the task.

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

Pro-Tip: Use Data Validation

To keep your data consistent and avoid typos, use Google Sheets' Data Validation feature. For the 'Status' and 'Owner' columns, you can create dropdown menus. This ensures everyone uses the exact same terms (e.g., "In Progress" instead of "In-progress" or "ongoing"), which is critical for your formulas to work correctly.

To do this, select the entire 'Status' column, go to Data > Data validation, and for 'Criteria', choose 'List of items'. Then, enter your statuses separated by commas: Not Started,In Progress,Completed,Blocked.

Step 2: Creating the Dashboard Tab

With your data structure in place, it's time to create the space where your dashboard will live. Create a new tab in your Google Sheet and name it "Dashboard". This is a clean canvas where we will pull in summary numbers, lists, and charts based on the information in your "ProjectData" tab. Keeping the raw data separate from the dashboard display is a best practice that keeps everything tidy and easy to manage.

Step 3: Prompting ChatGPT for Your Key Dashboard Metrics

This is where the magic happens. We'll identify a few key metrics for our project dashboard and then craft simple, clear prompts for ChatGPT to generate the formulas. For each example, open a new chat with ChatGPT and describe what you need.

A good prompt should always include a few key things:

  • The context: "I am working in Google Sheets."

  • The data location: Mention the tab name ("ProjectData") and the specific columns ('A', 'B', 'C', etc.).

  • The goal: Clearly state what you want to calculate or display.

Metric 1: Project Progress At-a-Glance (Donut Chart)

Your first and most important visual should be a high-level overview of project completion. A pie or donut chart showing the breakdown of tasks by status is perfect for this.

First, we need a small summary table on our 'Dashboard' tab. Let's ask ChatGPT to create the formulas for that table.

Your Prompt for ChatGPT:

"I have a Google Sheet with a tab named 'ProjectData'. In column E of that tab, I track the 'Status' of each task, with possible values being 'Not Started', 'In Progress', and 'Completed'. On my 'Dashboard' tab, in cell A2 I have 'Not Started', in A3 'In Progress', and in A4 'Completed'. Please give me a formula for cell B2 that counts how many times 'Not Started' appears in the 'ProjectData' tab, column E. I will then drag this formula down for the other statuses."

ChatGPT's Likely Response: The formula for cell B2 is: =COUNTIF(ProjectData!E:E, A2) Place this formula in cell B2 on your Dashboard tab and drag the small blue square in the bottom-right corner of the cell down to B4. It will automatically update to count for "In Progress" and "Completed."

Turn it into a Chart: Highlight your small summary table (A1:B4). Go to Insert > Chart. Google Sheets will likely suggest a Pie Chart by default. In the chart editor on the right, you can customize it and change the type to a Donut Chart for a more modern look.

Metric 2: Spotting Overdue Tasks (Automated List)

You need to quickly see which tasks are falling behind. Let's create a dynamic list that automatically pulls in all tasks that are past their due date but are not yet marked as "Completed."

Your Prompt for ChatGPT:

"I need a Google Sheets formula that creates a list of overdue tasks. My data is on the 'ProjectData' tab. 'Task Name' is in column B, 'Owner' is in column C, 'Status' is in column E, and the 'Due Date' is in column G. On my 'Dashboard' tab, I want to display the 'Task Name', 'Owner', and 'Due Date' for all tasks where the 'Status' is NOT 'Completed' and the 'Due Date' is before today."

ChatGPT's Likely Response: This is a more complex request, so ChatGPT will likely use the powerful QUERY or FILTER function. =QUERY(ProjectData!B:G, "SELECT B, C, G WHERE E <> 'Completed' AND G < TODAY()", 1)

Paste this single formula into a cell on your Dashboard (e.g., D2), and it will automatically generate a clean, three-column list of all overdue tasks. This list will update in real-time as you change statuses and due dates on the "ProjectData" tab.

Metric 3: Workload Distribution by Team Member

Wondering if task distribution is fair or if one person is a bottleneck? A simple bar chart can answer this. Similar to our first metric, we'll start with a summary table.

Your Prompt for ChatGPT:

"I'm using the same 'ProjectData' tab. Column C has the name of the task 'Owner'. How do I create a summary table that lists each unique owner and counts how many tasks they are assigned? I want the owner's name in one column and the count of their tasks in the next."

ChatGPT's Likely Response: It will likely suggest a two-formula approach using UNIQUE and COUNTIF. In one cell (e.g., A7), place this formula to get a list of all team members: =UNIQUE(ProjectData!C2:C) Then, in the cell next to it (B7), use this formula and drag it down: =COUNTIF(ProjectData!C:C, A7)

Turn it into a Chart: Highlight this new summary table, go to Insert > Chart, and choose a Bar Chart or Column Chart. Now you have an instant visual of everyone's workload.

Tips for Better ChatGPT Prompts and Dashing Dashboards

  • Be Hyper-Specific: The more detail you provide about tab names, column letters, and your data ranges, the better the formula will be.

  • Iterate and Refine: If the first formula doesn’t work perfectly, don't give up! Copy the formula, go back to ChatGPT, and explain the error. For example: "This formula gave me a #REF! error. I think it's because..." ChatGPT is excellent at debugging its own code.

  • Describe Your Goal, Not the Function: You don't need to know the name of the function you need. Instead of asking for a VLOOKUP formula, describe what you're trying to achieve: "I want to find the 'Owner' from the 'ProjectData' tab that corresponds to the 'Task ID' I typed in cell A1."

  • Keep it Clean: Good dashboard design is about clarity. Use color-coding thoughtfully, provide clear labels for every chart and metric, and use whitespace to avoid a cluttered look.

Final Thoughts

By pairing the simple power of Google Sheets with the intelligence of ChatGPT, you can move from a simple to-do list to an automated, professional-looking dashboard in under an hour. This frees you from the tedious work of writing formulas and allows you to build a project command center that provides real-time clarity and helps your team stay on track.

While building dashboards in Google Sheets is powerful, we know the process still requires manual setup and assumes all your data lives neatly in one spreadsheet. We built Graphed because we wanted to skip the manual formula-building entirely. Instead of prompting for individual formulas to build charts one by one, you just connect your tools (like Google Ads, Shopify, or Salesforce) and ask for what you want, like "create a real-time sales performance report." We'll instantly generate a complete, interactive dashboard for you, keeping everything updated automatically and saving you from ever having to wrangle a spreadsheet again.