How to Create a Project Portfolio Dashboard in Excel with AI

Trying to manage multiple projects at once can quickly feel like you're juggling flaming torches. Without a clear, high-level view, it's easy for details to get missed, resources to be misallocated, and for key stakeholders to lose sight of the bigger picture. Creating a project portfolio dashboard in Excel is a straightforward solution to bring all that chaos under control. This article walks you through how to build one from the ground up and how to use Excel’s hidden AI features to make it even more powerful.

What is a Project Portfolio Dashboard?

While a regular project dashboard zooms in on the tasks, timeline, and budget of a single project, a project portfolio dashboard zooms out. It's a one-page, high-level summary that tracks the performance of all your projects in a single view. The goal isn't to see every single task, but to monitor the overall health, progress, and financial status of your entire portfolio at a glance.

It's designed to answer critical questions for managers and executives, like:

• Which projects are on track, and which are at risk or delayed?

• How are we tracking against our total budget across all initiatives?

• Are our resources (team members) allocated effectively, or is someone overloaded?

• Which projects are delivering the highest impact or ROI?

• Are we on track to meet our strategic goals for the quarter?

A well-built portfolio dashboard gives you the visibility needed to make smarter, data-driven decisions, allocate resources more effectively, and proactively address risks before a small problem becomes a major fire.

Why Use Excel for Your Project Portfolio Dashboard?

Sure, there are dozens of specialized project management tools out there like Jira, Asana, and Monday.com. Many of them are fantastic. So why bother with Excel? The simple answer is that it's ubiquitous, flexible, and powerful enough for the job.

Almost everyone on your team already has Excel and knows the basics of how to use it. This eliminates the need for expensive subscriptions and time-consuming training on a new platform. Excel’s flexibility allows you to customize your dashboard to track the exact metrics that matter to your organization, without being constrained by a software's pre-defined limitations. With features like PivotTables, Slicers, and a vast library of formulas, you can create a completely custom and interactive report that's perfectly tailored to your needs.

Step 1: Gather and Structure Your Project Data

Before you can build your dashboard, you need organized, clean data. The quality of your dashboard is 100% dependent on the quality of your source data. Your first step is to create a master data sheet in a new Excel file. Create a table that will serve as the "brain" for your entire dashboard, listing each project as a separate row.

For each project, you should collect at a minimum the following data points. You can always add more custom fields that are specific to your business.

• Project Name: A clear, unique name for the project.

• Project Manager: Who is responsible for the project's success.

• Start Date: The official kick-off date.

• End Date: The projected completion date.

• Status: The current health of the project (e.g., On Track, At Risk, Delayed, On Hold, Completed). Using a drop-down list here is a great way to ensure consistency.

• % Complete: The project's progress as a percentage.

• Budget: The total allocated budget for the project.

• Spent: The amount of the budget spent to date.

Once you've entered your data, turn it into an official Excel Table. To do this, click anywhere inside your data range and press Ctrl + T (or go to Insert > Table). This is a game-changer. It makes your data dynamic, easier to reference in formulas, and unlocks powerful features like Slicers.

Your table should look something like this:

Step 2: Design Your Dashboard Layout in a New Sheet

With your data organized, it's time to start working on the fun part. Create a new sheet and name it "Dashboard." This separation is key - your raw data lives in one place, and your polished visual report lives in another. This keeps things clean and prevents accidental edits to your source data.

Before you start inserting charts, sketch out a rough layout on paper. Think about your audience. What are the 3-5 most important things they need to see? A common layout includes:

• Top Section: Key Performance Indicators (KPIs) showing headline numbers - Total Projects, Total Budget, 'At Risk' Count.

• Left & Center: Primary visualizations, like a chart showing project status distribution and a bar chart comparing project budgets vs. actuals.

• Right or Bottom: Detailed views, like a list of delayed projects or a timeline view.

Step 3: Build Your Dashboard with Key Formulas, Charts, and Slicers

Now you can start populating your dashboard layout with live data fed from your data sheet.

Calculating Key Metrics (KPIs)

For your top-line KPIs, use powerful formulas like COUNTIF, SUM, and SUMIF to pull summary stats. If you named your data table "ProjectData" (you can do this in the Table Design tab), your formulas will be clean and easy to read.

For example, to get a live count of all your projects currently "At Risk," you would use:

=COUNTIF(ProjectData[Status], "At Risk")

To calculate the total budget for all projects, you can simply use:

=SUM(ProjectData[Budget])

Visualizing Portfolio Health and Budgets

Charts bring your data to life. Create the core visuals of your dashboard from pivot tables built on your source data.

• Project Status (Pie or Donut Chart): From your Data sheet, go to Insert > PivotChart. Drag the "Status" field into both the "Axis (Categories)" and "Values" areas. Excel will automatically count the projects in each status category. Then, change the chart type to a Pie or Donut chart for a simple visual breakdown.

• Budget vs. Spent (Bar Chart): Create another PivotChart. This time, pull "Project Name" into the Axis area and both "Sum of Budget" and "Sum of Spent" into the Values area. A Clustered Bar chart works perfectly for displaying these side-by-side comparisons for each project.

• % Complete (Bar Chart with Conditional Formatting): For a simple view of project progress, list each project name and use an XLOOKUP or a direct link to pull in its "% Complete" value. Then, use Conditional Formatting (Home > Conditional Formatting > Data Bars) to create in-cell progress bars.

Making Your Dashboard Interactive with Slicers

Slicers are the secret sauce for making an Excel dashboard feel like a real analytics app. They are clickable filter buttons that control your pivot tables and charts. Instead of manually filtering dropdowns, you can click a button for "Alice" and instantly see only her projects reflected in all your charts.

To add a slicer, click on any of your PivotCharts. On the ribbon, navigate to PivotChart Analyze > Insert Slicer. A dialog box will pop up where you can check the fields you want to filter by, such as "Project Manager" or "Status." You can connect a single slicer to multiple charts so they all update in unison as you filter.

Step 4: Supercharge Your Dashboard with Excel’s AI Features

This is where things get really interesting. Modern versions of Excel have powerful AI-driven features built-in that can help you find insights you might have otherwise missed and dramatically speed up your workflow.

Leverage "Recommended Charts"

Don't know which chart type works best? Let Excel decide. Simply highlight a range of your data (like your project names, budgets, and spent columns), go to the Insert tab, and click Recommended Charts. Excel's AI analyzes your selection and suggests several appropriate chart types, giving you a live preview of how each would look with your data. This takes the guesswork out of visualization.

Unlock Insights with "Analyze Data"

The "Analyze Data" feature is essentially having a junior data analyst built right into Excel. With a single click, it scans your data for trends, patterns, outliers, and correlations and then automatically generates relevant charts and pivot tables for you.

To use it, click anywhere inside your master project data table, go to the Home tab, and click the Analyze Data button on the far right. A panel will appear with dozens of auto-generated insights. Even better, you can ask it questions in plain English. Just type into the search box at the top, such as:

• "Show delayed projects"

• "Sort projects by most over budget"

• "What is the average % Complete for Bob's projects?"

Analyze Data will interpret your question and instantly create the chart or answer. You can then add these auto-generated charts directly to your dashboard sheet.

Final Thoughts

Building a project portfolio dashboard in Excel is a powerful way to bring much-needed clarity to your operations and communicate progress effectively. By starting with a clean data structure and building up your visuals methodically, you can create a centralized source of truth that keeps everyone aligned - from project team to leadership.

We built Graphed because we know that even with a powerful tool like Excel, the process of manually setting up, connecting, and refreshing data is still a massive time suck for busy teams. Our goal is to eliminate that friction completely. With our platform, you can connect directly to your project management tools - along with your sales, marketing, and finance platforms - and create automated, real-time dashboards simply by describing what you want to see. It’s like having a data analyst on call 24/7 who can turn your questions into live dashboards in seconds.