P Chart in Excel: Build Control Limits and Read Results

To make a p chart in Excel, calculate each subgroup’s fraction nonconforming, find the overall fraction and three-sigma control limits, then plot those values over time. You’ll need a sequence of subgroups, the number of items inspected in each one, and the number found nonconforming. A p chart is useful for tracking the proportion of units with a yes/no defect or condition, especially when subgroup sizes vary.

What You’ll Need

Microsoft Excel for Microsoft 365 on Windows. The formulas and chart steps below also work in recent desktop Excel versions.
A dated or ordered list of subgroups, with the number inspected and the number nonconforming in each subgroup.
A consistent definition of “nonconforming.” Each inspected unit should be classified as either conforming or nonconforming for the measure being tracked.

Use subgroups collected in a meaningful time order, such as one production shift per row or one day per row. A p chart assumes the units are independent enough for a binomial model and that the way data are collected and classified remains consistent. It is a process-monitoring tool, not a pass/fail test against a customer specification.

Step-by-Step

Set up the data table. In row 1, enter these headings: A1, Subgroup; B1, Inspected; C1, Nonconforming; D1, Fraction; E1, LCL; F1, Centerline; and G1, UCL. Enter one subgroup per row, beginning in row 2. For example, a row might represent Monday’s inspection, with 120 items inspected and 6 nonconforming. Use counts, not percentages, in columns B and C. The number in C must be a whole-number count from zero through the number in B. Keep the subgroups in the order they were collected.
Calculate the fraction for each subgroup. With subgroup data in rows 2:11, where B contains the inspected count and C contains the nonconforming count, enter this in D2: =C2/B2. Press Enter, then use the fill handle to copy the formula through D11. For the example of 6 nonconforming out of 120, the result is 0.05, or 5%. Format D2:D11 as Percentage if that is easier to read; Excel’s percentage format changes how the result looks, not the underlying value used in the calculations. Make sure every inspected count in column B is greater than zero.
Calculate the overall fraction nonconforming. With the subgroup counts in B2:B11 and C2:C11, enter this formula in J2 and label J1 Overall p: =SUM(C2:C11)/SUM(B2:B11). This weights each subgroup by the number inspected. For example, if 52 of 1,000 total units are nonconforming, J2 returns 0.052, or 5.2%. Do not take a simple average of the values in column D when subgroup sizes differ; that would give a small subgroup the same weight as a large one.
Calculate the lower control limit. With the overall fraction in J2, the inspected count for the current subgroup in B2, and subgroup data starting in row 2, enter this in E2: =MAX(0,$J$2-3*SQRT($J$2*(1-$J$2)/B2)). Copy it down through the last subgroup. The formula calculates the lower three-sigma limit for that subgroup and prevents the result from falling below zero. The dollar signs keep the reference to J2 fixed while the B-row reference changes.
Add the centerline. With J2 holding the overall fraction nonconforming, enter this in F2: =$J$2. Copy it down the column. The centerline is the same for every subgroup: it is the overall fraction across all inspected units. If J2 is 0.052, the centerline is 5.2% when displayed as a percentage.
Calculate the upper control limit. With J2 holding the overall fraction and B2 holding the current subgroup size, enter this in G2: =MIN(1,$J$2+3*SQRT($J$2*(1-$J$2)/B2)). Copy it down through the final row. The formula caps the upper limit at 1, or 100%, because a fraction cannot exceed 1. The limits can differ from row to row when the subgroup sizes differ; that is expected for a p chart.
Format and check the calculated columns. Format D:G as Percentage, using the same number of decimal places for all four columns. Check a few rows by hand: a subgroup’s fraction should equal its nonconforming count divided by its inspected count, and the lower limit should not exceed the centerline, which should not exceed the upper limit. If a subgroup has no nonconforming units, its fraction is zero; it is still a valid point. If the overall fraction is exactly zero or one, the calculated limits collapse to that value, so the chart cannot show ordinary variation. Investigate whether more representative data are needed.
Make a chart-ready block. To avoid selecting separated columns, create a small block in I1:M1 with headings Subgroup, Fraction, LCL, Centerline, UCL. With your main table in A:G, enter =A2 in I2, =D2 in J2, =E2 in K2, =F2 in L2, and =G2 in M2. Copy each formula down to the final data row. Format J:M as percentages. This block links to the calculations, so it updates when the source data change.
Insert the chart. Select the filled chart-ready block, including its headings, such as I1:M11. On the Windows ribbon, choose Insert > Insert Line or Area Chart > Line with Markers. Excel should use the subgroup labels for the horizontal axis and plot the fraction, lower limit, centerline, and upper limit as four series. If it plots each row as a separate series, use Chart Design > Switch Row/Column.
Make the chart readable and interpret it. Give it a descriptive title, such as “Nonconforming Fraction by Shift.” Add axis titles with Chart Design > Add Chart Element > Axis Titles if helpful. The vertical axis is a proportion, so set its number format to Percentage if it is shown as decimals. Compare each fraction point with that subgroup’s limits. A point outside its control limits is a signal to investigate, not proof of a particular cause. Also look for sustained runs or clear trends, and use your organization’s established signal rules rather than inventing rules after seeing the chart.

The formulas above assume a table ending in row 11 only in the examples. Replace 11 with your actual final row in the overall-fraction formula and fill the row formulas down to match your data. Excel for Mac has the same formulas and a similar Insert chart workflow; ribbon labels or placement can vary slightly by release. In Excel for the web, formulas and basic line charts are available, though some chart-formatting controls may be arranged differently.

Common Mistakes

Averaging subgroup percentages equally. If subgroup sizes vary, use total nonconforming divided by total inspected for the centerline. A simple average of the subgroup fractions can produce the wrong centerline.
Using the wrong denominator. Divide the nonconforming count by the number actually inspected in that same subgroup. Do not divide by the number produced, scheduled, or inspected across the whole period unless it is genuinely the subgroup size.
Confusing defects with defective units. A p chart tracks the fraction of units classified as nonconforming. If one unit can have several defects and you want to track defects per unit, a different chart may be appropriate.
Entering percentages as whole numbers. If a subgroup has 5 nonconforming items out of 100, enter 5 in C and 100 in B, not 5% in C. The calculated fraction is 0.05. You may format it as 5% for display.
Using one fixed limit for unequal subgroup sizes. The three-sigma limits depend on each subgroup’s size. Copy the formulas down so the B-row reference changes; do not use a single limit calculated from an average subgroup size when actual sizes vary substantially.
Changing the definition or sampling method midstream. Mixing inspection criteria, products, or sampling procedures can make points difficult to compare. Keep the measure consistent, or clearly separate periods that use different methods.

Tips

Keep the raw counts and formulas in separate, clearly labeled columns. This makes it easier to audit a surprising point and less likely that someone will overwrite a calculation.
Use dates or meaningful subgroup labels on the horizontal axis. If labels become crowded, widen the chart or display fewer axis labels; don’t remove data points from the calculations simply to make the chart look cleaner.
Retain more precision in formulas than you display. Formatting a value to one decimal place does not round the stored value, which is desirable for limit calculations.
For a stable process, control limits are estimated from process data and describe expected process variation. They are not the same as specification limits, which define product or service requirements.
When subgroup sizes vary sharply, note the size alongside the chart or in a nearby table. The varying limits are meaningful, and readers should be able to see why their widths change.
After adding new rows, extend the formulas and the chart-ready block, then confirm that the chart includes the new subgroups. Converting the data range to an Excel table with Insert > Table can help keep expanding data organized, though you should check that the chart range expands as intended.

Troubleshooting

The formula shows #DIV/0!: Check that the inspected count in column B is not zero or blank. Correct the source data rather than hiding an invalid subgroup in the chart. If a row is intentionally unused, leave it outside the calculation range.
The formula is displayed as text: Change the cell format to General, select the cell, press F2, then Enter. Also check that the formula begins with an equals sign and that there is no apostrophe before it. On a Mac, use Control-U to edit a cell, then Return.
The chart shows decimals instead of percentages: The calculations may be correct. Format the fraction and limit columns as Percentage, or format the chart’s vertical axis as Percentage using Format Axis > Number. Avoid multiplying the source values by 100 if you are also applying percentage formatting.
The chart has the wrong series or axis labels: Select the chart, choose Chart Design > Select Data, and check that the horizontal axis labels point to the subgroup column in the chart-ready block and that the four series use the fraction and limit columns. Use Switch Row/Column if Excel interpreted the data in the wrong direction.
The limits look constant even though subgroup sizes differ: Check that the formulas were filled down and refer to B2, B3, and so on. If every formula refers to $B$2, the subgroup-size reference was accidentally fixed.
A point is outside the limits or the lower limit is zero: Recheck the counts, subgroup size, data order, and calculation range. A point outside the limits can be a valid signal, and a zero lower limit can be mathematically correct for a small fraction or small subgroup. Don’t delete or alter a point just to bring it inside the limits; investigate the process and document any data correction.

Frequently Asked Questions

What does a p chart show?
A p chart shows the fraction of inspected units that are nonconforming in each subgroup, alongside a centerline and control limits. It helps distinguish routine process variation from patterns that may merit investigation.

Can I use a p chart when sample sizes change?
Yes. The p chart is suitable for varying subgroup sizes, and its limits change with the inspected count in each subgroup. Use the actual size for each row in the limit formulas.

What is the difference between a p chart and an np chart?
A p chart plots a fraction and can accommodate varying subgroup sizes. An np chart plots the count of nonconforming units and is generally used when subgroup size is constant.

Can a p chart have a lower control limit of zero?
Yes. The calculated lower limit cannot be below zero, so the formula sets negative results to zero. This is especially common when the overall fraction is small or subgroup sizes are limited.

Does Excel have a dedicated p chart button?
Excel’s standard chart menu does not create a complete p chart automatically. Calculate the fractions and limits in worksheet cells, then use a line chart to display the values.

Should I remove a point outside the control limits?
No, not simply because it is outside. Verify the underlying record and sampling procedure, then investigate possible causes. Correct a point only when you have a documented data error, and preserve the original record.

Try it: the Percentage Formula Generator: percent of, percent change, increase and decrease, with the formula.

More in Charts & pivot tables

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.