Graphed LogoGraphed
Graphed LogoGraphed
How It WorksAgentsIntegrationsPricing

How to Create a Project Dashboard in Excel

Cody Schneider••8 min read

Building a project dashboard lets you see your entire project's health at a glance, from task progress to looming deadlines. While dedicated project management tools are great, you can build a powerful, dynamic, and fully custom dashboard using a tool you already know and own: Microsoft Excel. This guide will walk you through creating a project dashboard from scratch, step-by-step, no add-ins required.

GraphedGraphed

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.

Watch Graphed demo video

What is a Project Dashboard (and Why Use Excel)?

A project dashboard is a one-page summary that visualizes your most important project metrics - your Key Performance Indicators (KPIs). Instead of digging through endless task lists and spreadsheets, a dashboard gives you a high-level, real-time view of progress, team workload, and potential bottlenecks so you can make informed decisions quickly.

So, why Excel? It's perfect for creating a dashboard for a few key reasons:

  • It's Accessible: Almost everyone has Excel. You don't need to buy or learn a new, complex business intelligence tool just to track your project.
  • It's Flexible: You have complete control over what you track and how you visualize it. Your dashboard can be tailored precisely to your project's specific needs.
  • It's Skill-Building: The process will sharpen your spreadsheet skills, which are valuable in any role.

Step 1: Structure Your Raw Data

Every effective dashboard is built on a foundation of clean, well-structured data. This is the single most important step. Your visualizations and calculations are only as good as the data they pull from. For the best results, dedicate one sheet in your workbook purely for this raw data. Let's call it a "Data" tab.

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.

Set Up Your Data as an Excel Table

Don't just type your data into a range of cells. Instead, format it as a proper Excel Table. This is a game-changer because tables automatically expand to include new rows, meaning your dashboard charts and formulas will update dynamically as you add more tasks.

  1. Enter your column headers in the first row.
  2. Add a few rows of sample project data.
  3. Select any cell within your data range.
  4. Go to the Insert tab and click Table.
  5. Ensure the "My table has headers" box is checked and click OK.

Your table should include at least the following columns:

  • Task Name: A brief description of the task.
  • Assigned To: The name of the team member responsible.
  • Start Date: The date the task is scheduled to begin.
  • Due Date: The deadline for the task.
  • Status: The current status (e.g., "Not Started," "In Progress," "Completed," "On Hold").
  • % Complete: A numerical value from 0 to 100 representing task completion.

Keeping this data on a separate, organized sheet prevents accidental edits and keeps your workbook tidy.

Step 2: Create a Calculations Sheet

While you can write formulas directly within your dashboard, a great best practice is to create another separate sheet to house all your summary calculations. Think of this as the "engine room" of your dashboard. This decouples your data analysis from your data presentation, making your dashboard easier to manage and debug if something goes wrong.

Create a new sheet and name it "Calculations." On this sheet, we'll calculate the key metrics that will power our visuals. Because we used an Excel Table (let's assume you named it ProjectTasks), we can use clean, structured references.

GraphedGraphed

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.

Watch Graphed demo video

Calculate Key Project Metrics

Here are some of the most common metrics for a project dashboard and the formulas to calculate them:

Overall Task Counts

To count the total number of tasks in your project, use this formula:

=COUNT(ProjectTasks[Task Name])

To count tasks by their status:

  • Completed Tasks: =COUNTIF(ProjectTasks[Status], "Completed")
  • In Progress Tasks: =COUNTIF(ProjectTasks[Status], "In Progress")
  • Not Started Tasks: =COUNTIF(ProjectTasks[Status], "Not Started")

Overdue Tasks

An "overdue" task is one that is not marked "Completed" and whose due date is in the past. We can use COUNTIFS for this, as it checks multiple criteria:

=COUNTIFS(ProjectTasks[Status], "<>Completed", ProjectTasks[Due Date], "<" & TODAY())

This formula counts rows where the Status is not equal to "Completed" AND the Due Date is less than today's date.

Overall Project Progress

To calculate the average completion of all tasks, you can use the AVERAGE function on your % Complete column:

=AVERAGE(ProjectTasks[% Complete])

Your "Calculations" sheet will now look like a tidy summary of your project's vital signs, ready to be visualized.

Step 3: Build and Arrange Your Visualizations

Now for the fun part! On a new, clean sheet titled "Dashboard," we'll start building the visual components. There are no right or wrong charts to use, but here are a few popular and effective ones for project tracking.

Create KPI Cards

KPI cards are large, hard-to-miss numbers that display your most important metrics. They are incredibly easy to create.

  1. Go to the Insert tab and select Shapes > Rectangle. Draw a rectangle on your dashboard grid.
  2. Style the shape as you like (e.g., color, border).
  3. Do not type directly into the shape. Instead, with the shape selected, click into the Formula Bar at the top of Excel.
  4. Type an = symbol, then click over to your "Calculations" sheet and select the cell containing your total task count. Press Enter.
  5. The shape's text will now be linked to that cell and will update automatically. Add a Text Box underneath to label the KPI ("Total Tasks").

Repeat this process for your other key metrics like "Completed," "In Progress," and "Overdue."

Create a Task Status Donut Chart

A donut chart is a great way to show the proportion of work completed versus what's still pending.

  1. On your "Dashboard" sheet, select the data from your "Calculations" sheet containing the labels ("Completed," "In Progress," "Not Started") and their corresponding counts.
  2. Go to Insert > Pie > Doughnut.
  3. Cut and paste this chart onto your Dashboard sheet.
  4. Customize the chart's title, colors, and labels to make it clear and on-brand. You can right-click the chart elements to find format options.

Create a Project Progress Bar

A simple bar chart is perfect for showing the overall project completion percentage.

  1. Select the single cell on your "Calculations" sheet containing the AVERAGE of % Complete.
  2. Go to Insert > Column or Bar Chart > 2-D Bar.
  3. This will look a bit odd at first. First, right-click the vertical axis (the one with the label "1") and delete it. Do the same for the chart title.
  4. Right-click the horizontal axis and choose Format Axis.
  5. In the Axis Options pane that appears, set the Bounds Minimum to 0 and the Bounds Maximum to 1. This ensures your progress bar is always on a scale of 0% to 100%.
  6. Finally, right-click the bar itself, choose Format Data Series, and adjust the Gap Width to be smaller to create a thicker, more prominent bar.

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.

Create a Bar Chart with a PivotTable

What if you want to see who on your team has the most tasks assigned? A PivotTable is the easiest way to do this.

  1. Go back to your "Data" sheet and select any cell inside your ProjectTasks table.
  2. Go to Insert > PivotChart. In the dialog box, just click OK. This will create a new sheet with a PivotTable and a linked PivotChart.
  3. From the PivotTable Fields pane on the right, drag the Assigned To field into the Axis (Categories) box.
  4. Next, drag the Task Name field into the Values box. It should default to "Count of Task Name," which is exactly what we want.
  5. Your PivotChart will update instantly to show a bar chart of tasks per team member. You can now cut this chart and paste it onto your Dashboard sheet.

Step 4: Design and Finalize Your Dashboard

With all your visual components created, the final step is to arrange them into a clean, easy-to-read layout.

  • Turn Off Gridlines: Go to the View tab and uncheck the Gridlines box. This single click makes your dashboard look much more professional.
  • Use Consistent Colors: Stick to a simple color palette that is easy on the eyes. Use color intentionally - for instance, make your "Overdue" KPI card red to draw attention.
  • Align Your Objects: Use Excel's alignment tools. Select multiple charts or shapes while holding Shift, then go to the Shape Format tab and use the Align tool to perfectly align objects to the left, top, or distribute them evenly.
  • Add Slicers for Interactivity: If you used a PivotChart, you can make your dashboard interactive. Select your PivotChart, go to the PivotChart Analyze tab, and click Insert Slicer. Choose a field like "Assigned To" or "Status." This will create a filter button that allows anyone viewing the dashboard to drill down into the data in a single click.

Final Thoughts

Building a project dashboard in Excel is a straightforward process when broken down into logical steps. By separating your raw data from your calculations and visualizations, you can create a robust and dynamic reporting tool that automatically updates as your project progresses, helping you stay on top of every detail.

While Excel is fantastic for building custom dashboards, the process can become time-consuming, especially when your project data lives in multiple apps like Salesforce, an accounting tool, or your CRM. At some point, manually downloading CSVs and pasting them into your "Data" tab can get old fast. That's where we aimed to create a much more streamlined experience. With Graphed you can connect your data sources in seconds and simply ask for what you want to see - like, "Create a dashboard showing project tasks by status and owned by each team member this quarter." We automatically generate the real-time dashboard for you, saving you from writing formulas and building charts manually.

Related Articles