Maintenance KPI Tracking in Excel: A Step-by-Step Setup Guide

Quick answer

You can set up a maintenance KPI tracker in Excel with four tabs: a breakdown log, a PM log, a KPI summary and a monthly dashboard. Teams enter data only in the two logs.

The summary tab then uses SUMIFS, COUNTIFS and IFERROR to work out MTBF, MTTR and PM completion for each machine, every month.

Key takeaways
  • Keep raw data and results apart. People type into the logs. Formulas do the rest.
  • Format each log as an Excel Table. New rows then flow into every formula on their own.
  • Use actual run hours for MTBF. Calendar hours make machines look better than they are.
  • Work out plant figures from totals. Don't average the MTBF of each machine.
  • Dropdowns keep the data clean. Use them for asset IDs, failure causes and status.
  • The rules matter more than the file. Agree what counts as a breakdown before anyone starts typing.

Why scattered records don't work

In many plants, maintenance records live in five places. There's a breakdown register on the shop floor and a PM checklist in a file. Shift reports go out on WhatsApp, and a supervisor keeps notes in a diary.

Each record makes sense on its own. Together, they can't answer simple questions. Which machine failed most last month? How many PM tasks slipped? Is repair time going up or down?

Good maintenance KPI tracking in Excel starts with one place for all of it. You don't need software for that. A well-built Excel file does the job for many plants.

This guide shows you how to build one, tab by tab. It includes the columns, the formulas and the rules that keep the data honest.

1. Plan your maintenance KPI tracking in Excel workbook

Set up one workbook with four main tabs, plus a small Lists tab. Two main tabs are for typing. Two are for reading.

Tab 1 · Input
Breakdown Log
One row for every unplanned stop.
Tab 2 · Input
PM Log
One row for every scheduled PM task.
Tab 3 · Formulas
KPI Summary
MTBF, MTTR and PM completion for each machine.
Tab 4 · Read only
Monthly Dashboard
The few numbers and charts managers look at.
Support tab
Lists
Asset IDs, failure causes and names for the dropdowns.

The Lists tab holds your asset IDs, machine names, failure causes and technician names. The dropdowns in Section 6 read from it.

Now turn each log into an Excel Table. Click inside the data and press Ctrl + T. Then name the tables BreakdownLog and PMLog under Table Design.

This one step saves a lot of trouble. When someone adds a new row, every formula and chart picks it up. Nobody has to stretch a range by hand.

2. Build the breakdown log

Every unplanned stop gets one row. Not one row per shift, and not one row per day. One stop, one row.

Use these columns. Here's a sample entry:

ColumnSample entryHow it's filled
Date14-10-2026Formula
ShiftBDropdown
Asset IDVMC-02Dropdown
Machine nameVertical machining centre 2Looked up from the Asset ID
Failure start14-10-2026 10:30Typed (date and time)
Restored at14-10-2026 14:45Typed (date and time)
Downtime hours4.25Formula
Waiting for spares (hours)2.5Typed
Failure causeElectricalDropdown
Action takenReplaced spindle drive cardTyped
TechnicianS. NayakDropdown

Three columns are formulas, so nobody types them.

Date is always the date the breakdown started. The monthly figures use this column, so a stop that runs past midnight or month-end still counts in the right month. Take it from the start time instead of typing it:

=INT([@[Failure start]])

Downtime hours subtracts the start from the restore time. Excel stores time in days, so multiply by 24. The IF part keeps the cell blank until the machine is restored:

=IF([@[Restored at]]="","",([@[Restored at]]-[@[Failure start]])*24)

Machine name is pulled from the Lists tab. That way it's spelt the same way every time:

=IFERROR(VLOOKUP([@[Asset ID]],Lists!A:B,2,FALSE),"Check asset ID")

Why a separate column for waiting for spares? Downtime hours shows the full time to restore, from stop to restart. That's how we measure MTTR. Some plants leave spares waiting out of MTTR. With this column, you can see both figures and know which part is slow.

3. Build the PM log

The PM log has one row for every scheduled task. Add the rows at the start of each month, straight from your preventive maintenance schedule.

Use these columns:

  • Asset ID (dropdown)
  • Task, for example "Check hydraulic oil level and filter"
  • Scheduled date
  • Completed date (left blank until the task is done)
  • Status (formula)
  • Technician (dropdown)
  • Reason for delay (filled only if the task was late)

Let a formula set the status. Then nobody has to remember to change it:

=IF([@[Completed date]]="", IF(TODAY()>[@[Scheduled date]],"Overdue","Open"), IF([@[Completed date]]<=[@[Scheduled date]],"Done on time","Done late"))

This shows four results: Open, Overdue, Done on time and Done late.

A late task still counts as done. It just doesn't count as on time. If your plant allows a grace period, say two days, change the last check to [@[Scheduled date]]+2.

4. Calculate monthly figures with SUMIFS, COUNTIFS and IFERROR

This is the KPI Summary tab. It has one row per machine. Nobody types here except for one input column.

Put the month's first date in cell B1, for example 01-10-2026. In B2, enter =EOMONTH(B1,0) to get the last date. Change B1 and the whole tab updates for that month.

List your asset IDs in column A, starting at row 5. Then add these columns.

Operating hours (the only input)

Enter the hours each machine actually ran that month. Take them from production records or hour meters.

Don't use calendar hours. A machine that runs one shift isn't running 720 hours a month. Calendar hours would make its MTBF look far better than it is.

Breakdowns and downtime

Breakdowns counts the stops for that machine in that month:

=COUNTIFS(BreakdownLog[Asset ID],$A5, BreakdownLog[Date],">="&$B$1, BreakdownLog[Date],"<="&$B$2)

Downtime hours adds up the stop time with SUMIFS, using the same conditions:

=SUMIFS(BreakdownLog[Downtime hours], BreakdownLog[Asset ID],$A5, BreakdownLog[Date],">="&$B$1, BreakdownLog[Date],"<="&$B$2)

Copy the same formula for spares waiting hours. Just swap the first part for BreakdownLog[Waiting for spares (hours)].

MTBF and MTTR

Say operating hours are in column B, breakdowns in C, downtime in D and spares waiting hours in E.

MTBF divides run hours by breakdowns. IFERROR stops a #DIV/0! error when a machine had no breakdowns:

=IFERROR(B5/C5,"No failures")

Avg downtime per breakdown (MTTR) divides downtime by breakdowns. In this tracker, MTTR is the full time to restore, from stop to restart. It includes finding the fault, waiting for spares and the repair itself:

=IFERROR(D5/C5,"-")

Waiting for spares can be a big share of that time. So add one more column, Repair time per breakdown (excl. spares). Say spares waiting hours are in column E:

=IFERROR((D5-E5)/C5,"-")

Label both columns clearly, so nobody mixes them up. A high MTTR with low repair time points to a spares problem, not a skills problem.

Want to know what each number means and when to worry? Our guide to 10 maintenance KPIs for manufacturing plants covers that.

PM completion

PM scheduled counts the tasks due that month:

=COUNTIFS(PMLog[Asset ID],$A5, PMLog[Scheduled date],">="&$B$1, PMLog[Scheduled date],"<="&$B$2)

PM on time adds one more condition, the status:

=COUNTIFS(PMLog[Asset ID],$A5, PMLog[Scheduled date],">="&$B$1, PMLog[Scheduled date],"<="&$B$2, PMLog[Status],"Done on time")

PM on-time % divides the two. With MTBF in F, MTTR in G and repair time in H, say PM scheduled is in column I and PM on time in column J:

=IFERROR(J5/I5,"No PM due")

Format the cell as a percentage. IFERROR shows "No PM due" in months with no tasks, instead of an error.

Worked example: plant totals, not averages

Add a total row at the bottom. Here's where many trackers go wrong.

Asset IDRun hoursBreakdownsDowntime (h)MTBF (h)Avg downtime per breakdown / MTTR (h)
CMP-01450134503.0
VMC-02450520904.0
Average of the two MTBFs–––270–
Plant total9006231503.8

If you average the two MTBF figures, you get 270 hours. That's not true. Together, the two machines ran 900 hours and had 6 breakdowns. So the aggregate MTBF across the tracked machines is 900 ÷ 6 = 150 hours.

Note that this uses total machine run hours, not plant calendar time. Two machines running 450 hours each add up to 900 machine-hours.

So in the total row, add up the hours and the breakdowns first. Then divide. Do the same for MTTR (23 ÷ 6 = 3.8 hours), repair time and PM on-time %.

5. Build a simple monthly dashboard

The dashboard is for the monthly review. Keep it to one screen. If people have to scroll, they stop reading.

Put four headline numbers across the top:

Breakdowns
Total row of the Breakdowns column
Downtime hours
Total row of the Downtime column
PM on time
Plant PM on-time % from the total row
Currently overdue PM
Open tasks past their due date, as of today

The overdue count reads straight from the PM log. It shows every task overdue right now, not just tasks from the selected month. That's deliberate. An old overdue task still needs doing:

=COUNTIFS(PMLog[Status],"Overdue")

Below the numbers, add three charts:

  • MTBF by machine, as a bar chart sorted from lowest to highest. Your worst machines show up first.
  • MTTR by machine, as a bar chart. It shows which machines take longest to get running again.
  • Downtime hours by machine, as a column chart. It shows where the lost hours went.

This month vs last month

One month on its own tells you little. Add a small table that puts it next to the month before.

In the KPI Summary tab, put =EDATE(B1,-1) in C1 for last month's first date. Put =EOMONTH(C1,0) in C2 for its last date.

Then count each month with the same formulas, minus the Asset ID condition. Last month's breakdowns, for example:

=COUNTIFS(BreakdownLog[Date],">="&$C$1, BreakdownLog[Date],"<="&$C$2)

Use SUMIFS the same way for downtime. Your table will look like this (sample figures):

MeasureLast monthThis monthChange
Breakdowns86−2
Downtime hours3123−8

The Change column is just this month minus last month. A minus sign is good news here.

Then use conditional formatting on the KPI Summary tab. Go to Home → Conditional Formatting → Highlight Cells Rules.

Turn PM on-time % cells red when they fall below your plant's target. Turn MTTR cells amber when they go above it. Set the targets from your own past months, not from a number you read somewhere.

6. Set data entry rules everyone follows

A tracker is only as good as what goes into it. These rules matter more than any formula.

Agree the definitions first

Write these down on a single page and share it with every shift:

  • What counts as a breakdown. For example, any unplanned stop longer than 10 minutes.
  • When the clock starts and when it stops.
  • What "on time" means for a PM task.

If two shifts use different rules, the numbers won't match. And nobody will trust them.

Give each person one job

The shift in-charge logs every breakdown before handover. The technician closes PM tasks the same day. One person, often the maintenance planner, owns the KPI Summary and checks it every week.

Make logging part of the shift handover. A stop that isn't logged by the end of the shift is often never logged.

Use dropdowns, not free text

Select the Asset ID column. Go to Data → Data Validation → List and point it to the asset list on the Lists tab. Do the same for shift, failure cause, technician and PM task.

Keep the failure causes short and fixed. For example: Mechanical, Electrical, Hydraulic, Pneumatic, Lubrication, Tooling, Operator error and Other.

If "Other" keeps growing, read those rows. You probably need a new cause on the list.

Lock the formula cells

One wrong keystroke can wipe out a formula. Unlock only the input cells, then protect the sheet with a password. Microsoft explains how to lock specific areas of a worksheet.

7. Fix common tracker problems

Most trackers don't fail because of bad formulas. They fail because of bad entries. Here's what to look for.

ProblemWhat it doesFix
Same stop logged twiceDoubles the breakdown count when a stop runs across two shiftsThe shift where it started owns the row. The next shift adds the restore time.
Missing restore timeCounts the stop but leaves out its hours, so downtime looks lowerUse conditional formatting to flag blank restore times older than one day.
Different names for one machineSplits one machine's breakdowns across two rowsUse the Asset ID dropdown and look up the name.
Blank or text datesLets SUMIFS and COUNTIFS skip the row without warningSet data validation on date columns to accept dates only.
Formulas miss new rowsLeaves the KPIs looking better than realityKeep the logs as Excel Tables and use table names in formulas.
Breakdowns logged as PM workMakes MTBF look healthy while the floor sees stops every dayUnplanned repairs always go in the breakdown log. Check each PM row matches a task on the schedule.

Spend ten minutes a week on these checks. If the numbers and the shop floor still don't agree, it may be time for a closer look. These signs your plant needs a maintenance audit can help you decide.

8. When to move beyond spreadsheets

Excel works well for many plants. But it has limits, and there's no fixed point where you outgrow it. Watch for these signs:

  • The file gets slow, or people keep saving over each other's changes.
  • You spend more time fixing the file than reading it.
  • You need work orders, spare stock and history linked in one place.
  • Technicians need to log work on a phone, right at the machine.
  • You want automatic alerts when a PM task is due.

When that happens, a maintenance management system (CMMS) is the usual next step. Keep your Excel file even then. The columns and rules you built here move across easily.

Some plants also start using sensor data at this stage. It helps to know whether preventive or predictive maintenance suits each machine.

Frequently asked questions

Can maintenance KPIs be tracked in Excel?

Yes. Excel works well for many plants. Keep a breakdown log and a PM log, then use SUMIFS, COUNTIFS and IFERROR to work out MTBF, MTTR and PM completion for each machine.

What should a maintenance KPI tracker include?

At minimum, it needs a breakdown log, a PM log, a KPI summary and a dashboard. The logs should record the asset ID, failure start and restore times, waiting time for spares, failure cause and PM status.

How often should the tracker be updated?

Log breakdowns before every shift handover and close PM tasks on the day they're done. Then review the KPI summary every week and the dashboard every month.

Can several shifts use the same tracker?

Yes, if everyone follows the same definitions and uses dropdowns instead of free text. If people keep saving over each other's work, move the file to Excel Online or Google Sheets so they can edit together.

Do I need macros to build a maintenance KPI tracker?

No. Excel Tables, SUMIFS, COUNTIFS, IFERROR, data validation and conditional formatting cover everything in this guide. Without macros, the file is also easier to share and keep up.

Same machines topping your tracker every month?

A tracker shows you where the problems are. Fixing them takes the right plan on the floor. SGK India works with manufacturing plants to plan and carry out equipment maintenance, so repeat breakdowns come down over time.

Plant Equipment Maintenance Services
Facebook
Twitter
Email
Print

Leave a Reply

Your email address will not be published. Required fields are marked *

Latest Post

SGK India Engineering Private Limited Company Logo

Get the Right Solution for Your Plant

Tell us about your requirement and our team will get back to you shortly.