Excel guide · 5 min read
How to calculate OEE in Excel
=G2/B2, =(D2/60)*E2/G2 and =F2/E2, multiplied together. Line A in the sample runs at 78.7% OEE.Sample data
Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.
| Row | A | B | C | D | E | F | G | H | I | J | K | L |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Line | Planned min | Downtime min | Ideal cycle (sec) | Total count | Good count | Run min | Availability | Performance | Quality | OEE | |
| 2 | Line A | 480 | 47 | 30 | 780 | 756 | 433 | 0.90208333333 | 0.90069284065 | 0.96923076923 | 0.7875 | |
| 3 | Line B | 480 | 95 | 45 | 470 | 461 | 385 | 0.80208333333 | 0.91558441558 | 0.98085106383 | 0.7203125 | |
| 4 | Line C | 240 | 20 | 20 | 590 | 555 | 220 | 0.91666666667 | 0.89393939394 | 0.9406779661 | 0.77083333333 | |
| 5 | Line D | 480 | 62 | 60 | 380 | 377 | 418 | 0.87083333333 | 0.90909090909 | 0.99210526316 | 0.78541666667 |
Download this sheet as an Excel practice file (.xlsx)
Lay out the shift log
One row per line per shift: planned production minutes in B (the shift minus planned breaks), unplanned downtime in C, the ideal cycle time in seconds per unit in D, total units made in E and good units in F. Run time in G is =B2-C2.
Keep the units consistent. Times here are in minutes and the cycle time in seconds, so the performance formula divides the cycle time by 60. Mixing seconds and minutes is the most common reason an OEE sheet shows nonsense.
Availability, performance and quality
Availability in H2 is =G2/B2: the share of planned time the line actually ran. Line A lost 47 minutes, so it ran 433 of 480, 90.2%.
Performance in I2 is =(D2/60)*E2/G2: the minutes the output should have taken at the ideal speed, divided by the minutes it actually ran. Line A made 780 units at an ideal 30 seconds each, which is 390 minutes of work in 433 minutes of running, 90.1%. Slow cycles and small stops land here.
Quality in J2 is =F2/E2: good units over all units. Line A scrapped or reworked 24 of 780, so quality is 96.9%. Fill H2:J2 down.
Result: 0.7875 shows as 78.8% with Percent format
0.7875, 78.7% OEE for Line A: 90.2% x 90.1% x 96.9%.
Times typed as hh:mm
If operators log downtime as 0:47 rather than 47, Excel stores it as a fraction of a day: 47 minutes is 0.0326. Dividing that by 480 planned minutes gives a tiny, wrong availability. Multiply by 1440, the minutes in a day, to turn a time into minutes first.
The same applies to shift lengths typed as 8:00 (multiply by 1440 for 480 minutes) and to start and stop times: =(stop-start)*1440. For shifts that cross midnight, use =MOD(stop-start,1)*1440, as in time difference over midnight.
Result: 47
47: the 0:47 in M2 converted to minutes, ready to subtract from planned minutes.
Plant OEE across lines, and the biggest loss
Don't average the four OEE percentages. Line C ran a half shift, so it shouldn't count as much as a full one. Weight by planned time instead: OEE also equals good count x ideal cycle time / planned time, so the plant figure is the total ideal minutes of good output over the total planned minutes. Here the plain average (76.6%) happens to be close, but one short shift with a very high or very low OEE would pull an average far off.
To find the line to work on, look up the lowest OEE with XLOOKUP and MIN, then compare its three factors. Line B is lowest, and its availability (80.2%) is the weak factor, so downtime is where the minutes went, not speed or scrap.
Result: 0.76532738095 shows as 76.5% with Percent format
0.7653, 76.5% plant OEE: good output worth 1,285.75 ideal minutes in 1,680 planned minutes.
Which line has the lowest OEE?
XLOOKUP the smallest value in the OEE column and return the line name.
Result: Line B
Line B, at 72.0% OEE.
Why is a performance above 100% a problem?
It means the line made more than physically possible at the ideal speed, so the ideal cycle time is wrong: often an old spec or a figure that includes handling time. Line D with its cycle entered as 70 seconds instead of 60 shows it.
Result: 1.0606060606
1.0606: 106% performance, which can't be real. Fix the ideal cycle time, don't cap the result at 100%.
Turn OEE into lost minutes
A percentage tells you how bad a line is; minutes tell you what to fix and what it's worth. Every planned minute ends up in exactly one of four buckets, and they add back up to the planned time:
- Availability loss: the unplanned downtime itself, C2. Line A lost 47 minutes.
- Performance loss: run time minus the time the output should have taken,
=G2-D2/60*E2. Line A ran 433 minutes for 390 minutes of work at ideal speed, so 43 minutes went to slow cycles and small stops. - Quality loss: the ideal time spent making bad units,
=(E2-F2)*D2/60. Line A's 24 rejects at 30 seconds each cost 12 minutes. - Fully productive time: good units at ideal speed,
=F2*D2/60. Line A: 378 minutes. Divide by the 480 planned minutes and you get the same 78.7% OEE, which is a handy check that the three factors were built correctly.
Result: 136.83333333
136.83: minutes of performance loss across all four lines, the second biggest bucket after 224 minutes of downtime.
How do the four buckets add up across the plant?
Downtime is =SUM(C2:C5), 224 minutes. Performance loss is the formula above, about 137 minutes. Quality loss is the formula here, about 33 minutes. Fully productive time is =SUMPRODUCT(F2:F5*D2:D5)/60, 1,285.75 minutes. Together they make the 1,680 planned minutes.
Result: 33.416666667
33.42: minutes of quality loss on the four lines. Scrap is the smallest bucket here, so a scrap project would recover the least time.
Why not just work on the line with the lowest OEE?
Because the minutes say more than the rank. Line B has the lowest OEE, but the plant loses more time to slow running across all lines (about 137 minutes) than Line B loses to its own downtime (95 minutes). Sort the buckets by minutes, then by line, and start at the top. Small stops that nobody logs as downtime usually hide in the performance bucket.
Common mistakes
Mixing seconds and minutes
Pick one unit for time. With cycle time in seconds and run time in minutes, divide the cycle time by 60.
Dividing hh:mm times by minutes
Times are fractions of a day. Multiply by 1440 to get minutes before using them.
Averaging OEE across lines or shifts
Weight by planned time: total good units x ideal cycle time over total planned time.
Counting planned breaks as downtime
Remove planned stops from planned production time; downtime is only the unplanned part, so availability stays fair.
FAQ
What is the OEE formula in Excel?
=availability*performance*quality, where availability is run time/planned time, performance is ideal cycle time*total count/run time and quality is good count/total count.
Is there a quicker OEE formula?
Yes: OEE = good count x ideal cycle time / planned production time. It gives the same answer as multiplying the three factors, but you lose the breakdown that tells you where the loss is.
Why is my OEE over 100%?
Usually the ideal cycle time is too slow, so performance comes out above 100%. Use the true best-possible cycle time for the product and machine.
How do I convert hh:mm downtime to minutes in Excel?
Multiply the time by 1440, the number of minutes in a day, for example =C2*1440, and format the result as a number.
What is a good OEE score?
85% is the figure most often quoted as world class, but the number depends heavily on the ideal cycle time you choose and on what you count as planned time. Compare a line with its own trend under one fixed definition rather than with another plant's headline figure.
Functions used
SUMPRODUCTMultiply then addSUMAdds numbersXLOOKUPModern lookup in any directionMINSmallest numberMODRemainder after divisionTIMEBuilds a time
Related guides
- KPI formulas in Excel: how to calculate and score KPIs
- How to calculate MTBF and MTTR in Excel
- How to calculate takt time in Excel
- How to calculate first pass yield (FPY) in Excel
- How to calculate scrap rate in Excel
- How to calculate overtime in Excel
Updated