How to Build a Public Works Work Order Dashboard in Excel
If you read our guide to the 10 most important KPIs for public works departments, you already know what to measure. This article is the implementation guide — how to take a standard work order system export and build a dashboard in Excel that makes those metrics visible to the people who need to act on them.
No PowerBI license required. No IT department involvement. No enterprise software budget. Just Excel, a structured data export, and the step-by-step process this article walks you through.
Why Excel Is the Right Starting Point
Before building anything, a word on tool choice. Public works departments have access to a range of work order management platforms — from enterprise systems like IBM Maximo and Cityworks to simpler platforms like Oxmaint and Novo Solutions. Most of them have reporting modules built in.
So why build a dashboard in Excel?
Three reasons that matter in practice. First, most mid-size government departments do not have staff who can customize their work order platform's reporting interface — but almost everyone can use Excel. Second, Excel dashboards can be shared with any colleague regardless of whether they have a license to the underlying work order system. Third, building the dashboard yourself in Excel means you understand every metric, every formula, and every data source — which makes you significantly more capable of explaining the numbers to leadership and council than a black-box report from a vendor platform.
While manual systems treat work orders as isolated repair tickets, digital work order management creates a connected lifecycle where every request generates actionable data. Each completed work order contributes to asset history, informs capital planning, and provides audit-proof documentation. An Excel dashboard built on that export data is your window into that lifecycle — without requiring your department to replace its existing systems. OXMaint
For departments that want a fully built version with professional formatting, formulas, and dummy data already loaded, the Local Government Dashboard Template Pack includes a ready-to-use Public Works Service Request Dashboard in Excel. This tutorial walks you through building your own version from scratch — the template pack is for departments that want to skip the build and get straight to the analysis.
Step 1: Structure Your Raw Data Tab
Every good Excel dashboard starts with clean, consistently structured raw data. Your work order system export should produce one row per work order. If it does not — if it produces summary reports or pre-aggregated totals — you need to find the raw export option in your system settings before proceeding. Dashboards built on pre-aggregated data cannot be filtered, sliced, or updated dynamically.
Your Raw Data tab should have the following columns. These map to the standard fields available in most government work order management systems:
Work Order ID — a unique identifier for each work order. This is the field your COUNTIF and COUNTIFS formulas will reference as the basis for all volume metrics.
Date Submitted — the date the work order was created or received. This enables date-range filtering and trend analysis over time.
Request Type — the category of work being requested. Examples: Pothole Repair, Street Light Outage, Water Main Break, Tree Trimming, Sidewalk Repair, Sewer Overflow, Sign Repair, Park Maintenance, Graffiti Removal, Bulk Waste Pickup.
Priority Level — the urgency classification assigned to the work order. Use four consistent levels: Emergency, Same Day, Standard, Routine. Every row must use one of these exact values — no variations or abbreviations — for your formulas to calculate correctly.
Service Location — the address, intersection, or geographic zone where the work is required. This field enables district-level analysis and geographic breakdown of request volume, which is one of the most useful views for public works directors managing resources across multiple areas.
Assigned Department — which crew or division the work order is assigned to. Examples: Water and Sewer, Streets and Roads, Parks and Recreation, Electrical Services, General Services.
Status — the current state of the work order. Use four consistent values: Open, In Progress, Completed, Closed — No Action Required.
Date Completed — the date the work order was resolved. Leave blank for Open and In Progress records. This field is required for calculating resolution time.
Resolution Time (Hours) — the number of hours from Date Submitted to Date Completed for resolved work orders. This can be calculated in Excel using a formula rather than exported from your system: =(Date Completed - Date Submitted) * 24
Within Target — a Yes, No, or Pending value indicating whether the completed work order was resolved within its priority-level target timeframe. Emergency: within 4 hours. Same Day: within 8 hours. Standard: within 40 hours (5 business days). Routine: within 120 hours (15 business days). This field is the foundation of your on-time performance metrics.
Resident Reported — a Yes or No field indicating whether the work order originated from a resident complaint or was identified proactively by department staff. Tracking this ratio over time reveals whether your department is becoming more proactive or more reactive in its maintenance approach.
Once your raw data is structured consistently across all rows, format it as an Excel Table — select your data range, go to Insert → Table, and check the box that says My table has headers. Named Tables make your formulas more readable and automatically expand when you add new rows.
Step 2: Build Your Summary Calculations Tab
Create a second tab called Calculations. This tab sits between your Raw Data and your Dashboard — it aggregates your row-level data into the summary metrics your dashboard will display.
The core calculations you need are:
Total Work Orders=COUNTA(RawData[Work Order ID])
Open Work Orders=COUNTIF(RawData[Status],"Open")+COUNTIF(RawData[Status],"In Progress")
Completed Work Orders=COUNTIF(RawData[Status],"Completed")+COUNTIF(RawData[Status],"Closed - No Action Required")
On-Time Completion Rate=COUNTIF(RawData[Within Target],"Yes")/(COUNTIF(RawData[Within Target],"Yes")+COUNTIF(RawData[Within Target],"No"))
Volume by Priority Level
For each priority level create a COUNTIF:=COUNTIF(RawData[Priority Level],"Emergency")
Repeat for Same Day, Standard, and Routine.
Volume by Request Type
For each request type in your list:=COUNTIF(RawData[Request Type],"Pothole Repair")
Repeat for each category.
Volume by District or Zone
If your Service Location field uses consistent district labels:=COUNTIF(RawData[Service Location],"District 1")
Repeat for each district.
Average Resolution Time by Priority=AVERAGEIF(RawData[Priority Level],"Emergency",RawData[Resolution Time (Hours)])
Repeat for each priority level.
Backlog Age Analysis
Count work orders that have been open longer than their target response time. For Standard priority work orders open more than 40 hours:=COUNTIFS(RawData[Priority Level],"Standard",RawData[Status],"Open",RawData[Resolution Time (Hours)],">40")
Keep all these calculations on the Calculations tab. Your Dashboard tab will reference these cells rather than pulling directly from the Raw Data — which keeps your dashboard formulas clean and makes troubleshooting significantly easier.
Step 3: Build Your Dashboard Tab
Create a third tab called Dashboard. This is the only tab your department head, director, or council member ever needs to see. Turn off gridlines — View → uncheck Gridlines — and set a consistent light gray background color across the entire tab to give it a clean, professional appearance.
The Dashboard Structure
Organize your dashboard into four sections from top to bottom:
Section 1 — KPI Cards (rows 1-8)
Four large KPI cards displaying your headline metrics: Total Work Orders, Open Work Orders, Completed Work Orders, and On-Time Rate. Each card should be a merged cell range showing a large bold number with a smaller label beneath it. Apply conditional formatting to the On-Time Rate card — green if above 80%, yellow if between 65-80%, red if below 65%.
Section 2 — Priority Performance Table (rows 10-18)
A formatted table showing each priority level with its total volume, open count, completed count, within-target count, exceeded-target count, and target timeframe. This is the section department supervisors use most actively — it tells them immediately if any priority level is underperforming.
Section 3 — Request Type Breakdown (rows 20-32)
A bar chart or formatted table showing work order volume by request type. Sort from highest to lowest volume. This section reveals which service categories are generating the most demand — critical intelligence for staffing and resource allocation decisions.
Section 4 — District or Zone Summary (rows 34-44)
A table or map showing work order volume and on-time performance broken down by service location or district. Citizen request density maps identifying problem hotspots and GIS integration that turns static reports into dynamic decision tools are increasingly standard in public works reporting. Your Excel version achieves the same analytical goal through a district summary table — identifying which areas are generating disproportionate demand or experiencing lower-than-average on-time performance. Novo Solutions
Step 4: Add a Date Filter
The most important usability feature in any work order dashboard is the ability to filter by time period. A department head reviewing the dashboard in October wants to see October's numbers — not the entire year's cumulative data.
Add a date filter using Excel's Data Validation dropdown:
Create a cell at the top of your Dashboard tab labeled Report Month. Add a Data Validation dropdown listing your available months — January 2026, February 2026, and so on.
Then modify your Calculations tab formulas to include a month filter. Using COUNTIFS instead of COUNTIF adds the date criterion:
=COUNTIFS(RawData[Status],"Open",TEXT(RawData[Date Submitted],"MMMM YYYY"),Dashboard!$B$2)
Where Dashboard!$B$2 references your Report Month dropdown cell.
This single addition transforms your dashboard from a cumulative total view into a monthly performance management tool — the format that department heads actually use to track operational health over time.
Step 5: Format for Non-Technical Audiences
Your dashboard will be reviewed by people who did not build it — supervisors, directors, elected officials, and residents if it becomes a public transparency tool. Municipal reports should include service request volume and resolution rates in formats that are accessible to non-technical stakeholders, with consistent status reporting that enables month-over-month comparison. Novo Solutions
Four formatting principles that make the difference between a dashboard people use and one they ignore:
Use color consistently and sparingly. Reserve red for metrics below target, green for metrics meeting or exceeding target, and amber for approaching threshold. Never use color decoratively — every color on the dashboard should carry meaning.
Make the headline numbers large. Your four KPI cards should display numbers in 28-36 point font. A supervisor opening the dashboard on a standard monitor should be able to read the total work order count and on-time rate without adjusting zoom or leaning toward the screen.
Label everything in plain language. No account codes, no system abbreviations, no technical jargon. "Pothole Repair" not "PWK-RD-001." "On-Time Completion Rate" not "WO SLA %." Your audience is non-technical by definition.
Add a last-updated date. A simple cell at the top of the dashboard reading "Data through: [date]" tells viewers exactly how current the information is — and signals that someone is actively maintaining it, which builds credibility.
Updating the Dashboard Monthly
The update process once your dashboard is built should take 10 minutes or less:
Export your work order data for the month from your system as a CSV or Excel file. Open your Raw Data tab. Delete all rows below the header row. Paste your new export data starting at row 2. Verify that all column headers match exactly. Check that the Status and Priority Level values match the exact text your formulas reference. Update the Report Month dropdown if you have added the date filter. Save the file.
Every formula, chart, and KPI card on your Dashboard tab updates automatically the moment you paste in the new data. That automation is the difference between a dashboard that gets used every month and one that gets built once and then abandoned when the update process proves too time-consuming.
From DIY Dashboard to Ready-Built Template
The process above — five steps, roughly four to six hours of build time — produces a fully functional work order dashboard tailored to your department's specific data structure and request categories.
For departments that want to skip the build entirely and start analyzing immediately, the Local Government Dashboard Template Pack at $47 includes a professionally built Public Works Service Request Dashboard with 290 rows of pre-loaded fictional Riverside City work order data, all formulas built and verified, conditional formatting applied, and a PDF setup guide that walks you through replacing the dummy data with your real system export in under 30 minutes.
Whether you build from scratch or start with the template, the goal is the same — a monthly dashboard that tells your public works director, at a glance, whether the department is meeting its targets, where the demand is concentrated, and which request types or districts need attention before the next council meeting.
Final Thoughts
A work order dashboard does not change how your department operates. It changes whether your department can see how it operates — and that visibility is what enables the operational improvements that actually matter.
Implementing a digital work order lifecycle allows municipalities to track every request from intake to resolution with timestamped accountability — reducing return trips by 35-50% and creating the audit trail that protects against liability claims. The Excel dashboard is your window into that data — the tool that turns raw work order exports into the operational intelligence your leadership needs to allocate resources, justify staffing requests, and report transparently to the residents your department serves. OXMaint
Start with your raw data export. Structure it consistently. Build the calculations tab. The dashboard follows from there.
Ready to skip the build and start analyzing?
The Local Government Dashboard Template Pack includes a fully built Public Works Service Request Dashboard — pre-loaded with fictional data, all formulas verified, and a step-by-step setup guide included. Replace the dummy data with your real export and your dashboard is live in under 30 minutes.
