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.
- 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
- 1. Plan your maintenance KPI tracking in Excel workbook
- 2. Build the breakdown log
- 3. Build the PM log
- 4. Calculate monthly figures with SUMIFS, COUNTIFS and IFERROR
- 5. Build a simple monthly dashboard
- 6. Set data entry rules everyone follows
- 7. Fix common tracker problems
- 8. When to move beyond spreadsheets
- Frequently asked questions
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.
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:
| Column | Sample entry | How it's filled |
|---|---|---|
| Date | 14-10-2026 | Formula |
| Shift | B | Dropdown |
| Asset ID | VMC-02 | Dropdown |
| Machine name | Vertical machining centre 2 | Looked up from the Asset ID |
| Failure start | 14-10-2026 10:30 | Typed (date and time) |
| Restored at | 14-10-2026 14:45 | Typed (date and time) |
| Downtime hours | 4.25 | Formula |
| Waiting for spares (hours) | 2.5 | Typed |
| Failure cause | Electrical | Dropdown |
| Action taken | Replaced spindle drive card | Typed |
| Technician | S. Nayak | Dropdown |
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:
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:
Machine name is pulled from the Lists tab. That way it's spelt the same way every time:
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:
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:
Downtime hours adds up the stop time with SUMIFS, using the same conditions:
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:
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:
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:
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:
PM on time adds one more condition, the status:
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:
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 ID | Run hours | Breakdowns | Downtime (h) | MTBF (h) | Avg downtime per breakdown / MTTR (h) |
|---|---|---|---|---|---|
| CMP-01 | 450 | 1 | 3 | 450 | 3.0 |
| VMC-02 | 450 | 5 | 20 | 90 | 4.0 |
| Average of the two MTBFs | – | – | – | – | |
| Plant total | 900 | 6 | 23 | 150 | 3.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:
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:
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:
Use SUMIFS the same way for downtime. Your table will look like this (sample figures):
| Measure | Last month | This month | Change |
|---|---|---|---|
| Breakdowns | 8 | 6 | −2 |
| Downtime hours | 31 | 23 | −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.
| Problem | What it does | Fix |
|---|---|---|
| Same stop logged twice | Doubles the breakdown count when a stop runs across two shifts | The shift where it started owns the row. The next shift adds the restore time. |
| Missing restore time | Counts the stop but leaves out its hours, so downtime looks lower | Use conditional formatting to flag blank restore times older than one day. |
| Different names for one machine | Splits one machine's breakdowns across two rows | Use the Asset ID dropdown and look up the name. |
| Blank or text dates | Lets SUMIFS and COUNTIFS skip the row without warning | Set data validation on date columns to accept dates only. |
| Formulas miss new rows | Leaves the KPIs looking better than reality | Keep the logs as Excel Tables and use table names in formulas. |
| Breakdowns logged as PM work | Makes MTBF look healthy while the floor sees stops every day | Unplanned 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