Excel for Manufacturing Jobs: Production and Quality Data

Excel for manufacturing is mostly about summarising production and quality logs: how many units each line made, how many were defective, which shift is slower, and how efficient the equipment was. A handful of functions, SUMIFS, COUNTIFS and AVERAGEIFS, plus pivot tables, cover most of that work. This guide shows each one on a small production log, so you can prepare for a production, quality or planning role and its Excel test.

What manufacturing teams use Excel for#

Factories often have systems that collect data, but the analysis still lands in Excel. Typical tasks for production supervisors, quality technicians, planners and process engineers include:

• Daily and weekly output by line, shift, product or operator
• Quality figures such as defect counts, defect rate, scrap and first-pass yield
• Downtime by cause, and equipment efficiency (OEE)
• Planning: matching orders to capacity and checking stock of parts

Almost all of it starts with a log: one row per line, shift or batch. Here is a small one used in every example below:

Engineering and supply chain roles use the same skills on different data; see the Excel skills for engineers and the Excel skills for supply chain roles.

Total output and counts with SUMIFS and COUNTIFS#

SUMIFS adds the numbers that meet one or more conditions, and COUNTIFS counts the rows that do. They answer most "how many?" questions on a production log.

SUMIFS puts the range to add first, then pairs of "where to look" and "what to match". Every condition must be true for a row to count. The SUMIFS function guide and the COUNTIFS function guide list every argument, and the article on COUNTIFS and SUMIFS with multiple conditions has more examples, including date ranges.

Defect rate and yield#

Defect rate is total defects divided by total units for the same group. Use one SUMIFS on top and one underneath:

=SUMIFS(E2:E7,B2:B7,"Line 2")/SUMIFS(D2:D7,B2:B7,"Line 2")

Line 2 had 82 defects out of 2,850 units, so the result is 0.0288, or 2.9% when you format the cell as a percentage (Windows: Ctrl+Shift+%; Mac: Control+Shift+%). Line 1 is 55 ÷ 3,550 = 1.5%.

Yield (here, the share of good units) is the opposite: =1- the defect rate, so Line 2's yield is about 97.1%.

One trap catches many people. If each row had its own defect percentage, averaging those percentages would give each shift equal weight, whatever its size. Dividing the totals gives the true rate for the whole line. In an interview task, totals divided by totals is the safe choice.

Compare lines and shifts with AVERAGEIFS and MAXIFS#

AVERAGEIFS returns the average of the rows that meet your conditions, and MAXIFS and MINIFS return the largest or smallest. They make quick comparisons between lines, shifts or products.

=AVERAGEIFS(D2:D7,C2:C7,"Day")

The day shift averages 1,100 units per shift, against 1,000 for nights (=AVERAGEIFS(D2:D7,C2:C7,"Night")). To find the worst Line 1 shift for defects:

=MAXIFS(E2:E7,B2:B7,"Line 1")

The result is 22. The AVERAGEIFS function guide explains the argument order, and AVERAGEIFS with several conditions shows how to average by line and shift at the same time. MAXIFS and MINIFS need Excel 2019 or later, including Microsoft 365.

How to calculate OEE in Excel#

OEE (Overall Equipment Effectiveness) measures how much of a machine's planned time produced good parts at full speed. It multiplies three ratios:

In Excel, put each input in its own cell and multiply the three ratios. There is a useful check: OEE also equals good units ÷ (planned time × ideal rate), here 1,182 ÷ 1,440 = 82.1%. If the two answers match, your inputs are consistent. Always confirm how your employer defines planned time, because some sites exclude breaks and some don't.

Summarise a month with a pivot table#

A pivot table is an Excel tool that groups and totals a list without formulas. For a month of production rows, it is often faster than writing a SUMIFS for every line and shift.

1. Click any cell in the log and go to Insert > PivotTable, then click OK.
2. Drag Line to Rows and Shift to Columns.
3. Drag Units and Defects to Values. They show as sums.

You now have units and defects for every line and shift, and it updates when you click Refresh after adding rows. Many teams use pivots for the weekly report and SUMIFS for fixed dashboards that must always show the same layout. The guide to Excel for operations managers covers the planning side, such as lead times and due dates.

Excel tests for manufacturing jobs#

Employers for production, quality and planning roles often use a general Excel test or a short case study. Typical tasks look like the examples above: a total by line, a count with two conditions, an average by shift, a rate built from two totals, and a pivot summary. The operations case study mock test gives you a timed task of this kind, and the intermediate Excel test covers the core functions. If you're not sure where you stand, the Excel level test takes 12 questions and points you to the right lessons.

Practise it#

Production questions come down to grouping and totals. Practise COUNTIFS and SUMIFS in a live spreadsheet: you type each formula and it checks it instantly. Then compare groups with AVERAGEIF, MAXIFS and MINIFS and build your first summary report in Pivot tables 1. Start with the Line 2 defect rate in the exercise on this page.