How to Create a Project Portfolio Dashboard in Google Sheets with ChatGPT

Managing a single project is hard enough, juggling an entire portfolio is a masterclass in organized chaos. If you're tired of switching between different tools and drowning in status updates, a clean project portfolio dashboard can be a lifesaver. This article will show you how to build a dynamic, filterable dashboard right in Google Sheets, using ChatGPT to write the formulas and do the heavy lifting for you.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Why Use Google Sheets for Project Portfolio Management?

Before sophisticated project management software, there was the humble spreadsheet. While tools like Asana, Monday, and Trello are powerful, sometimes they’re overkill. Using Google Sheets for your project portfolio dashboard has a few distinct advantages:

• It’s Free and Accessible: Every team member with a Google account can access, view, and collaborate on the sheet from anywhere, at any time. There are no user licenses or subscription fees to worry about.
• Complete Customization: You aren't boxed into a software's predefined structure. You can track exactly what matters to your organization, from budget variance and team allocation to specific client milestones. If you can think of a metric, you can add a column for it.
• Familiarity: Most team members are already comfortable working in a spreadsheet environment. This drastically reduces the learning curve and time needed for team adoption. You won’t have to run training sessions to get everyone up to speed.

The goal isn't to replicate a complex PM tool but to create a single source of truth - a high-level command center - that gives anyone a clear snapshot of your project landscape in seconds.

What to Track: Defining Your Dashboard's Core Metrics

Before you write a single formula, you need to decide what information is most important. A cluttered dashboard is an ignored dashboard. Start with the essentials that give you the biggest overview of portfolio health. We'll build our setup around two main tabs: one for raw data entry ('Projects Data') and another for the visual dashboard ('Dashboard').

Your data tab should look something like a simple database - just rows and rows of project information, clean and easy to update.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step-by-Step: Setting Up Your Google Sheet

With an understanding of your key metrics, setting up the Google Sheet is straightforward. We'll be creating a "backend" for your data and a "frontend" for your dashboard.

Step 1: Create a 'Projects Data' Tab

This is where all your project information lives. Open a new Google Sheet and rename the first tab to 'Projects Data'.

1. Create the columns you defined in the last section: Project Name, Project Lead, Status, Progress (%), Start Date, Due Date, Budget ($), and Actual Spend ($).
2. To make status updates consistent, create a dropdown menu for the 'Status' column. Select the entire column (minus the header), then navigate to Data > Data validation.
3. In the criteria field, select 'List of items' and enter your statuses separated by commas: On Track,At Risk,Off Track. Click 'Save'.
4. Populate this sheet with 5-10 rows of sample project data. This will make it easier to test your formulas as you build.

Step 2: Create a 'Dashboard' Tab

Click the "+" icon in the bottom-left corner to add a new sheet. Rename this tab to 'Dashboard'. This is where your summary stats and visualizations will live. For now, leave it blank. All the magic will happen by pulling data from the 'Projects Data' tab into this one.

Using ChatGPT to Write Formulas and Build Your Dashboard

Here’s where you’ll see the process accelerate. Instead of trying to remember complex syntax or search for a specific function, you can simply ask ChatGPT to generate the formulas you need in plain English.

Make sure to be descriptive in your prompts. Mention your tab names and column letters for the best results.

Crafting a High-Level Summary

The first part of any good dashboard is a set of "at-a-glance" numbers. In your 'Dashboard' tab, create headers for: Total Projects, Projects On Track, Projects At Risk, and Projects Off Track.

Now, let's use ChatGPT. Open a new chat and use a prompt like this:

"I am creating a project dashboard in Google Sheets. I have a tab named 'Projects Data' where project information is stored. I need formulas for my 'Dashboard' tab.

ChatGPT will provide the formulas you need:

• For Total Projects: =COUNTA('Projects Data'!A2:A)
• For Projects On Track: =COUNTIF('Projects Data'!C2:C, "On Track")

Simply repeat this for "At Risk" and "Off Track" to round out your health snapshot. You now have a live summary that updates automatically whenever you add or change project data.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Building a Financial Overview

Next, let's do the same for our financials. Create headers in your 'Dashboard' for Total Budget, Total Spend, and Remaining Budget.

Here's your next ChatGPT prompt:

"In the same sheet, 'Projects Data'!G:G is my 'Budget ($)' column and 'Projects Data'!H:H is my 'Actual Spend ($)' column. Please provide formulas to:

ChatGPT will give you:

• For Total Budget: =SUM('Projects Data'!G2:G)
• For Total Actual Spend: =SUM('Projects Data'!H2:H)
• For Remaining Budget, you can nest these formulas: =SUM('Projects Data'!G2:G) - SUM('Projects Data'!H2:H)

Adding a Dynamic, Filterable Project List

This is where your dashboard goes from static to interactive. We want to create a dropdown menu of all your project leads and then, based on the lead you select, display only their projects.

Step 1: Create the Dropdown

First, find a spot on your dashboard for the dropdown, maybe in cell B2. Then, ask ChatGPT for help:

"How do I create a drop-down list in cell B2 of my 'Dashboard' tab that shows all the unique names from the 'Project Lead' column, which is column B on my 'Projects Data' sheet?"

ChatGPT will likely walk you through the Data validation menu again. Select cell B2 in your 'Dashboard', go to Data > Data validation, and for the criteria, choose 'List from a range'. For the range, enter: ='Projects Data'!B2:B. You now have a dropdown menu of your project leads.

Step 2: Create the Dynamic Filter

This is the most powerful part. We'll use the FILTER function to create a table that updates based on the dropdown selection. Pick the area below your summary stats to add the project table. Create headers for Project Name, Status, and Progress.

Now, let's ask ChatGPT for the magic formula:

"I have a dropdown in 'Dashboard'!B2 with project lead names. The data is on the 'Projects Data' sheet. I want to display a list of all projects assigned to the person selected in the dropdown. Specifically, when a name is picked in B2, I need to show their Project Name (from 'Projects Data'!A:A), Status (from 'Projects Data'!C:C), and Progress (from 'Projects Data'!D:D). Please provide me with a FILTER formula."

ChatGPT will produce a formula like this. Paste it into the cell right under your 'Project Name' header (e.g., A6):

=FILTER('Projects Data'!A2:D, 'Projects Data'!B2:B = B2)

Just like that, your entire table will populate with the corresponding projects. When you select a new name from the dropdown, the list updates instantly.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Best Practices for Prompting ChatGPT for Formulas

The quality of your ChatGPT output depends on the quality of your input. Here are a few tips to get the best formulas every time:

• Be Hyper-Specific: Always include the tab names ('Projects Data' or 'Dashboard') and exact column letters or ranges (A2:A, C:C). The more specific you are, the less guessing the AI has to do.
• State Your Goal Clearly: Instead of saying "give me a counting formula," explain what you want to count. For example, "Count all rows where the value in column C is 'At Risk' and the value in column G is greater than $5000."
• Ask for an Explanation: If you get a formula you don't understand, ask ChatGPT to explain it. Prompting "explain what each part of that formula does" is a fantastic way to learn more about how Google Sheets works.
• Iterate and Refine: If the first formula isn't quite right, don't give up. Tell ChatGPT what was wrong. For example: "That formula worked, but it returned an error when the filter found no matching projects. Can you modify it to show a message that says 'No Projects Found' instead of an error?" (Hint: It will likely suggest wrapping your formula in an IFERROR function).

Final Thoughts

Building a dashboard like this gives you a clean, simple, and powerful way to monitor your project landscape without the cost or complexity of specialized software. By using ChatGPT, you can bypass the friction of searching for syntax and instead focus on what you want the dashboard to do, making the entire process faster and more accessible.

As powerful as custom-built Google Sheets dashboards are for internal projects, syncing and analyzing data from official platforms like Google Analytics, Shopify, QuickBooks, or Salesforce can get complicated and fragile. At Graphed you’ll see a link that you can follow.