How to Create a Project Portfolio Dashboard in Excel

Tracking a single project is hard enough, but trying to manage a whole portfolio of them can feel like an impossible task. A well-designed project portfolio dashboard can tame that chaos, giving you a single source of truth to see how everything is progressing. This guide will walk you through exactly how to build a dynamic and visual project portfolio dashboard in Excel, step-by-step.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Why You Need a Project Portfolio Dashboard

Before jumping into the nuts and bolts, it's worth understanding what you gain by having one. Constantly asking for status updates via email or Slack is inefficient and often leads to outdated information. A centralized dashboard solves this by providing instant clarity.

• A Single Source of Truth: Everyone, from team members to stakeholders, sees the same data at the same time. This eliminates confusion and miscommunication about project status, timelines, and budgets.
• Better Decision-Making: When you see the big picture, you can spot potential bottlenecks, identify at-risk projects, and reallocate resources more effectively. Are too many projects behind schedule? Is one department overloaded while another has capacity? The dashboard makes these issues obvious.
• Improved Stakeholder Communication: Instead of pulling together manual reports for every leadership meeting, you can simply share a link to your dashboard or a screenshot of it. It’s a clean, professional way to keep everyone informed without the extra work.

Planning Your Dashboard: Key Metrics to Track

A dashboard is only as good as the data it displays. The first step isn’t opening Excel, but rather deciding what information is most important for you and your stakeholders. Dumping every possible metric onto the screen will only create more noise. Start with the essentials.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Project-Level Details

This is the basic inventory of your projects. At a minimum, you'll want to track:

• Project Name: A clear, unique identifier for each project.
• Project Manager: Who is the accountable owner?
• Department/Team: Which team is responsible?
• Start Date: When the project officially kicked off.
• End Date: The planned completion date.

Timeline and Progress

This section tells you whether things are moving according to plan.

• Project Status: Use a simple category system like "On Track," "At Risk," "Off Track," or "Complete." This is arguably the most important high-level metric.
• Percent Complete: A numerical representation of progress (e.g., 75%). This helps distinguish between two "On Track" projects - one might be 10% done and the other 90%.

Financial Information

If your projects have budgets, tracking finances is non-negotiable.

• Budgeted Amount: The total approved budget for the project.
• Actual Spend: The total amount spent to date.
• Variance: The difference between Budget and Actual Spend. This quickly shows which projects are over or under budget.

Step-by-Step Guide: Building Your Portfolio Dashboard in Excel

Now for the fun part. Let's get into Excel and translate our plan into a functional dashboard. We'll set up a raw data tab, calculate summary metrics, and then build the visualizations.

Step 1: Create a "ProjectData" Tab

Never build your dashboard on the same sheet as your raw data. Create two tabs: one called "Dashboard" and another named "ProjectData." Your "ProjectData" tab is the engine, the "Dashboard" tab is the cockpit.

On the "ProjectData" tab, set up your columns based on the metrics you planned. The most crucial part of this step is to format your data as a table. This makes formulas and charts much easier to manage.

1. Organize your data with headers like Project Name, Project Manager, Status, Start Date, End Date, Percent Complete, Budget, and Actual Spend.
2. Fill in the data for all your projects.
3. Click anywhere inside your data range, go to the Insert tab, and click Table. Make sure "My table has headers" is checked.
4. Name your table. Click on your table, then go to the "Table Design" tab that appears in the ribbon. In the top-left corner, rename your table from "Table1" to something descriptive, like "Projects."

Your table should now look clean, with alternating colored rows for readability.

Graphed

Your AI Data Analyst to Create Live Dashboards

Connect your data sources and let AI build beautiful, real-time dashboards for you in seconds.

Build Your First Dashboard

Step 2: Create a Summary Section on the "Dashboard" Tab

On your "Dashboard" tab, let's create a high-level overview. This area will show key numbers at a glance. We'll use formulas to pull information from our "Projects" table.

Calculating Key Metrics:

Find some open cells on your dashboard sheet and add these labels:

• Total Projects: To count every project, use the COUNTA formula. It counts non-empty cells.
• Projects On Track: Here we use the COUNTIF formula, which counts cells that meet a certain condition.
• Projects At Risk: Use another COUNTIF for this one.
• Total Budget: A simple SUM function pointing to your table works perfectly.
• Total Actual Spend: Same as above, but for the actuals column.

These simple formulas create a dynamic summary. When you add a new project to your "ProjectData" table, these numbers will update automatically.

Step 3: Add Visualizations and Charts

Numbers are great, but charts tell the story faster. Let's add a few visuals.

Overall Project Status (Donut Chart)

1. First, you'll need to create a small summary table on your Dashboard sheet (or a hidden calculations sheet) to feed the chart. List the statuses: "On Track," "At Risk," "Off Track," "Complete." Beside each one, use a COUNTIF formula as we did above.
2. Once you have this small summary table:

Budget vs. Actual Spend (Bar Chart)

1. On the "Dashboard" tab, select an empty cell.
2. Go to Insert > Charts > Column or Bar Chart. A blank chart will appear.
3. Right-click the chart and choose Select Data.
4. Under "Legend Entries (Series)," click Add. For the "Series name," click on the "Budget" header cell in your ProjectData table. For "Series values," select all the data in the Budget column.
5. Click Add again. This time, use the "Actual Spend" header for the name and select all data in the Actual Spend column.
6. Under "Horizontal (Category) Axis Labels," click Edit and select all your project names from the ProjectData table.
7. Click OK. You now have a bar chart visually comparing the budget and actual for every project.

Bonus - A Gantt-Style Timeline Chart

Excel doesn't have a default Gantt chart, but we can make one using a stacked bar chart. This is a powerful visualization for timelines.

1. You'll need two new columns in your "ProjectData" table: "Days to Start" (which will be your Start Date formatted as a number) and "Duration" (calculated as [End Date] - [Start Date]).
2. Go to your dashboard sheet, click Insert > Chart > Stacked Bar Chart.
3. Right-click the blank chart and Select Data.
4. Add a series for "Days to Start." For the series values, select your "Days to Start" data.
5. Add another series for "Duration." For the series values, select your "Duration" data.
6. For the "Horizontal (Category) Axis Labels," edit it to select your project names. Click OK.
7. You'll see a chart with two colored bars. Click on the first series (the one for "Days to Start," usually blue). Right-click, select Format Data Series, go to the "Fill" options, and select No Fill. This makes the first part of the bar invisible, leaving you with floating bars that look just like a Gantt chart!
8. Finally, format a category axis and set "Categories in reverse order." Also, re-format value axis with correct range to show "human sensemaking" dates.

AI for Data Analysis

Crash Course

Graphed

Free PDF Guide

AI for Data Analysis Crash Course

Learn how to get AI to do data analysis for you — the best tools, prompts, and workflows to go from raw data to insights without writing a single line of code.

Download Free Guide

Step 4: Make It Interactive with Slicers

Slicers are filters that look like modern buttons. They turn a static dashboard into an interactive one.

1. Click on any of your charts.
2. Go to the Chart Analyze (or Design) tab and click Insert Slicer.
3. A dialog box will appear with all your data table headers. Check the box for "Project Manager." Click OK.
4. A slicer for Project Manager appears. Right-click on your Slicer and under Report Connections choose which visuals your Slicer will affect on the dashboard.
5. You can add a slicer for "Department" or "Project Status." Now you and your team can instantly filter the entire dashboard to see only the projects relevant to them.

Tips for an Even Better Dashboard

• Keep it Simple: The temptation is to add more and more, but the best dashboards are simple. Focus on clarity over clutter. If a chart or number doesn't help someone make a decision, it probably doesn't belong.
• Use a Consistent Color Scheme: Align your colors with your company's branding or use a logical scheme (e.g., green for good, red for bad). This makes your dashboard look more professional and easier to interpret at a glance.
• Define a Clear Update Process: Even the best dashboard is useless if the data is stale. Decide how often the data will be updated (e.g., every Monday morning) and who is responsible for providing those updates.

Final Thoughts

Building a project portfolio dashboard in Excel transforms messy data from dozens of projects into a clean, actionable command center for your entire team. By carefully setting up your data, using formulas and charts, and adding interactive elements like slicers, you can create a powerful tool that brings clarity and helps drive better decisions.

While Excel is fantastic, you've likely noticed the process is manual - from setting up tables and formulas to the constant need for someone to update the raw data file. We built Graphed to remove this manual work. Instead of spending hours in spreadsheets, you can connect your data sources (like your project management tools) once and then simply ask for what you need in plain English - like "create a dashboard showing project status and budget burn rate for all active projects." Your dashboard appears in seconds, updates in real-time, and you never have to wrestle with a VLOOKUP formula again.