An Excel project dashboard template is a workbook that turns a structured project list into a quick view of status, deadlines, progress, and budget. In Microsoft Excel for Microsoft 365 on Windows, start with a Projects sheet for the source data and a Dashboard sheet for summary metrics, charts, and deadline warnings; the example below includes a usable layout and formulas you can adapt.
What a project dashboard should show
A dashboard is a summary, not a replacement for the project plan. Its purpose is to help a reader answer a few recurring questions quickly: How many projects are being tracked? Which are late or at risk? How far along is the work? Are costs or deadlines moving away from plan? Keep the first screen focused on information that supports a decision.
For a small portfolio, useful elements usually include:
- Portfolio totals: total projects, projects in progress, and projects past their due date.
- Progress: a portfolio average or a chart showing progress by project.
- Schedule: due dates, days remaining, or a simple timeline.
- Cost: planned budget, actual cost, and the difference between them, if cost tracking is relevant.
- Project details: owner, status, and a short note about a blocker or next action.
Do not add every available measure simply because Excel can display it. A dashboard crowded with small charts and decorative indicators can make the important exceptions harder to see. Agree on what “late,” “active,” and “complete” mean before building calculations around those terms.
Set up the workbook source data
Create a worksheet named Projects and put one project on each row, with column headings in row 1. A practical starter layout is:
- A: Project
- B: Owner
- C: Status
- D: Start date
- E: Due date
- F: Progress
- G: Budget
- H: Actual cost
- I: Days left (calculated)
Enter dates as real Excel dates, not text that only looks like a date. Format columns D and E as dates, F as Percentage, and G:H as currency or another consistent number format. Use one progress convention throughout: enter 0% to 100%, rather than mixing values such as 0.6 and 60. Status labels also need to be consistent; a list such as Planned, In progress, On hold, and Complete is easier to count than variations like “In progress,” “In-progress,” and “Active.”
For the example, suppose four rows contain Website refresh (In progress, 60%, budget 12,000, actual 7,000, due November 20, 2026); CRM migration (In progress, 75%, 20,000, 15,000, due October 30, 2026); Training rollout (Planned, 0%, 5,000, 0, due December 15, 2026); and Brand update (Complete, 100%, 8,000, 7,600, due October 20, 2026). The dates make the example’s overdue result specific to November 3, 2026; in a live workbook, TODAY() changes the result as time passes.
Consider converting the range into an Excel Table: select a cell in the data and choose Insert > Table, then confirm that the table has headers. A table provides filters and expands when rows are added directly beneath it. Give it a clear name from Table Design, such as ProjectsTable. The formulas below use a fixed range through row 101 for readability; extend that range when needed, or use table references consistently if the list will grow.
Build the dashboard summary formulas
Add a worksheet named Dashboard. Put metric labels in column A and results in column B, for example: B2 total projects, B3 in progress, B4 overdue, B5 average completion, and B6 budget remaining. These formulas refer to the Projects sheet and leave room for up to 100 projects in rows 2 through 101.
With project names in Projects!A2:A101, count populated project rows in Dashboard!B2 using =COUNTA(Projects!A2:A101). For the four example rows, the result is 4. This counts nonblank project names, so avoid entering notes or other text in the project-name range below the list.
With statuses in Projects!C2:C101, count projects whose status is exactly In progress in Dashboard!B3 using =COUNTIF(Projects!C2:C101,"In progress"). The example returns 2. If your organization calls this status Active instead, use that exact label in both the data validation list and formula.
With due dates in Projects!E2:E101 and statuses in Projects!C2:C101, count overdue projects that are not complete in Dashboard!B4 using =COUNTIFS(Projects!E2:E101,"<"&TODAY(),Projects!C2:C101,"<>Complete",Projects!E2:E101,"<>"). On November 3, 2026, the example returns 1: CRM migration is past its due date and not marked complete. The formula excludes blank due dates. If you want overdue items to include projects on hold, this formula does so; change the criteria only if your reporting definition differs.
With completion percentages in Projects!F2:F101, calculate the mean in Dashboard!B5 with =IFERROR(AVERAGE(Projects!F2:F101),0). The example returns 58.75%, the arithmetic mean of 60%, 75%, 0%, and 100%. A simple average gives each project equal weight. If a large project should count more than a small one, define a defensible weighting measure, such as estimated work, and calculate a weighted result instead of presenting this mean as effort-weighted progress.
With planned budgets in Projects!G2:G101 and actual costs in Projects!H2:H101, calculate budget remaining in Dashboard!B6 with =SUM(Projects!G2:G101)-SUM(Projects!H2:H101). The example returns 15,400: total budget is 45,000 and recorded actual cost is 29,600. A positive difference means recorded costs are below the total planned budget; it does not prove that the project is on budget, because future commitments, timing, and forecast costs are not included.
Add a days-left calculation
A days-left column makes upcoming deadlines easier to scan. With the due date in E2 and status in C2 on the Projects sheet, enter this formula in I2 and fill it down: =IF(OR(E2="",C2="Complete"),"",E2-TODAY()). It returns a blank for a missing due date or a completed project; otherwise it returns the number of calendar days until the due date. For CRM migration, dated October 30, 2026, the result is -4 on November 3, 2026. A negative number indicates a past due date, not necessarily a confirmed delay if the schedule has not been updated.
To fill the formula down, select I2 and drag the fill handle at the lower-right corner of the cell, or copy I2 and paste into the remaining rows. Keep the column formatted as General or Number, not Date. TODAY() is a volatile function: the displayed result can update when Excel recalculates the workbook, so it is useful for a live dashboard but not a fixed historical record. For a dated report, save a dated copy or record the reporting date separately.
Make deadlines and status visible
Conditional formatting can highlight overdue work without requiring a separate chart. Select the project rows, for example A2:I101, then choose Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format. Use =AND($E2<TODAY(),$C2<>"Complete",$E2<>"") as the rule and choose a restrained fill color. The dollar signs lock the date and status columns while allowing the row number to adjust. This highlights rows with a past due date, a non-complete status, and a nonblank due date.
Use Home > Conditional Formatting > Data Bars on the progress column to show completion visually, while retaining the percentage values. Color should not be the only signal: preserve the written status and make sure text remains readable for people who cannot distinguish the chosen colors. Avoid applying several competing rules to the same cells without checking their order in Home > Conditional Formatting > Manage Rules.
To reduce inconsistent entries, select the status cells and choose Data > Data Validation. In the dialog, set Allow to List and provide the approved status values as the source, or point the source to a range containing those values. This creates a drop-down list. Excel for the web and Excel for Mac have related validation features, but dialog details and available controls can differ from the Windows desktop steps; check the options shown in your version.
Choose charts that answer a question
A chart is useful when it makes a comparison easier than reading a table. For example, select project names and progress values, then choose Insert > Insert Column or Bar Chart and use a bar chart to compare completion across projects. Keep the scale consistent from 0% to 100%; a truncated axis can exaggerate small differences. A horizontal bar chart can fit long project names better than a column chart.
A doughnut chart showing counts by status may help with a compact portfolio, but it is less useful when there are many status categories or very small slices. A small summary table can be clearer. Avoid a pie chart for progress by project: each project’s progress is not a part of one shared whole, so those percentages do not form meaningful slices. If budget is important, a simple table with budget, actual cost, and variance is often more useful than a decorative gauge.
Arrange the Dashboard sheet so key numbers appear near the top, followed by one or two charts and an exception list of overdue or at-risk work. Freeze panes only if the dashboard itself is long; freeze the header row on the Projects sheet if scrolling makes column headings disappear. Use descriptive chart titles, readable labels, and enough spacing to keep the sheet usable on a normal laptop screen and when printed.
Keep the template accurate as work changes
A template is only as reliable as its inputs and definitions. Assign someone to update each project’s progress, due date, status, and actual cost on a regular cadence. Decide whether progress is a manager estimate, a completed-milestone calculation, or a measure from another system; do not mix methods silently. Similarly, define whether “actual cost” means invoices paid, costs incurred, or another accounting measure.
- Check that every project has a unique, recognizable name and an owner.
- Review blank due dates and progress values; blanks can make averages or overdue counts misleading.
- Confirm that completed work has a Complete status and a sensible final progress value.
- Reconcile actual costs to the source used by the organization before using the dashboard for decisions.
- Test formulas after adding rows and verify that the displayed totals match the source list.
- Keep a dated snapshot when reporting historical status, since formulas using TODAY() recalculate.
If multiple people edit the workbook, agree who owns the source data and avoid making separate copies the authoritative record. Protecting a sheet can reduce accidental edits to formulas, but protection is not a substitute for access controls or a secure storage location. Avoid putting confidential project details into a workbook shared more broadly than necessary.
Common limitations and alternatives
Excel is a practical choice for a small or moderately sized portfolio when the data is maintained by a team that already works with spreadsheets. It offers flexible layouts, familiar formulas, and charts without requiring a dedicated project-management platform. A workbook can become harder to govern when many teams edit it simultaneously, when dependencies and resource capacity need to be modeled, or when information must synchronize automatically with other systems.
For a larger portfolio, consider whether a project-management service, a database-backed reporting tool, or a business intelligence platform better fits the need. Those options may offer permissions, workflows, and automated data refresh, but they require setup, administration, and potentially paid licenses. A downloaded Excel template can save layout time, yet inspect its formulas, macros, external links, and assumptions before using it. A template that looks polished may still define progress or overdue work differently from your organization.
For Excel for Microsoft 365 on Windows, the ribbon paths above are the standard desktop routes. Excel for Mac generally offers the same core worksheet concepts, but some ribbon labels and dialog layouts vary; Excel for the web may also expose fewer controls than the desktop app. If a command is not shown, use the Excel Search box to locate the feature available in that version rather than assuming every interface is identical.
Frequently Asked Questions
Does Excel have a project dashboard template?
Excel may offer downloadable templates through its template gallery, and third-party templates are also available. Their contents vary, so inspect the data layout, formulas, and any macros before entering important information. You can also build a basic dashboard from a project table and summary formulas like those above.
How do I make a project dashboard in Excel?
Create a source-data sheet with one project per row, standardize status and progress entries, then add a dashboard sheet with counts, averages, budget measures, and charts. Check each result against the underlying rows, and decide how the team defines terms such as overdue and complete.
Can Excel automatically show overdue projects?
Yes. A formula using TODAY() can compare each due date with the current date, and conditional formatting can highlight rows that meet an overdue rule. The result updates as Excel recalculates, so preserve a dated snapshot if you need to retain what the dashboard showed on a past reporting date.
What is the best chart for project progress?
A bar chart is often clear for comparing completion percentages across a modest number of projects, particularly when names are long. Keep the scale at 0% to 100% and label the chart. For a very short list, a formatted table with data bars may be easier to read.
Can I use this dashboard for multiple projects?
Yes. The example formulas cover rows 2 through 101, or 100 projects, but that is a chosen range rather than an Excel limit. Extend the references as the list grows, or use an Excel Table and consistently designed table formulas so new records are included in reporting.
Related
- Excel Power Query Connection Only Explained
- Stampin’ Up Inventory Spreadsheet: What to Track
- Spreadsheet Free Budget Planner: A Simple Excel Setup
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Pivot Power Skating: How to Turn, Train, and ImprovePivot power skating is the practice of changing from forward to backward skating, or backward to forward, while…
- Excel Line Chart Data Range: How to Set ItIn Excel for Microsoft 365 on Windows, select the cells you want to chart—including the category labels and…
- Excel Pivot Table: How to Remove Grand Totals and SubtotalsTo remove grand totals from an Excel PivotTable in Microsoft 365 for Windows, click inside the PivotTable, open…
- Insert a Combo Chart in Excel: Steps, Axes, and TipsTo insert a combo chart in Excel for Microsoft 365 on Windows, select your data, then choose Insert…
- Pivot Charter School in Riverview, FL: What to KnowYes. Pivot Charter School, also known as Pivot Charter School of Tampa Bay, is a tuition-free public charter…
- Pareto Chart in Excel: Use Word Labels and CountsYes—an Excel Pareto chart can use words such as “Late delivery” or “Damaged item” as category labels, with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.