Expert guide
OEE Calculation in Excel for Manufacturing
Availability, performance and quality per shift, and the downtime reasons behind them
Call us at +1 (385) 386-3860
Short answer: log each shift per machine with planned production time, downtime minutes by reason, ideal cycle time, total count and good count. Then Availability = (planned − downtime) ÷ planned, Performance = ideal cycle × total count ÷ run time, Quality = good ÷ total, and OEE = A × P × Q. Aggregate by summing the inputs, not averaging percentages, and chart downtime reasons as a Pareto. For shop-floor data entry and history, see our manufacturing database in Access.
OEE is the most widely used measure of how well a machine or line is used, and one of the most often miscalculated. The formula is simple; the mistakes come from inconsistent definitions, averaging percentages across shifts, and downtime that is recorded as a total with no reason. A structured Excel log fixes all three and turns OEE from a number on a wall into a list of problems to solve.
The Production Log
| Column | Example | Notes |
|---|---|---|
| Date, Shift, Line/Machine | 2026-11-03, B, Press 4 | One row per machine per shift |
| ShiftMinutes | 480 | Total shift length |
| PlannedStops | 30 | Breaks, planned maintenance, no-demand time |
| PlannedTime | =ShiftMinutes − PlannedStops | Basis for OEE |
| DowntimeMinutes | Sum from the downtime log | Unplanned stops, changeovers |
| IdealCycleSec | 12 | Per part, from the product master |
| TotalCount, GoodCount | 1,950, 1,890 | From counters or the operator |
| Product | Bracket 220 | Ideal cycle differs by product |
Keep downtime in its own log with machine, shift, start, minutes, reason category and a short note. The shift row sums it with SUMIFS. That separation is what makes reason analysis possible.
The Formulas
RunTime (minutes): =[@PlannedTime] - [@DowntimeMinutes] Availability: =IF([@PlannedTime] = 0, "", [@RunTime] / [@PlannedTime]) Performance (cap at 100%; above means the ideal cycle is wrong): =IF([@RunTime] = 0, "", MIN(1, ([@IdealCycleSec] * [@TotalCount] / 60) / [@RunTime])) Quality: =IF([@TotalCount] = 0, "", [@GoodCount] / [@TotalCount]) OEE: =IFERROR([@Availability] * [@Performance] * [@Quality], "")
If Performance regularly exceeds 100 percent, the ideal cycle time is set too slow. Correct it at the source rather than relying on the MIN cap, which only hides the problem.
Aggregating Correctly
Never average OEE percentages across shifts or machines. A short shift at 90 percent and a full shift at 50 percent do not average to 70. Sum the inputs and recalculate.
Weekly OEE for a line (line in A2, week start in B1):
=LET(
pt, SUMIFS(tblLog[PlannedTime], tblLog[Line], A2, tblLog[Date], ">=" & B1, tblLog[Date], "<" & B1 + 7),
rt, SUMIFS(tblLog[RunTime], tblLog[Line], A2, tblLog[Date], ">=" & B1, tblLog[Date], "<" & B1 + 7),
ideal, SUMPRODUCT((tblLog[Line]=A2) * (tblLog[Date]>=B1) * (tblLog[Date]<B1+7),
tblLog[IdealCycleSec] * tblLog[TotalCount]) / 60,
tot, SUMIFS(tblLog[TotalCount], tblLog[Line], A2, tblLog[Date], ">=" & B1, tblLog[Date], "<" & B1 + 7),
good,SUMIFS(tblLog[GoodCount], tblLog[Line], A2, tblLog[Date], ">=" & B1, tblLog[Date], "<" & B1 + 7),
(rt / pt) * (ideal / rt) * (good / tot))The Six Big Losses
| Loss | OEE factor | Example reason codes |
|---|---|---|
| Breakdowns | Availability | Mechanical failure, electrical, tooling break |
| Setup and changeovers | Availability | Product change, die change, cleaning between runs |
| Minor stops | Performance | Jams, sensor trips, material feed |
| Reduced speed | Performance | Running below rated speed, worn tooling |
| Startup rejects | Quality | Scrap while warming up after changeover |
| Production rejects | Quality | Scrap and rework during steady running |
Use a short, fixed list of reason codes mapped to these categories. Operators choosing from 8 to 15 reasons is realistic; 60 reasons produces "Other".
Downtime Pareto
Minutes by reason this month (spills sorted, highest first):
=SORTBY(UNIQUE(tblDowntime[Reason]),
SUMIFS(tblDowntime[Minutes], tblDowntime[Reason], UNIQUE(tblDowntime[Reason]),
tblDowntime[Date], ">=" & EOMONTH(TODAY(), -1) + 1), -1)Chart the result as a Pareto (bars plus cumulative percentage line). Usually two or three reasons account for most of the lost minutes; those are the improvement projects.
Data Collection That Operators Accept
A simple tablet or PC form per machine: shift start, counts, downtime events with a reason dropdown.
Record downtime as it happens, not reconstructed at shift end.
Machine counters or PLC data where available, so counts are not typed.
Show the operator their shift's OEE at the end of the shift; feedback improves data quality.
Excel is fine for analysis but weak for simultaneous shop-floor entry from many stations. That is where an Access front end with a shared back end works better; see our manufacturing database in Access and Access ERP.
Where AI Helps
Classify free-text downtime notes into your reason codes for older data.
Summarise the week: biggest losses, changes from last week, lines to watch.
Spot patterns across shifts, products and operators for the engineer to investigate.
Weekly narrative generation follows the approach in automating report commentary with AI. Quality losses link to the nonconformance (NCR) log. For a live dashboard refreshed from shop-floor data, see Excel dashboards and BI reporting.
FAQ
OEE calculation FAQ
Formulas, definitions and benchmarks