To make a project dashboard in Excel, organize project details in an Excel Table, calculate a few key measures such as project count and average completion, then summarize the data with charts or PivotTables on a separate dashboard sheet. Add filters for fields such as owner or status so readers can focus on the projects they need to review.
What a project dashboard shows
A project dashboard is a concise view of project health, workload, timing, and cost. It is usually a reporting layer over a more detailed project list, not a replacement for the task plan or the system where teams record day-to-day work. A useful dashboard answers practical questions quickly: How many projects are active? Which are at risk? Are deadlines approaching? Is progress broadly on track?
The right content depends on the audience. A team lead may need owners, due dates, blockers, and next milestones. An executive may prefer a smaller set of portfolio measures, such as active projects, late projects, budget variance, and overall progress. Showing every task and every field at once makes the page harder to scan, so start with decisions the dashboard should support.
Excel works well when the portfolio is modest, the source data is accessible, and people need a flexible report they can inspect or adapt. It is less suitable as a shared live project-tracking system if many users need simultaneous updates, permissions, automated workflows, or a dependable audit trail.
Set up a reliable source table
Put the underlying data on a sheet such as Projects, with one row per project and one column per attribute. Avoid merged cells, blank separator rows, and totals inside the data range; those can interfere with sorting, filtering, formulas, and PivotTables. Select the range and choose Insert > Table, or use Ctrl+T on Windows (Command+T on Mac, where the dialog may vary). Confirm that the table has headers, then give it a clear name using the Table Design tab.
A practical starter table might contain these columns:
- Project and Owner: identifiable names, entered consistently.
- Status: a controlled set such as Not started, In progress, At risk, and Complete.
- Start Date and Due Date: real Excel dates, not text that merely looks like a date.
- % Complete: numeric percentages from 0% to 100%.
- Budget and Actual Cost: amounts using the same currency and accounting basis.
For example, a five-project sample could have completion values of 40%, 60%, 100%, 0%, and 60%. Their statuses might include one At risk project, while budgets total $50,000 and actual costs total $32,300. These figures are illustrative; a real dashboard is only as trustworthy as the definitions and updates in its source table.
Use Excel data validation to reduce inconsistent entries. Select the Status cells and choose Data > Data Validation, then set Allow to List and supply the approved status values. Keep the list in a small reference range if it may change. Establish what each status means—for instance, whether At risk is a judgment from the owner or triggered by a measurable condition—so that different people do not apply it differently.
Choose KPIs that answer real questions
Start with a handful of measures that help someone act. A common first row of dashboard cards includes total projects, projects at risk, average completion, and remaining budget. With an Excel Table named Projects, these formulas update as rows are added to the table. Before each formula, the assumed layout is the table columns described above, with one row for each project.
- With project names in
Projects[Project], the total project count is=COUNTA(Projects[Project]). For the five sample projects, the result is 5. - With statuses in
Projects[Status], the number marked At risk is=COUNTIF(Projects[Status],"At risk"). In the sample, the result is 1. - With numeric completion values in
Projects[% Complete], average completion is=AVERAGE(Projects[% Complete]). For 40%, 60%, 100%, 0%, and 60%, the result is 52%. - With budget and actual-cost amounts in
Projects[Budget]andProjects[Actual Cost], budget less actual cost is=SUM(Projects[Budget])-SUM(Projects[Actual Cost]). The sample result is $17,700 remaining: $50,000 minus $32,300.
Label that last measure carefully. It is a simple difference between entered totals; it does not establish that spending is on schedule, that committed costs are included, or that a project will finish under budget. Likewise, an average completion percentage can be misleading when projects differ greatly in size. If appropriate, show project-level progress alongside the average, or calculate a weighted measure using an agreed basis such as planned effort. Do not imply that an unweighted average represents portfolio value unless that is what the organization intends.
Build the dashboard sheet
Add a separate sheet named Dashboard and arrange the most important information from top to bottom. A straightforward layout is a title and reporting date, a row of KPI cards, one or two charts, and a compact detail section listing projects that need attention. Keep the raw source table on its own sheet so viewers do not confuse input data with summary results.
- Decide who will read the dashboard and what decisions they need to make.
- Check that source columns have clear headers, consistent values, and usable date and number formats.
- Create KPI cells or PivotTables from the source table, then place the summaries on the Dashboard sheet.
- Add charts only where a visual comparison makes the information easier to understand.
- Test the dashboard after adding a new project, changing a status, and filtering the data.
For a PivotTable, select a cell in the source table and choose Insert > PivotTable, then choose where to place it. For example, put Status in Rows and Project in Values to count projects by status. Excel may default the Values field to a count when the source is text; check the field summary and labels. Select a PivotTable and use PivotTable Analyze > PivotChart to create a chart linked to that summary. When source records change, refresh the PivotTable using Data > Refresh All or the refresh command for the PivotTable. Refreshing is a common step people overlook.
Choose charts and filters thoughtfully
A bar or column chart is often useful for comparing project counts by status, owner, or department. A stacked bar can show the distribution of statuses across teams, provided there are not so many categories that the chart becomes crowded. A line chart makes sense for a measure recorded over time, such as completed milestones each week. A chart of current completion percentages can work for a short list, but a sorted table with conditional formatting may be easier to read when there are many projects.
Pie and doughnut charts are tempting for status summaries, but they become difficult to compare when categories are numerous or similar in size. A simple bar chart generally makes the differences clearer. Always use a descriptive title, show units where relevant, and avoid relying on color alone to communicate status. For example, label a series At risk rather than expecting every viewer to recognize a particular shade of red.
To make a PivotTable report interactive, select it and choose PivotTable Analyze > Insert Slicer, then select fields such as Owner or Status. A date field can use a timeline where that option is available. If a slicer should control more than one PivotTable, select the slicer and use the Slicer tab’s Report Connections command to connect compatible PivotTables. This depends on the PivotTables sharing an appropriate source or data model. For ordinary charts built from cells rather than PivotTables, use a filter in the source table or build a dedicated summary that responds to a selected value; a slicer does not automatically control every chart in a workbook.
Make it readable and maintainable
Give the page a clear visual hierarchy: make the most important numbers easy to find, use consistent number formats, and leave enough space between sections. A percentage should look like a percentage, dates should follow a consistent format, and currency values should identify the currency when readers could mistake it. Use conditional formatting sparingly to highlight meaningful exceptions, such as a due date that has passed, rather than decorating every cell.
Include a visible “data as of” date or update note if the source is not refreshed automatically. Define the reporting period, especially for measures that change over time. A dashboard showing current status without an update date can appear current even when its source has not been maintained for weeks. You can also add a brief note describing status definitions or the owner responsible for updates.
Protect the distinction between inputs and calculations. Use one sheet for the source table, another for calculations or PivotTables if needed, and the dashboard for presentation. If other people will update the workbook, explain which cells they should edit and avoid hard-coding the same status list or assumption in multiple places. A short data dictionary or instructions sheet often prevents more errors than additional formatting.
Common problems and how to prevent them
- Counts seem wrong: look for duplicate projects, blank names, inconsistent status spelling, and rows outside the source table. A table expands when users add data directly below it, but pasted data can still land elsewhere.
- Dates do not sort correctly: some entries may be text rather than Excel date values. Check the source and convert or re-enter invalid values; changing the display format alone does not convert text into dates.
- A chart misses new projects: check the chart’s source range. A chart based on a fixed cell range may not include later rows; use a Table-based source or a PivotChart and refresh it.
- Numbers look current but are stale: PivotTables need refreshing after source changes. If the workbook uses external data connections, also confirm that the connection has successfully updated.
- Completion looks healthy while deadlines slip: progress and schedule are different measures. Include due dates or milestones and define an overdue rule rather than treating percentage complete as a schedule forecast.
Be careful with a common misconception: Excel does not automatically know whether a project is late or at risk. A status label is only as current and meaningful as the information and rules behind it. If a dashboard calculates overdue items, decide how to handle completed projects, missing due dates, and the reporting date before presenting the result.
Excel dashboards versus dedicated project tools
An Excel dashboard is a reasonable choice for a small portfolio, a recurring report, or a team that already maintains a well-structured spreadsheet. It is easy to tailor, can combine information from different sources, and makes calculations visible to people who know the workbook. Excel is also useful for prototyping a reporting approach before committing to a formal system.
A dedicated project-management platform may be preferable when people need shared task ownership, dependencies, notifications, permissions, time tracking, or a continuously updated view without manual file circulation. Power BI can be a better fit for larger or frequently refreshed reporting across multiple sources, although it requires its own setup and access arrangements. These options are not automatic upgrades: compare collaboration, data governance, licensing, refresh needs, and the skills available to maintain them. An Excel workbook can also become brittle if it grows into an undocumented collection of formulas and manual steps.
Frequently Asked Questions
Can Excel make a project dashboard?
Yes. Excel Tables, formulas, PivotTables, charts, and slicers can summarize and filter project information. The workbook still needs accurate source data and a refresh process for any PivotTables or connections.
What should a project dashboard include?
Common elements are project count, status, owner, due date, progress, and a clearly defined risk or budget measure. Include only measures that help the intended readers make a decision or identify work needing attention.
How do I make an Excel dashboard update automatically?
Use an Excel Table as the source so it can expand as records are added, and base formulas or summaries on that table. PivotTables generally need a refresh after changes; external data connections have their own refresh settings and may require access to the source.
Can I filter an Excel project dashboard by owner?
Yes. Add an Owner slicer to a PivotTable report, or filter the underlying Excel Table. A slicer controls connected PivotTables, but it will not necessarily filter unrelated charts or formula summaries unless those are designed to respond to the same selection.
Is Excel enough for project management?
It can be enough for simple tracking and reporting, but a dashboard is not a full project-management system. Teams that need simultaneous task updates, dependencies, workflow automation, or detailed permissions may be better served by a dedicated platform.
Related
- Excel Heat Maps: How to Create, Format, and Read Them
- Excel Group Rows: How to Collapse and Expand Data
- Excel Named Range: What It Is and How to Use It
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Radar Chart Excel Template: Build and Format a Chart in ExcelA radar chart Excel template is a worksheet that arranges categories and comparable measurements so you can create…
- Excel Trendline Is Wrong? How to Check and Fix ItAn Excel trendline can look wrong even when Excel has calculated it correctly: the chart may be using…
- Pivot Chart Line Graphs in Excel: How They WorkYes. In Excel, a PivotChart line graph displays values from a PivotTable as a line chart, making it…
- Does Google Sheets Have Pivot Tables? How to Use ThemYes. Google Sheets has pivot tables, which let you summarize and reorganize rows of data without changing the…
- Excel Gantt Chart With a Today LineYes. For a Gantt chart made from worksheet cells, apply conditional formatting to the date grid so the…
- How to Create a Trendline in ExcelTo create a trendline in Excel, select a chart, then choose Chart Design > Add Chart Element >…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.