How to Create a Project Dashboard in Excel with ChatGPT

Building a project dashboard in Excel can feel like a daunting task, but ChatGPT can turn hours of formula-writing and head-scratching into a simple conversation. This guide will walk you through exactly how to use AI to generate the formulas, logic, and visualizations needed to create a clear and effective project dashboard from scratch.

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

First, Structure Your Project Data Properly

Before you can build a dashboard, you need a solid foundation of well-organized data. If your data is messy, your dashboard will be too. It’s the simple principle of "garbage in, garbage out."

Create a simple table on a new sheet in Excel. Let's call this sheet "ProjectData." Your table should have clear, distinct columns for each piece of information about your tasks. A good starting point looks like this:

• Task Name: A brief description of the task.
• Project Phase: The phase the task belongs to (e.g., Planning, Design, Development, Launch).
• Assigned To: The name of the team member responsible.
• Start Date: When 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, Completed, Overdue).

Here’s an example of how your data might look. For best results, format this range as a table in Excel by selecting your data and pressing Ctrl + T (or Cmd + T on Mac). This makes formulas easier to manage later on.

With clean source data like this, you’re ready to let ChatGPT do the heavy lifting.

Using ChatGPT to Generate Your Dashboard Components

Now for the fun part. Create a new sheet named "Dashboard." This is where you’ll build the visual elements. We’ll ask ChatGPT for the exact formulas needed to pull summary data from your "ProjectData" sheet.

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

1. Creating Key Metric Summaries

First, let’s create a high-level overview of the project’s health. We want to see the total number of tasks, how many are completed, and how many are currently in progress.

Open ChatGPT and give it a prompt like this:

I have an Excel sheet with project tasks. The data is in a table named 'Table1' on a sheet called 'ProjectData'. The task statuses are in a column named Status.

Can you give me the Excel formulas for the following calculations?
1. Total number of tasks
2. Number of tasks with the status 'Completed'
3. Number of tasks with the status 'In Progress'

ChatGPT will likely return something like this:

• For Total Tasks:
• For Completed Tasks:
• For In Progress Tasks:

Simply copy these formulas and paste them into separate cells on your "Dashboard" sheet. Add some labels next to them, and just like that, you have your core metrics.

2. Building a Task Status Donut Chart

Visuals make data easier to understand at a glance. A donut or pie chart is perfect for showing the distribution of task statuses.

First, we need a small summary table on your Dashboard sheet to power the chart. Ask ChatGPT for help:

Using the same Excel table, I want to create a summary for a chart. Can you give me the formulas to count the number of tasks for 'Not Started', 'In Progress', and 'Completed'?

On your "Dashboard" sheet, create a small two-column table like this and paste in the formulas ChatGPT provides:

Once you have this summary table:

1. Select the data in your summary table (including the headers).
2. Go to the Insert tab in Excel.
3. Click on the Pie Chart icon and select Doughnut.

Excel will instantly generate a chart. You can customize the colors and title to fit your dashboard's style.

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

3. Creating a "Tasks by Team Member" Bar Chart

Next, let’s see who’s responsible for what. A bar chart is great for comparing workloads across the team. Just like before, we start by asking ChatGPT to generate the formulas for a summary table.

Your prompt:

Now I need to count how many tasks are assigned to each person. Team members are listed in the Assigned To column of 'Table1'. Can you give me the formula to count tasks for 'Sarah', 'David', 'Mike', and 'Jen'?

Create another summary table on your "Dashboard" sheet with the names of your team members and paste in the formulas provided, which should look like this:

To create the bar chart:

1. Select the data in this new table.
2. Go to the Insert tab.
3. Click the Bar Chart icon and select a 2-D Clustered Bar chart.

Now you have a clear visual breakdown of the task distribution among your team members.

4. Automatically Highlighting Overdue Tasks

A great dashboard doesn't just show you data, it draws your attention to what’s important. Let’s use conditional formatting to automatically highlight tasks that are past their due date and not yet completed.

Writing conditional formatting formulas can be tricky, so it’s a perfect job for ChatGPT.

Use a specific prompt like this one:

In my 'Table1' on the 'ProjectData' sheet, I want to highlight overdue tasks. The due date is in the Due Date column and the status is in the Status column.

Create an Excel conditional formatting formula that will highlight the entire row if the task's status is NOT 'Completed' and its due date is in the past.

ChatGPT should provide a formula using the AND and TODAY functions:

=AND([@Status]<>"Completed", [@Due Date]<TODAY())

To apply this rule:

1. Go to your "ProjectData" sheet and select all the data in your table (but not the headers).
2. On the Home tab, click Conditional Formatting > New Rule.
3. Select "Use a formula to determine which cells to format."
4. Paste the formula ChatGPT gave you into the formula box.
5. Click the Format... button and choose a fill color (like a light red) to highlight the at-risk tasks.
6. Click OK. Now, any task that becomes overdue will automatically be highlighted for you.

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

Putting It All Together on Your Dashboard Sheet

Now that you have all the individual components, it's time to assemble them into a professional-looking dashboard.

On your "Dashboard" sheet:

• Arrange your elements: Cut and paste your charts and key metrics into a clean layout. Place the high-level metrics at the top like "cards."
• Add a title: Give your dashboard a clear title like "Project Phoenix - Live Dashboard."
• Remove gridlines: Go to the View tab and uncheck the "Gridlines" box. This quick change gives your dashboard a much cleaner, report-like appearance.

The beauty of this setup is that it's dynamic. As you update the task statuses on your "ProjectData" sheet, all the formulas and charts on your dashboard will update automatically. No more manual weekly reports!

Final Thoughts

By leveraging ChatGPT as your expert assistant, you can generate complex formulas and build a functional Excel dashboard without spending hours on tutorials. It empowers you to focus on the project's insights rather than getting tangled up in spreadsheet mechanics.

While this method makes internal reporting dramatically easier, we built Graphed because we believe there's an even better way to get insights. Instead of pulling data into spreadsheets just to build charts, our tool lets you connect directly to your data sources - like Google Analytics, HubSpot, or Shopify - and create live, interactive dashboards just by describing what you want to see in plain English. No more exporting CSVs or fighting with formulas.