With planned minutes in B2, stop minutes in C2, ideal cycle seconds in D2, total units in E2 and good units in F2, the OEE formula in Excel is =D2*F2/(B2*60). To see the three factors as well, add =(B2-C2)/B2, =D2*E2/((B2-C2)*60) and =F2/E2, then multiply them. Format the results as percent.
Set up the sheet like this first
Every formula on this page points at the cells below. Type these headers in row 1 and one shift in each row after it.
Columns A to F are typed. Columns G onward calculate, so you never type in them.
| A | B | C | D | E | F | G | H | I | J | K | L | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Shift | Planned min | Stop min | Ideal cycle s | Total units | Good units | Run min | Avail. | Perf. | Quality | OEE | Check |
| 2 | Mon A | 480 | 55 | 2.4 | 9,300 | 9,021 | 425 | 88.5% | 87.5% | 97.0% | 75.2% | 75.2% |
| 3 | Mon B | 480 | 110 | 2.4 | 7,800 | 7,488 | 370 | 77.1% | 84.3% | 96.0% | 62.4% | 62.4% |
| 4 | Tue A | 240 | 20 | 3.0 | 4,000 | 3,920 | 220 | 91.7% | 90.9% | 98.0% | 81.7% | 81.7% |
The formulas to paste into row 2
Click the cell, paste the formula and press Enter. Then select G2:L2 and drag the corner down to fill the other rows.
| Cell | What it is | Paste this | Row 2 gives |
|---|---|---|---|
| G2 | Run time, minutes | =B2-C2 | 425 |
| H2 | Availability | =G2/B2 | 88.5% |
| I2 | Performance | =D2*E2/(G2*60) | 87.5% |
| J2 | Quality | =F2/E2 | 97.0% |
| K2 | OEE | =H2*I2*J2 | 75.2% |
| L2 | Check, from good units | =D2*F2/(B2*60) | 75.2% |
Here is the sum behind row 2. Performance is 2.4 x 9,300 = 22,320 seconds of ideal time, divided by 425 x 60 = 25,500 seconds of run time.
These are the standard definitions from Overall Equipment Effectiveness (R. C. Hansen, 2001), and they match our guide to what OEE is. Total units go in performance, and good units go in quality.
Column L is your built-in test, since K and L are the same sum written two ways. The page on the time and count methods shows why they must match.
If your rate is in units per minute
Many plants know a rate, not a cycle time. Put the ideal rate in D instead, and swap these two formulas in row 2.
The weekly totals and loss columns further down still expect a cycle in seconds, so those two swaps are not enough for them.
The easy way round is to keep the rate in a spare column, say S, and make D2 =60/S2. Then 60 / 25 = 2.4, and every formula on this page works unchanged.
The safer versions that survive a real month
The simple formulas break on a blank row or a shift with no run time. They show #DIV/0!, and that error spreads into every total.
Once the sheet works, replace H2, I2, J2 and L2 with these, and add M2 as a flag. Fill them down like the others.
| Cell | Safer formula | What it adds |
|---|---|---|
| H2 | =IF(B2>0,G2/B2,0) | No error on an empty row |
| I2 | =IF(G2>0,MIN(1,D2*E2/(G2*60)),0) | Caps performance at 100% |
| J2 | =IF(E2>0,F2/E2,0) | No error when nothing was made |
| L2 | =IF(B2>0,D2*F2/(B2*60),0) | Stops the check erroring on an empty row |
| M2 | =IF(ABS(K2-L2)>0.001,"CHECK","OK") | Flags a row where K and L differ by over 0.1 points |
The cap matters because a result over 100% means the ideal rate is wrong or the counter double counts. Column M will say CHECK on such a row, which is what you want, and performance over 100% explains the fix.
Weekly totals: do not use AVERAGE
This is the most common Excel mistake with OEE. =AVERAGE(K2:K4) gives the 240-minute shift the same weight as a 480-minute shift.
Put totals in row 5, under the three shifts, and add up the time instead.
| Cell | Paste this | Gives |
|---|---|---|
| B5 | =SUM(B2:B4) | 1,200 |
| C5 | =SUM(C2:C4) | 185 |
| G5 | =B5-C5 | 1,015 |
| H5 | =G5/B5 | 84.6% |
| I5 | =SUMPRODUCT(D2:D4,E2:E4)/(G5*60) | 87.1% |
| J5 | =SUMPRODUCT(D2:D4,F2:F4)/SUMPRODUCT(D2:D4,E2:E4) | 96.9% |
| K5 | =H5*I5*J5 | 71.4% |
| L5 | =SUMPRODUCT(D2:D4,F2:F4)/(B5*60) | 71.4% |
SUMPRODUCT multiplies each cycle by its units, row by row, and adds the results. That is what keeps a slow product from being counted like a fast one, and it is why J5 is weighted too.
The same logic applies across machines. See how to average OEE across machines.
Add a loss check in minutes
A percent tells you the score, and minutes tell you where to look. These columns split the planned time and must add back to it.
| Cell | Loss | Paste this | Row 2 gives |
|---|---|---|---|
| N2 | Speed loss, min | =G2-D2*E2/60 | 53.00 |
| O2 | Quality loss, min | =D2*(E2-F2)/60 | 11.16 |
| P2 | Good output, min | =D2*F2/60 | 360.84 |
| Q2 | Must equal B2 | =C2+N2+O2+P2 | 480.00 |
Stops of 55, plus 53, 11.16 and 360.84, come to 480 exactly. If Q does not equal B, a typed value is wrong, and the OEE loss tree shows which box to check.
Three Excel traps that have nothing to do with OEE
- Semicolons. Many European Excel setups use
;as the list separator. If Excel rejects a pasted formula, write=MIN(1;D2*E2/(G2*60))and use your own decimal mark. - Stops typed as time. A cell showing 0:55 holds a fraction of a day. Turn it into minutes with
=C2*1440, which gives 55, and format that cell as Number, since Excel may show it as a time. - Numbers stored as text. Values pasted from a machine export may look fine and still add up to 0. Check that they sit on the right of the cell, and clean them before you trust a total.
Several products in one shift
Give each run its own row, with its own ideal cycle. Do not average the cycle times of two products into one cell.
To pull the cycle from a list, keep product names in column A and cycles in column B of a sheet called Rates. With the product name in R2, this goes in D2:
=INDEX(Rates!$B$2:$B$20,MATCH(R2,Rates!$A$2:$A$20,0))
Prefer a sheet that is already built?
If you would rather not build this yourself, the OEE tracking template has it ready, and the OEE calculator handles a single shift.
A sheet needs someone to type every stop and count. Fabrico takes stops, speed and counts from PLCs or IoT sensors, and operators tag the cause at the line. See how Fabrico shows OEE per line.
What people ask next
What is the simplest OEE formula in Excel?
=D2*F2/(B2*60), with the ideal cycle in seconds in D2, good units in F2 and planned minutes in B2. It needs only three numbers, and it is the check used in column L above. For the reasoning, see time vs count OEE.
Why does my Excel OEE differ from the factors multiplied together?
Usually a capped performance, a typing error or rounded factors typed by hand. Let Excel keep full precision, and compare columns K and L. Any gap larger than rounding means one count or time is wrong, as listed in OEE calculation mistakes.
How do I calculate monthly OEE in Excel?
Use one row per shift and the row 5 pattern over the whole month, with SUM for time and SUMPRODUCT for ideal time. Never average the daily percentages. Change the ranges to match your rows, for example D2:D61 for 60 shifts.
Is there a free OEE Excel sheet I can download?
Yes. Our OEE tracking template is a workbook with 500 shift rows, a weekly view and a six big losses sheet. It uses standard formulas and has no macros.
Do these OEE formulas work in Google Sheets?
Yes. IF, MIN, ABS, SUM, SUMPRODUCT, INDEX and MATCH all exist in Google Sheets, so the cell references work as shown. Sheets also uses a semicolon in some regions.
Fabrico is a light MES for OEE. It takes stops, speed and counts from PLCs or IoT sensors, with add-on AI cameras counting output where a machine has no usable signal, and operators tag the cause at the line.
← More OEE Lab guides · Download the OEE tracking template →