To create a p chart in Excel, list each subgroup’s sample size and count of nonconforming units, calculate the subgroup proportions and their three-sigma control limits, then plot those values as a line chart. A p chart shows whether the proportion of units that fail a defined standard is changing over time; it is not the same as a chart of the number of defects.
Before starting, make sure every subgroup uses the same definition of a nonconforming unit and that each inspected unit can be classified as conforming or nonconforming. The limits account for different sample sizes, so the sample size can vary between subgroups. The steps below use Excel for Microsoft 365 on Windows.
What You’ll Need
- Excel for Microsoft 365 and a worksheet containing subgroup data.
- A time-ordered label for each subgroup, such as a date, shift, batch, or inspection number.
- The number of units inspected in each subgroup and the number of nonconforming units found.
- Enough consistently collected subgroups to make a useful process chart. A handful can demonstrate the method, but more observations usually make patterns easier to assess.
Step-by-Step
- Set up the data table. In row 1, enter headings: A1
Subgroup, B1Inspected, C1Nonconforming, D1Proportion, E1Center Line, F1UCL, and G1LCL. Put one subgroup in each row, in chronological order. For a small worked example, enter five rows: subgroup 1 has 100 inspected and 4 nonconforming; subgroup 2 has 120 and 6; subgroup 3 has 80 and 3; subgroup 4 has 100 and 5; subgroup 5 has 100 and 2. Check that each nonconforming count is zero or greater and no larger than its inspected count, and that no sample size is zero. - Calculate each subgroup’s proportion. The observed proportion is the nonconforming count divided by the number inspected. With sample sizes in B2:B6 and counts in C2:C6, enter this in D2:
=C2/B2. Copy D2 down through D6. In the example, D2 is 0.04, or 4%, and D4 is 0.0375, or 3.75%. Format the results as percentages using Home > Number > Percent Style; increase decimal places if needed to see small differences. - Calculate the overall proportion for the center line. The p chart uses the total nonconforming count divided by the total inspected count. This weights subgroups by size; do not simply average the subgroup percentages when sample sizes differ. With sample sizes in B2:B6 and counts in C2:C6, enter this in E2:
=SUM($C$2:$C$6)/SUM($B$2:$B$6). Copy E2 down through E6 so a center-line value is available for every plotted row. In the example, the counts total 20 and the inspected total is 500, so the center line is 20/500 = 0.04, or 4%. - Calculate the upper control limit for each row. Because the standard error depends on subgroup size, the limits can change from one subgroup to another. With the center line in column E and that row’s sample size in column B, enter this in F2:
=MIN(1,$E2+3*SQRT($E2*(1-$E2)/B2)). Copy it down through F6. The formula calculates a three-sigma upper limit and caps it at 1, since a proportion cannot exceed 100%. For a subgroup of 100 in the example, the UCL is approximately 0.0988, or 9.88%. - Calculate the lower control limit. The lower three-sigma limit can be negative, but a proportion cannot be below zero, so the formula sets any negative result to zero. With the center line in column E and the sample size in column B, enter this in G2:
=MAX(0,$E2-3*SQRT($E2*(1-$E2)/B2)). Copy it down through G6. For the example’s subgroup of 100, the unbounded lower limit is below zero, so the charted LCL is 0%. With a subgroup size of 120, the UCL is about 9.37%; its lower limit is also zero. - Extend the formulas if you have more subgroups. If your last data row is not row 6, replace the 6 in the center-line formula with your actual last row, and fill all four calculated columns through that row. Keep the absolute references around the total ranges in the center-line formula. The row references in the UCL and LCL formulas should change as you copy them, because each row has its own sample size.
- Insert the chart. Select A1:A6, then hold Ctrl and select D1:G6. On Windows, use Insert > Insert Line or Area Chart > Line with Markers. Excel creates a chart with subgroup labels on the horizontal axis and the observed proportion, center line, UCL, and LCL as separate series. If selecting separate ranges is awkward, copy the subgroup labels and columns D:G to a temporary contiguous area, select that area, and insert the same chart.
- Make the chart readable and check it. Add a descriptive chart title, such as
Nonconforming Proportion by Subgroup. Format the vertical axis as a percentage: right-click the axis, choose Format Axis, and set Number to Percentage. Make the observed proportions easy to distinguish from the three limit lines, and verify that the horizontal labels remain in time order. In the example, the center line should be 4%, the fifth subgroup’s observed proportion is 2%, and the control limits change when the sample size changes.
The plotted limits are statistical control limits, not customer specifications or targets. A point beyond a limit or a sustained nonrandom pattern is a signal to investigate the process; it does not, by itself, identify the cause.
Common Mistakes
- Using an unweighted average for the center line. When subgroup sizes vary, averaging the percentages gives small and large subgroups equal influence. Use total nonconforming units divided by total inspected units instead.
- Charting counts instead of proportions. A larger subgroup can naturally contain more nonconforming units even when its underlying rate is unchanged. Plot the subgroup proportion and use limits that reflect the subgroup size.
- Using a p chart for defect counts. A p chart counts units classified as conforming or nonconforming. If one unit can have several defects and you are counting those defects, consider a chart designed for defect counts, such as a c or u chart.
- Applying one fixed limit to every subgroup. With varying sample sizes, the standard error changes. Calculate the UCL and LCL on every row rather than copying one constant limit across the chart.
- Mixing definitions or time periods. Changing what qualifies as nonconforming, or placing observations out of sequence, can make the chart misleading. Keep the inspection rule consistent and label subgroups clearly.
- Treating the limits as pass/fail specifications. Control limits describe expected process variation under the chart’s assumptions. A process can be stable but miss a customer target, or meet a target while showing an unusual pattern.
Tips
- Use the same inspection method, criteria, and unit of analysis across all subgroups. If a unit can be counted more than once as a defect, decide whether the question is about defective units or defect events before choosing a chart.
- Keep the subgroup labels meaningful and chronological. Dates, shifts, production lots, or inspection rounds make it easier to connect an unusual point with a process change.
- Use a consistent number format for D:G. Percentage formatting makes proportions and limits easier to compare, while extra decimal places can help when values are close.
- Keep the formulas in the workbook rather than pasting only their displayed values. When new subgroups arrive, extend the data and formulas, update the chart’s source range, and confirm that the center line includes the intended data period.
- Review points beyond limits and nonrandom runs or trends using your organization’s charting rules. Avoid changing the limits just to make a point appear in control; investigate and document any decision to exclude or separate data.
- Excel for Mac has broadly similar chart and formula features. For selecting the second, nonadjacent data range, use Command rather than Ctrl. Ribbon wording or layout can vary slightly by release.
Troubleshooting
The chart shows counts or the wrong series. Check the chart’s source range and confirm that the selected columns are A and D:G, not B or C. If Excel has treated the rows as series, use Chart Design > Switch Row/Column so each column is a plotted series and each subgroup is a category.
The control limits are flat even though sample sizes vary. Confirm that the UCL and LCL formulas refer to the sample size on the same row, such as B2 in row 2 and B3 in row 3. A formula that points to a fixed sample-size cell will incorrectly reuse one subgroup’s limits.
You see a #DIV/0! error. At least one sample-size cell may be blank or zero. Enter a positive inspected count for each subgroup, and do not calculate a proportion for a subgroup that was not inspected. Also check that the center-line formula’s total sample-size range includes the intended rows.
The results look too large or exceed 100%. Check that the nonconforming count does not exceed the inspected count and that the values are numeric rather than text. The observed proportion is count divided by sample size. The UCL formula caps the upper limit at 1, while the LCL formula cannot go below zero.
The chart does not update when you add data. Excel may not have included new rows in the chart source range or in the center-line formula. Edit the chart’s data range and extend the formulas to the added rows. Converting the source data to an Excel table can help new rows and formulas extend together, but check the chart range after adding them.
The center line or limits are undefined. If the total nonconforming count is zero, the estimated overall proportion is zero; if every inspected unit is nonconforming, it is one. Those boundary cases make the usual three-sigma p-chart calculations degenerate and provide little information for detecting changes in that direction. Check the data and inspection method, and consult an appropriate quality-control reference before interpreting such a chart.
Frequently Asked Questions
What does a p chart show?
A p chart tracks the proportion of inspected units that are nonconforming in each subgroup. It helps distinguish ordinary process variation from signals that may merit investigation.
Can sample sizes vary in a p chart?
Yes. That is a common reason to use a p chart. The center line is based on the total counts, while each subgroup’s control limits use its own sample size.
Should I use a p chart or an np chart?
Use a p chart when you want to track proportions, especially when sample sizes vary. An np chart tracks the number of nonconforming units and generally assumes a constant sample size.
Why is the lower control limit zero?
The calculated three-sigma lower limit can be negative, but a proportion cannot be negative. The p-chart convention is to set that limit to zero. In Excel, the MAX function in the LCL formula does this.
What should I do when a point falls outside a control limit?
Check the data first, then investigate what was happening in the process around that subgroup. Treat it as a signal to learn more, not as proof of a specific cause or an automatic reason to alter the process.
Can Excel create a p chart with one click?
Excel does not provide a dedicated p-chart command in its standard chart menu. Calculate the proportions and control limits in worksheet columns, then plot those series in a line chart.
Related
- How to Count in Excel While Skipping Specific Cells Manually
- Count Cells by Color in Excel: Practical Methods
- Count Green Cells in Excel: 3 Reliable Ways to Do It
Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.
More in Charts & pivot tables
- Excel Pivot Table Grand Total Column: Add or Fix ItTo add a grand total column in an Excel PivotTable, click anywhere inside it and choose PivotTable Design…
- How to Edit the Chart Area in ExcelTo edit a chart area in Excel, select the chart, open the contextual Format tab, choose Chart Area…
- Excel Line Chart Smooth Curve: Setup, Limits, and TipsYes. In Excel for Microsoft 365 on Windows, select a line in the chart, open Format Data Series,…
- Excel Copy Chart Formatting to Another Chart QuicklyTo copy a chart’s formatting in Excel for Microsoft 365 on Windows, select the source chart and press…
- Dashboard from Excel Data: Build a Clear, Useful ViewYes. To make a dashboard from Excel data, organize the source as a clean Excel Table, summarize it…
- Excel Line Charts: Ignore Blank Cells or Show Gaps?Yes. In Excel for Microsoft 365 on Windows, select the line chart, choose Chart Design > Select Data…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.