Four Numbers That Run a Plant: A Maintenance Dashboard in Excel

Walk into most small maintenance shops and ask "how are we doing?" and you'll get a story, not a number. The supervisor remembers the big fire from Tuesday, the part that's been on backorder, the PM that keeps slipping. It's all true, and it's all in one person's head. The day that person is off, nobody can answer the question.

A dashboard fixes that — not the kind that takes a six-month software rollout, but a single screen that reads from the work you're already logging. You don't need a BI platform. You need four numbers, refreshed automatically, sitting at the top of the same spreadsheet where your work orders, PMs, and parts already live.

Why four, and which four

The temptation with dashboards is to add everything — twenty tiles, a dozen charts, a heat map nobody reads. Resist it. A dashboard you check every morning has to answer the questions a maintenance lead actually asks before the shift starts:

• Open work orders — what's outstanding? The size of the backlog, at a glance.
• PMs due — what's scheduled that I can't let slip? Planned work that turns into a breakdown if ignored.
• Low-stock parts — what am I about to run out of? The reorder list before it becomes a stockout.
• PM compliance — are we actually keeping up? The percentage of preventive work done on time.

Those four cover the present (backlog), the near future (PMs due, parts), and the trend (compliance). Everything else is detail you can drill into when one of the four looks wrong.

Each number is a formula, not a tally

The whole point is that nobody updates the dashboard by hand. Each tile is a single formula reading the sheet you already maintain:

• Open WOs = count of rows in the work order database where status isn't "Done." One COUNTIF.
• PMs Due = count of PM rows where the next-due date is on or before today. COUNTIFS against TODAY().
• Low Stock = count of parts where on-hand is at or below the reorder point. COUNTIF comparing two columns.
• PM Compliance = PMs completed on time ÷ PMs that came due, over a rolling window. A ratio of two COUNTIFS.

Because they're formulas, the dashboard is never stale. Close a work order and the open count drops. Receive a part and it falls off the low-stock list. The numbers move because the work moved — not because someone remembered to update a slide.

Read the four together, not alone

The numbers are most useful in combination, and that's where the judgment comes in:

• Backlog climbing while PM compliance falls is the classic death spiral — you're so busy reacting to breakdowns that you skip the preventive work that would stop them, which causes more breakdowns. Catch it early and you can staff or prioritize your way out. Catch it late and you're firefighting for a quarter.
• Low stock spiking right before a big PM week means you're about to start jobs you can't finish. Order now, not when the technician opens an empty bin.
• PM compliance at 100% with a growing backlog sometimes means the PMs are too easy and the real work is hiding in corrective orders. Worth a look at what's actually in the backlog.

A single number can lie. The four together tell the truth.

Keep it honest

A dashboard is only as good as the data feeding it, so two rules keep it trustworthy:

1. One source per number. PM compliance reads from the PM master, open WOs from the work order log, low stock from the parts list. If a number can be calculated two ways, it will eventually disagree with itself — pick one source and point the formula there.
2. No manual overrides. The second someone types a number over a formula "just for today," the dashboard stops being trustworthy. If a tile looks wrong, fix the underlying data, not the tile.

Where a template helps

You can build all four tiles yourself — a summary sheet at the front, a handful of COUNTIF and COUNTIFS formulas pointed at your work order, PM, and parts sheets, and a clean layout so it reads at a glance. If you'd rather not wire up the formulas, the dashboard in our CMMS Lite set is exactly this: four live tiles reading from the work order database, PM master, and parts list, with the reorder and compliance logic already built.

Either way, the goal is the same. Turn "how are we doing?" from a story only one person can tell into four numbers anyone can read in five seconds — and refresh them off the work you're already doing, not a second job nobody has time for.

Want a dashboard that reads from the sheets you already keep? Get in touch — we'll help you set it up.