How to Create a Project Management Dashboard in Excel

Transforming a cluttered spreadsheet of project tasks into a clear, visual dashboard doesn't require complex software. With a bit of organization and a few key features, you can build a powerful project management dashboard directly in Excel. This article will guide you step-by-step through setting up your data, creating essential charts, calculating key metrics, and adding interactivity to keep your projects on track.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Why Use an Excel Dashboard for Project Management?

Before jumping into the “how,” let’s touch on the “why.” While dedicated project management tools are great, using Excel offers some unique advantages. It’s accessible since nearly everyone has it, incredibly flexible to meet your specific needs, and cost-effective. You can tailor it to show exactly what you and your stakeholders need to see, without being locked into the rigid structure of other applications.

An Excel dashboard puts you in complete control, allowing you to centralize information, monitor progress visually, and quickly identify potential roadblocks before they become serious problems.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 1: Laying the Groundwork - The Data Sheet

A great dashboard is built on a foundation of well-structured data. All your charts and metrics will pull from a single, organized source of truth. If your data is messy, your dashboard will be unreliable.

Start by creating a new sheet in your workbook named something like "Project Data" or "Raw Data." This sheet is where you'll list every task associated with your project. The goal here is to be meticulous. Set this up as a proper Excel Table, which is crucial for making your dashboard dynamic. To do this, simply enter your headers and some data, then select a cell and press Ctrl + T (or go to Insert > Table).

Essential Data Columns:

• Task ID: A unique identifier for each task (e.g., 1, 2, 3 or A-01, A-02).
• Task Description: A clear, concise description of the task.
• Project Phase/Category: Groups tasks into larger phases (e.g., Planning, Development, Testing, Launch).
• Assigned To: The name of the person or team responsible for the task.
• Priority: The task's priority level (e.g., High, Medium, Low).
• Start Date: The planned start date for the task.
• End Date: The planned completion date for the task.
• Duration (Days): The number of days the task is expected to take. This can be calculated with a simple formula: = [End Date] - [Start Date].
• Status: The current state of the task (e.g., Not Started, In Progress, On Hold, Completed, Overdue). This is one of the most important columns for tracking progress.
• % Complete: A numerical representation of how much work is done on a task.

By using an Excel Table, any new tasks you add to the bottom of the list will automatically be included in your dashboard's calculations and charts without you having to manually update data ranges.

Step 2: Building Your Dashboard Canvas

With your data neatly organized, it’s time to build the dashboard itself. Create a new, clean sheet and name it "Dashboard." This separation is key - your "Project Data" sheet is for data entry, while the "Dashboard" sheet is exclusively for visualization. Nobody needs to see the raw data unless they're looking for specific details.

Designing the Layout:

Before you start adding charts, sketch a quick layout. A common and effective layout includes:

• KPIs at the top: A row of high-level summary numbers (e.g., Overall Progress %, Completed Tasks, Open Tasks).
• Main visuals in the middle: Key charts like a Gantt chart or task status overview.
• Supporting details on the side or bottom: Charts showing workload distribution or tasks by priority.
• Filters/Slicers on the side: Interactive controls to filter the view.

Formatting this sheet with a simple background color and a clear header can make it feel more like a professional report and less like a spreadsheet.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 3: Creating Essential Dashboard Components

Now for the fun part: turning your raw data into insightful visuals. We’ll primarily use PivotTables and PivotCharts, which are powerful tools for summarizing data without complex formulas.

1. High-Level KPI Cards

These are the at-a-glance numbers that give you a quick health check of the project. In your Dashboard sheet, create text boxes or format some cells to stand out as "cards."

You can use simple formulas to pull in these metrics directly from your data table. Let’s assume your table is named ProjectTable.

• Overall Project Progress (%): Calculates the percentage of tasks marked as "Completed."
• Tasks Remaining: Counts all tasks that are not yet complete.
• Overdue Tasks: Counts tasks that are not completed and whose end date has passed.

Format these cells with a large font size to make them stand out.

2. Task Status Overview (Donut Chart)

A donut or pie chart is perfect for showing the proportion of tasks in different stages. This helps you quickly see if a large number of tasks are stuck in one phase.

• Go to your "Project Data" sheet and click anywhere inside your data table.
• Go to Insert > PivotChart. Excel will ask where to place it, choose "Existing Worksheet" and select a cell on your "Dashboard" sheet.
• In the PivotChart Fields panel, drag the Status field to both the Legend (Series) area and the Values area.
• This will create a default column chart. With the chart selected, go to the Design tab and click Change Chart Type. Select Pie > Donut.
• Clean up the chart by removing unnecessary elements like the legend (the slices can have data labels instead) and giving it a clear title like "Task Status."

3. Workload Distribution (Bar Chart)

Want to see if one team member is overloaded? A simple bar chart can show who is assigned the most tasks.

• Follow the same process to insert a new PivotChart onto your Dashboard sheet.
• In the PivotChart Fields panel, drag the Assigned To field to the Axis (Categories) area.
• Drag the Task Description field to the Values area. It will default to "Count of Task Description," which is exactly what you want.
• Clean up the chart design to make it easy to read. You get an instant visual overview of task distribution across the team.

4. Project Timeline (Gantt Chart)

A Gantt chart is a project management staple. Creating a true Gantt chart in Excel is a bit of a workaround, but it’s very effective once set up.

• Select your task data (not the whole table, just the Task Description, Start Date, and Duration columns).
• Go to Insert > Chart > Bar > Stacked Bar Chart.
• Your chart will look strange at first. Right-click on the vertical axis (with the task names) and select Format Axis. Check the box for "Categories in reverse order" to list your tasks from top to bottom.
• Now, we hide the first part of the bar. The blue bars likely represent the Start Date. Click on one of the blue bars to select the entire series.
• Right-click and go to Format Data Series. Under the Fill & Line options, select "No fill" for the fill and "No line" for the border.

Just like that, the "start date" portion of the bars becomes invisible, leaving you with floating bars that represent the task duration, just like a classic Gantt chart. You can then format the axis to tighten the date range and make the chart more compact.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Making Your Dashboard Interactive with Slicers

Slicers are user-friendly filters that allow you (or your stakeholders) to drill down into the data without having to use the clunky filter dropdowns in the PivotChart fields.

• Click on one of your PivotCharts, like the Task Status donut chart.
• Go to the PivotChart Analyze tab and click Insert Slicer.
• A dialog box will appear with all your data table columns. Check the boxes for the fields you want to filter by - common ones are Assigned To and Priority. Click OK.
• Slicers for each of these fields will appear on your dashboard. Now, to make one slicer control all your charts, right-click the slicer and select Report Connections.
• In the pop-up, check the box for every PivotTable/PivotChart in your workbook. Repeat this for each slicer.

Now, when you click a person’s name in the "Assigned To" slicer, all of your charts will instantly update to show data only for that person. This transforms your static report into a powerful, interactive analytical tool.

Final Thoughts

Creating a project management dashboard in Excel is an incredibly valuable skill. By structuring your data properly in an Excel Table and leveraging tools like PivotCharts and Slicers, you can build a dynamic, interactive report that offers clear insights into your project's health and helps you make proactive decisions.

While Excel is fantastic for customized dashboards, the setup and maintenance can become time-consuming, especially when your project data lives in multiple places like Asana, Jira, or your CRM. At Graphed we automate this entire process. You can connect your different data sources in a few clicks, and then simply ask in plain English to create a real-time dashboard. In seconds, you get live, interactive reports, freeing you from manual data wrangling and letting you focus on actually managing your projects.