How to Create a Project Portfolio Dashboard in Excel with ChatGPT

Building a comprehensive project portfolio dashboard might sound like a task reserved for data analysts, but you can create a powerful and dynamic one directly in Excel. By pairing a simple spreadsheet with ChatGPT, you can streamline the process, from generating complex formulas to getting clear guidance on chart creation. This article walks you through a step-by-step process for building a project portfolio dashboard in Excel with a little help from your AI assistant.

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 Build a Project Portfolio Dashboard?

A project portfolio dashboard gives you a high-level, visual overview of all your projects in one place. Instead of digging through individual project plans or asking for updates, you can check the dashboard to quickly understand the overall health of your initiatives. It serves multiple purposes:

• Visibility: It consolidates key metrics from all projects, making it easy for stakeholders and leadership to see progress at a glance.
• Decision Making: It helps identify which projects are on track, at risk, or delayed, allowing for better resource allocation and proactive problem-solving.
• Accountability: It tracks performance against key performance indicators (KPIs) like budget, timeline, and completion status.

In short, it turns a chaotic mess of project data into a clean, actionable command center.

Step 1: Structure Your Project Data in Excel

Before you can build anything, you need a solid foundation. The most critical step is organizing your project data cleanly in a single worksheet. Good dashboards are built on good data. For a project portfolio, your raw data should be set up in a simple, flat table format. Each row represents a single project, and each column represents a piece of information about that project.

Create a new sheet in Excel named "ProjectData" and set up columns with the following headers:

• ProjectID: A unique identifier for each project.
• ProjectName: The name of the project.
• ProjectManager: The primary person responsible.
• Department: The department owning the project (e.g., Marketing, Engineering).
• StartDate: The project's official start date.
• EndDate: The project's official end date.
• ProjectStatus: The current status (e.g., On Track, At Risk, Delayed, Completed).
• PercentComplete: The completion percentage (formatted as %).
• PlannedBudget: The originally allocated budget.
• ActualCost: The total cost spent to date.

Pro Tip: Format as an Excel Table

Once you have your headers and a few rows of sample data, convert the range into an official Excel Table. Click anywhere inside your data and press Ctrl + T (or Command + T on Mac). In the dialog box, make sure "My table has headers" is checked and click OK.

Formatting your data as a table is crucial because it makes your data dynamic. When you add a new project (a new row), all your formulas and charts connected to this table will automatically update. It also lets you use structured references (like TableName[ColumnName]), which are much easier to read than traditional cell references.

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 2: Use ChatGPT to Generate Excel Formulas

Now that your data is structured, you can add calculated columns to derive new insights. This is where ChatGPT becomes an incredibly powerful assistant. Instead of trying to remember complex formulas, you can simply describe what you want in plain English.

Let's add three new columns to our "ProjectData" table: CostVariance, DaysRemaining, and BudgetStatus.

Calculating Cost Variance

Cost variance shows whether you are over or under your planned budget. Open ChatGPT and use a prompt like this:

I have an Excel table named "ProjectDataTable". It has two columns: "PlannedBudget" and "ActualCost". Write me an Excel formula to calculate the cost variance by subtracting the actual cost from the planned budget.

ChatGPT will likely give you this formula:

`=[@[PlannedBudget]]-[@[ActualCost]]`

Copy this formula into the first cell of your "CostVariance" column. Because you're using an Excel Table, it will automatically fill down for all rows.

Calculating Days Remaining

This metric helps you see which projects are approaching their deadlines. Here's a prompt for ChatGPT:

In my Excel table, I have an "EndDate" column. I need an Excel formula to calculate the number of days left from today until the end date. If the end date has already passed, the formula should return 0.

The AI will give you an elegant solution:

`=MAX(0, [@[EndDate]]-TODAY())`

This formula subtracts today's date from the end date and uses the MAX function to ensure you don't get negative numbers for projects that are already past their due date.

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

Determining Budget Status

A simple text-based status for budget can be helpful for quick visual scans. Use this prompt:

I need an Excel IF formula. My table has "ActualCost" and "PlannedBudget" columns. If "ActualCost" is greater than "PlannedBudget", the cell should say "Over Budget". If it's less, it should say "Under Budget". If they are equal, it should say "On Budget".

ChatGPT will generate a nested IF statement:

`=IF([@[ActualCost]]>[@[PlannedBudget]], "Over Budget", IF([@[ActualCost]]<[@[PlannedBudget]], "Under Budget", "On Budget"))`

Within minutes and without any deep formula knowledge, you've added three meaningful calculated columns to your dataset.

Step 3: Build Your Dashboard Visuals

With your data prepared, it's time to build the dashboard itself. Create a new worksheet and name it "Dashboard". This is where your charts and KPIs will live. ChatGPT can't create the charts for you, but it can provide perfect step-by-step instructions.

Create High-Level KPIs

Start with a summary section at the top for your main KPIs. This section should give you the big picture in just a few seconds. We'll add cards for:

• Total Number of Projects
• Total Portfolio Budget
• Overall Percentage Complete
• Number of "At Risk" Projects

Again, use ChatGPT to get the formulas quickly.

• For Total Projects: =COUNTA(ProjectDataTable[ProjectName])
• For Total Budget: =SUM(ProjectDataTable[PlannedBudget])
• For Avg. Completion: =AVERAGE(ProjectDataTable[PercentComplete])
• For "At Risk" Projects: =COUNTIF(ProjectDataTable[ProjectStatus], "At Risk")

Arrange these in titled cells at the top of your dashboard sheet for easy viewing.

Create a Project Status Doughnut Chart

Visualizing the mix of project statuses is a great way to gauge portfolio health. Ask ChatGPT for guidance:

I want to make a doughnut chart in Excel to show the breakdown of projects by their status. My data is in a table named "ProjectDataTable" with a column called "ProjectStatus". Walk me through the steps.

ChatGPT will guide you to use a PivotTable as an intermediary step, which is best practice.

1. Click on any cell inside your ProjectDataTable sheet.
2. Go to the Insert tab and click PivotTable. Choose to place it in a new worksheet.
3. In the PivotTable Fields pane, drag ProjectStatus to the Rows area.
4. Drag ProjectID (or any other unique identifier) to the Values area. It should default to "Count of ProjectID".
5. Now, click on your completed PivotTable. Go to the Insert tab and choose Pie > Doughnut.
6. Cut and paste this chart into your Dashboard sheet and format it with a title and data labels.

Build a Budget vs. Actual Column Chart

This chart is essential for spotting budget issues at the project level.

How do I create a clustered column chart in Excel comparing "PlannedBudget" and "ActualCost" for each project? The project names are in the "ProjectName" column.

Again, the best method starts with a PivotTable and PivotChart:

1. Create another PivotTable from your main data source.
2. Drag ProjectName to the Rows area.
3. Drag PlannedBudget and ActualCost to the Values area. Ensure they are both set to "Sum".
4. With the PivotTable selected, go to Insert > Recommended Charts and choose the Clustered Column chart.
5. Move this chart to your dashboard and adjust the colors and title.

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

Add a Project Timeline (Gantt Chart)

Creating a Gantt-style chart is a classic Excel challenge, but ChatGPT can break it down.

I have project names, start dates, and end dates in an Excel table. How can I create a simple Gantt chart to visualize the project timelines?

ChatGPT will walk you through the clever "stacked bar" method:

1. First, add a new column to your ProjectDataTable called Duration. The formula is simple: =[@[EndDate]]-[@[StartDate]]. Ensure it is formatted as a Number.
2. On your Dashboard sheet, select a blank cell. Go to Insert > Chart > Stacked Bar. This will insert a blank chart.
3. Right-click the chart and Select Data. Under Legend Entries (Series), click Add.
4. Click Add again to add a second series for Duration, selecting the appropriate header and data.
5. For the Horizontal (Category) Axis Labels, click Edit and select all the data in your ProjectName column. Click OK.
6. Your chart now shows two colored bars for each project. Click on the first series (the StartDate bars) and format them to have No Fill. This makes them invisible, leaving only the Duration bars floating, which creates the Gantt effect.
7. Finally, right-click the project names axis and choose Format Axis. Under Axis Position, check the box for "Categories in reverse order" to display your projects from top to bottom.

Step 4: Make It Interactive with Slicers

The final touch is to add slicers, which are dynamic, user-friendly filter buttons. Ask ChatGPT a final question:

How can I add interactive filters to my Excel dashboard to filter all my pivot charts by "ProjectManager" and "Department" at the same time?

The instructions are simple:

1. Click on any one of your PivotCharts on the dashboard.
2. Go to the PivotChart Analyze contextual tab and click Insert Slicer.
3. In the pop-up window, check the boxes for ProjectManager and Department. Click OK.
4. Two slicer boxes will appear. Right-click each slicer and select Report Connections. Make sure you check the boxes for all the PivotTables you created. This ensures that one click on a slicer filters every chart on your dashboard simultaneously.

Now, you can click on a project manager's name and see only their projects, check a specific department's budget-to-actual, and get a tailored view of your portfolio in seconds.

Final Thoughts

By structuring your data mindfully and leveraging ChatGPT as your expert assistant, you can build a highly effective project portfolio dashboard in Excel without being a formula or charting wizard. This approach transforms a potentially intimidating task into a manageable and even creative process, letting you focus on the insights rather than the setup.

While building dashboards in Excel is powerful, it does involve a manual setup and refresh process. At Graphed, we automate this entire workflow. Instead of setting up tables and writing formulas, you can connect your data sources (like Google Sheets, Asana, or Jira) and simply ask for what you want in plain English: "build me a project portfolio dashboard showing the budget vs. actual and project status by manager." We generate a live, interactive dashboard for you in seconds, saving you hours of manual work and ensuring your data is always up-to-date.