How to Create a Project Management Dashboard in Excel with AI

Trying to keep track of project tasks, timelines, and budgets in a static spreadsheet can feel like you're herding cats in the dark. A clear, visual dashboard is the answer, and you don’t need an expensive, complicated tool to create one. This tutorial will walk you through how to build a dynamic project management dashboard in Excel, first the traditional way, and then show you how AI can do almost all of the heavy lifting for you.

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

Why Use a Dashboard for Project Management?

Before building anything, it's useful to understand what a dashboard actually accomplishes. It's not just about making your data look nice, it’s about transforming raw project information into clear, actionable insights.

A good project management dashboard gives you a high-level overview of everything you care about in one place. Instead of digging through multiple tabs or different files, you get a single-screen "cockpit" view of your project's health. You can instantly see what’s on track, what’s lagging, where your budget stands, and who’s working on what. This allows you and your stakeholders to make smarter, quicker decisions without waiting days for a manual report.

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 1: Lay the Groundwork by Organizing Your Project Data

Your dashboard is only as good as the data powering it. The most common mistake people make is not setting up their source data cleanly. Resist the urge to merge cells, add decorative colors, or scatter notes everywhere. The goal is to create a simple, raw data table that a machine can easily read.

Create a new tab in your Excel file and name it "ProjectData" or something similar. Set up columns for all the key project information you want to track. Think of each row as a single task.

Here are the essential columns you'll want to include:

• Project Name: The name of the overall project the task belongs to.
• Task ID: A unique identifier for each task (e.g., 1, 2, 3).
• Task Description: A brief description of the task.
• Assigned To: The person responsible for the task.
• Status: The current status (e.g., Not Started, In Progress, Completed, On Hold).
• Start Date: The planned start date for the task.
• Due Date: The planned end date for the task.
• Budgeted Hours: The estimated time allocated for the task.
• Actual Hours: The actual time spent so far.

Your simple data table should look something like this:

Once you've entered your data, select any cell inside your data range and press Ctrl + T (or Cmd + T on Mac) to format it as an official Excel Table. This makes it much easier to work with, as formulas and charts will update automatically when you add new rows of data.

Step 2: Build the Dashboard Manually with PivotTables and Charts

With your data prepped, it's time to create the visual elements for your dashboard. We'll build these individually and then assemble them on a separate "Dashboard" sheet.

Building Key Performance Indicators (KPIs)

KPIs show your most important metrics at a glance. We'll create cards for Total Tasks, Completed Tasks, and In-Progress Tasks.

1. Navigate to your "ProjectData" sheet, click inside your data table, and go to Insert > PivotTable. Place it on a new worksheet.
2. Drag the "Status" field into the Rows area and the "Task ID" field into the Values area. Excel will automatically set it to "Count of Task ID."
3. Now, in three separate cells next to your PivotTable, create your KPIs. For ‘Total Tasks’, you can use a formula like:

=COUNTIFS(ProjectData[Status],"<>")

1. For Completed Tasks, use:

=COUNTIFS(ProjectData[Status],"Completed")

You now have clean numbers you can use to build "cards" on your dashboard sheet.

Visualizing Task Status and Workload

Charts help you quickly understand breakdowns and comparisons.

• Task Status Donut Chart: Go back to your PivotTable. With the PivotTable selected, click on PivotTable Analyze > PivotChart. Choose a Donut chart. This will create a chart showing the proportion of tasks by status.
• Workload by Team Member Bar Chart: Create another PivotTable. This time, drag "Assigned To" into the Rows area and "Count of Task ID" into the Values area. Convert this data into a bar chart to see who has the most tasks assigned to them.

Creating a Project Timeline (Gantt-style Chart)

While Excel doesn’t have a built-in Gantt chart, you can create a simplified version using a stacked bar chart. This is a bit more involved, but powerful for visualizing timelines.

1. On your "ProjectData" tab, add a new column called "Duration" with the formula =[@[Due Date]]-[@[Start Date]].
2. Create a new Chart on your dashboard sheet using the 'Stacked Bar' chart type.
3. Right-click on the chart and select ‘Select Data.' For the data source, choose both your start date column AND your newfound "duration" column. This should also include your "Task description" column which you can use for the vertical labels.
4. Right-click the vertical axis (with the tasks listed) and choose Format Axis. Under Axis Options, check the box for "Categories in reverse order" to put your first task at the top.
5. Click on the blue bars representing the "Start Date" series. In the Format Data Series pane, go to Fill and select "No fill" to make them invisible.

What remains is a floating bar for each task, effectively showing you the timeline of each task!

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

Assembling the Dashboard

Once you've created all your individual charts and KPIs, cut and paste them onto a new sheet you’ve named "Dashboard." Arrange them logically, add a title, and use Slicers (Insert > Slicer on a PivotTable) to allow users to filter the entire dashboard by project name or team member.

Manually refreshing this can be tiresome. You’ll need to go to the Data tab and click Refresh All every time you update your source data.

Step 3: The Game-Changer - Using AI to Do the Work for You

The manual method works, but it takes time and requires you to know your way around PivotTables and chart formatting. This is where AI drastically simplifies the process, turning hours of clicks into a few simple prompts.

Instead of manually building each component, you can describe what you want, and AI can generate it for you. This approach is perfect for folks who aren't familiar with advanced Excel functions and just want to get to the insights quickly.

Using Excel's Built-in 'Analyze Data' Feature

Microsoft has started embedding AI features directly into Excel. The 'Analyze Data' tool is a great example.

1. Click any cell within your formatted project data table.
2. Go to the Home tab and click on the Analyze Data button on the far right.
3. A pane will open on the side, automatically suggesting interesting charts and insights based on your data.
4. Even better, there’s a prompt box where you can ask a question in plain English. Try typing something like, "Show a count of tasks by status as a pie chart" or "What is the average duration by project?"

Excel will instantly generate a PivotChart answering your question, which you can then insert directly into your workbook. This is a massive shortcut that replaces all the manual steps of creating PivotTables and formatting charts.

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

Taking it Further with Dedicated AI Analytics Tools

Excel's Analyze Data feature is powerful, but modern AI dashboards built for data analysis take this concept even further. These tools directly connect to data sources - like an Excel file, Google Sheet, or even project management apps like Jira or Asana - and let you build entire dashboards using natural language.

The process is typically straightforward:

1. Connect Your Data Source: You link your spreadsheet or app with a few clicks. The tool keeps your data synced automatically, so you're always looking at real-time information, not a stale report.
2. Ask for What You Want: Instead of fumbling with chart settings, you type a request just like you're talking to a colleague. For example: "Create a bar chart comparing budgeted vs. actual hours for each person," or "Show me all tasks that are due this week, sorted by project."
3. Build Iteratively: The AI generates the visualization in seconds. You can then ask follow-up questions to refine it further. "Okay, now filter this chart just for the Phoenix Project," or "Change that to a line chart showing completed tasks over time."

This conversational approach completely removes the technical barrier. You don't need to learn a single formula or remember where a specific button is. Your expertise is in knowing what questions to ask about your project, not in your Excel proficiency.

Final Thoughts

Building a project management dashboard in Excel provides incredible value by turning cluttered data into clear, visual signals. While the traditional process using PivotTables and charts is effective, it’s also time-consuming and rigid. By embracing AI, you can shift your focus from manually wrangling data to asking meaningful questions and uncovering insights faster.

This is precisely why we created Graphed — to make powerful data analysis accessible to everyone, not just those with technical skills. With our platform, you can connect your Excel sheets, Google Sheets, or other project management tools, and then simply describe the dashboard you need in plain English. Instead of spending an afternoon building charts, you can ask for a full project overview showing task status, workloads, and timelines, and get a live, shareable dashboard in seconds. You get back hours in your week and empower your team to make better, data-driven decisions without the headache.