How to Create a Project Management Dashboard in Excel with ChatGPT

Building a project management dashboard in Excel no longer has to be a weekend-long battle with nested formulas and formatting headaches. By pairing the universal accessibility of Excel with the power of an AI assistant like ChatGPT, you can create a clear, dynamic dashboard in a fraction of the time. This guide will walk you through using ChatGPT to generate realistic project data, write complex formulas, and structure essential visuals for tracking your project's success.

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, Why Even Use Excel for Project Management?

While dedicated project management tools like Asana, Jira, or Trello have their place, Excel remains a popular choice for a few good reasons:

• Accessibility: Nearly everyone has access to Excel and a basic understanding of how it works.
• Flexibility: You can customize your dashboard to show the exact metrics you care about, without being locked into a specific software's layout.
• Integration: Data can often be easily exported from other systems and dropped directly into your Excel workbook.

The main drawbacks - being manual, time-consuming, and prone to human error - are precisely where using ChatGPT can make a huge difference. Think of it as your new data-savvy assistant who can handle the tedious parts for you.

Laying the Groundwork: Planning Your Dashboard

Before you write a single prompt, you need a clear idea of what you want to track. A great dashboard tells a story at a glance. Focus on the key performance indicators (KPIs) that matter most for your project.

Define Your Project Metrics

Every effective dashboard is built on a foundation of clean, well-structured data. Start by creating a dedicated worksheet in Excel named something like "ProjectData." This will be the "backend" database that powers your entire dashboard.

Here are some of the most common project management KPIs to include:

• Task Status Breakdown: A count of tasks that are 'Not Started', 'In Progress', and 'Completed'.
• Overall Project Completion: The percentage of total tasks that are marked as 'Completed'.
• Budget vs. Actuals: Tracking how your spending compares to your budget for individual tasks or the entire project.
• Resource Allocation: Monitoring how many tasks are assigned to each team member to ensure workloads are balanced.
• Upcoming Deadlines: A view of tasks due in the coming week or month.

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

Structure Your "Data" Worksheet

In your "ProjectData" sheet, set up a simple table with headers for each piece of information you plan to track. A good starting point includes columns like:

• Task ID
• Task Name
• Description
• Assigned To
• Start Date
• Due Date
• Status (e.g., Not Started, In Progress, Completed, Stuck)
• Estimated Hours
• Actual Hours

Once you have your headers, select them and press Ctrl+T (or Cmd+T on Mac) to format the range as an official Excel Table. Give your table a descriptive name in the "Table Design" tab, like ProjectDataTable. Using an official Table is crucial because it makes your data ranges dynamic, which means your charts and formulas will automatically update when you add new tasks.

Using ChatGPT to Generate Realistic Project Task Data

A blank spreadsheet is daunting. Instead of manually inventing dozens of tasks, you can ask ChatGPT to generate realistic sample data for you. This allows you to build and test your dashboard visuals without needing finalized project plans.

Open ChatGPT and use a detailed prompt like this:

Please generate sample data for a project management dashboard in a CSV format. The project is a 'New Website Launch' and should last 3 months. Create 30 tasks with the following columns: TaskID, TaskName, AssignedTo, StartDate, DueDate, Status Make the 'AssignedTo' names generic (e.g., Alex, Jordan, Sam, Riley). Distribute the 'Status' column realistically between 'Not Started', 'In Progress', and 'Completed'.

ChatGPT will produce a clean, formatted block of text. Simply copy this directly from the chat window and paste it into your ProjectDataTable in Excel. Excel should automatically parse the comma-separated text into the correct columns.

Building Dynamic Visuals with ChatGPT and PivotTables

Now for the fun part: visualizing the data. The fastest and most powerful method for creating interactive dashboard components is using PivotTables and PivotCharts. They do the heavy lifting of summarizing your data, and ChatGPT can guide you every step of the way.

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

1. Create Your Summary Cards

Summary cards provide a high-level overview of the project's health. We can generate these stats using powerful formulas that instantly summarize your data.

Give ChatGPT a prompt like this:

I have an Excel table named 'ProjectDataTable' with a 'Status' column. Give me three separate formulas to:

ChatGPT will provide the exact formulas you need:

=COUNTA(ProjectDataTable[TaskName])

=COUNTIF(ProjectDataTable[Status],"Completed")

=COUNTIF(ProjectDataTable[Status],"In Progress")

On your dashboard sheet, create simple text boxes or use cells to display these metrics. You can add one more formula to calculate the percentage complete: =[Completed Tasks Cell] / [Total Tasks Cell], then format that cell as a percentage.

2. Visualize Task Status with a PivotChart

A donut chart is perfect for showing the proportional breakdown of task statuses. Instead of manually creating summary data, let's use a PivotChart.

Ask ChatGPT for instructions:

How do I create a Donut PivotChart in Excel to see the count of tasks for each status? My data is in a table called 'ProjectDataTable'.

ChatGPT will give you a list of steps, which you can follow:

1. Click anywhere inside your ProjectDataTable.
2. Go to the Insert tab on the Ribbon and click PivotChart.
3. Choose "PivotChart" and click "OK". This will create a new worksheet with your PivotChart and its corresponding PivotTable.
4. In the "PivotChart Fields" pane on the right, drag the Status field into the Legend (Series) box.
5. Drag the TaskID field into the Values box. It should default to "Count of TaskID," which is exactly what we need.

You can now cut and paste your newly created Donut Chart onto your main dashboard sheet and resize it. Customize its colors and remove clutter like the legend if the chart segments are clearly labeled.

3. Add Slicers for Interactive Filtering

Slicers are interactive, user-friendly filters that make your dashboard feel like a professional application. They allow you or your stakeholders to filter the dashboard data with the click of a button.

Creating one is straightforward:

1. Click on your Task Status PivotChart.
2. Go to the PivotChart Analyze tab.
3. Click Insert Slicer.
4. In the pop-up window, check the box for AssignedTo. Click OK.

You now have a slicer that will filter your donut chart. When you click a name, the chart will update to show only the tasks assigned to that person. To make this an even better experience, you can create multiple PivotCharts (e.g., one for budget, one for workload per person) and connect the same slicer to all of them. Simply right-click the slicer, choose Report Connections, and check the boxes for every PivotTable you want it to control.

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

4. Track Progress with a Gantt Chart

A Gantt chart is a staple of project management, visualizing the project timeline. Excel doesn’t have a built-in Gantt chart type, but you can create one using a stacked bar chart. This is a perfect task to offload to your AI assistant.

Use a specific prompt in ChatGPT:

I have an Excel table with 'TaskName', 'StartDate', and 'EndDate'. Give me the step-by-step instructions to create a Gantt chart in Excel using a stacked bar chart.

ChatGPT will guide you through the process:

1. Create a "Duration" Column: First, add a new column to your ProjectDataTable called "Duration." The formula will be = [EndDate] - [StartDate].
2. Insert Chart: Select your "TaskName" and "StartDate" columns. Go to Insert > Chart > Stacked Bar Chart.
3. Add Duration Data: Right-click the chart, choose "Select Data," and add a new series for your new "Duration" column.
4. Format the 'StartDate' Series: Click on the bars representing the "Start Date" (they'll be the first segment of each bar). Right-click, go to Format Data Series, and in the "Fill & Line" options, choose No Fill. This makes the first part of the bar invisible, giving the illusion that you have floating bars that start on the correct date.
5. Reverse Task Order: Click on the vertical axis (your task list). Right-click, choose Format Axis, and check the box that says "Categories in reverse order." This makes your Gantt chart display chronologically from top to bottom.

Final Thoughts

By following these steps, you’ve used ChatGPT as a true co-pilot to take a blank workbook and turn it into a functional, interactive project management dashboard. You’ve leveraged AI to generate data, write formulas, and guide you through Excel's more complex charting features, saving yourself hours of tedious work and configuration.

While this method dramatically speeds up the creation process, the completed Excel file is still a static report. It requires you to manually refresh data and share the file repeatedly to keep stakeholders updated. If you find yourself wanting to move from static reports to truly live dashboards, we've built Graphed for exactly that purpose. You can ask for a dashboard in plain English, and instead of just instructions, our platform builds it for you in seconds with a real-time connection to all your data sources, with no need to wrestle with formulas, formatting, or PivotTables ever again.