How to Compute Cpk in Excel: Formula and Step-by-Step Guide

AvatarByEric Dawson Computers & Software

To compute Cpk in Excel, put your measurements in one column, calculate the mean with AVERAGE, estimate the standard deviation, then take the smaller of two ratios: (USL minus mean) divided by three standard deviations, and (mean minus LSL) divided by three standard deviations. With data in A2:A21, the lower limit in E2, the upper limit in E3, and the standard deviation in E8, the one-cell formula is =MIN((E3-AVERAGE(A2:A21))/(3*E8),(AVERAGE(A2:A21)-E2)/(3*E8)). A result of 1.33 or higher is the usual pass mark. The catch is the standard deviation: a true Cpk uses the within-subgroup estimate (average moving range divided by 1.128 for individual readings), while STDEV.S gives you Ppk. Both routes, with a real 20-part dataset, are worked through below.

What Cpk Measures and Where the Formula Comes From

Cpk is a process capability index. It compares the spread of a process against the specification limits a customer set, and it penalizes a process whose average has drifted away from the middle of those limits. The NIST/SEMATECH e-Handbook of Statistical Methods, section 6.1.6, defines it as the minimum of two one-sided indices: Cpu = (USL minus the mean) divided by three sigma, and Cpl = (the mean minus LSL) divided by three sigma. USL and LSL are the upper and lower specification limits, and sigma is the process standard deviation.

Two related indices sit alongside it. Cp = (USL minus LSL) divided by six sigma ignores where the mean sits and only asks whether the spread could fit inside the tolerance. Cpk can never exceed Cp, and the NIST handbook expresses the link as Cpk = Cp(1 minus k), where k is the distance from the specification midpoint to the process mean, scaled by half the tolerance width. A perfectly centered process has k = 0 and Cpk equal to Cp. The further the mean drifts, the bigger the gap between the two.

NIST’s own illustration is easy to reproduce by hand. With USL = 20, LSL = 8, a mean of 16 and a standard deviation of 2, Cp comes out at 1.0 and Cpk at 0.667. The handbook’s comment is that it would like to see Cpk of at least 1.0, so this is not a good process. Six Sigma Study Guide, written by Ted Hessing, adds the industry benchmark most customers actually ask for: a Cpk of at least 1.33.

Every version of the formula assumes two things. The data should be roughly normally distributed, and the process should be in statistical control (stable over time) before the index means anything. NIST’s handbook states the normality assumption outright, and Hessing’s guide states the control assumption. A Cpk calculated on a process that is drifting or has outliers will look better or worse than reality.

How to Compute Cpk in Excel Step by Step

Computing Cpk in Excel means a column of measurements, a column of moving ranges, and labeled cells that turn the mean and standard deviation into Cp, Cpk, Pp and Ppk, using AVERAGE, STDEV.S, ABS, COUNT and MIN in Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019 and Excel 2016, on Windows or Mac.

Set up the Sheet

  1. Column A, measurements. Type a header in A1 (Diameter, for example) and paste your readings into A2 downward, one reading per row, in the order they were produced. Order matters for the moving range later.
  2. Column B, moving ranges. In B3 enter =ABS(A3-A2) and fill it down to the last row of data. B2 stays empty because the first reading has no predecessor.
  3. Column D, labels, and column E, values. Type LSL in D2, USL in D3, n in D4, Mean in D5, Overall s in D6, Avg MR in D7, Sigma within in D8, then Cp, Cpu, Cpl, Cpk in D9 to D12 and Pp, Ppu, Ppl, Ppk in D13 to D16.
  4. Enter the limits. Type the lower specification limit in E2 and the upper limit in E3 as plain numbers, not text. A limit typed as “25.10 mm” will break every formula that references it.
  5. Enter the formulas from the table in the next section into E4 through E16, adjusting A2:A21 and B3:B21 to match the length of your data.

The Cpk Formula in Excel, Cell by Cell

Assuming 20 readings in A2:A21, these are the exact formulas. Copy them as written, then change the ranges if you have more or fewer rows.

Cell What it holds Excel formula
E4 Sample size n =COUNT(A2:A21)
E5 Process mean =AVERAGE(A2:A21)
E6 Overall (sample) standard deviation =STDEV.S(A2:A21)
E7 Average moving range =AVERAGE(B3:B21)
E8 Within standard deviation =E7/1.128
E9 Cp =(E3-E2)/(6*E8)
E10 Cpu =(E3-E5)/(3*E8)
E11 Cpl =(E5-E2)/(3*E8)
E12 Cpk =MIN(E10,E11)
E13 Pp =(E3-E2)/(6*E6)
E14 Ppu =(E3-E5)/(3*E6)
E15 Ppl =(E5-E2)/(3*E6)
E16 Ppk =MIN(E14,E15)

If you only want a single number and don’t care about the helper cells, this one formula returns Cpk directly, using the within standard deviation in E8: =MIN((E3-AVERAGE(A2:A21))/(3*E8),(AVERAGE(A2:A21)-E2)/(3*E8)). Swap E8 for STDEV.S(A2:A21) and the same formula returns Ppk instead.

The constant 1.128 is d2 for a subgroup of size two, which is what a moving range between consecutive readings is. NIST’s section on individuals control charts uses exactly that number when it converts the average moving range into a sigma estimate. Readings collected in subgroups (five parts every hour, say) use a different constant and a different range, covered further down.

A Worked Cpk Example with a Real Dataset

This worked example uses 20 turned shaft diameters, measured with a micrometer, against a drawing spec of 25.00 millimeters (mm) plus or minus 0.10 mm, so LSL = 24.90 and USL = 25.10. Readings are listed in production order, which is why the moving range column makes sense.

Row Diameter (mm) Moving range Row Diameter (mm) Moving range
2 25.02 12 25.01 0.01
3 25.03 0.01 13 25.03 0.02
4 25.01 0.02 14 25.06 0.03
5 25.04 0.03 15 25.04 0.02
6 25.02 0.02 16 25.03 0.01
7 25.00 0.02 17 25.05 0.02
8 25.03 0.03 18 25.02 0.03
9 25.05 0.02 19 25.04 0.02
10 25.04 0.01 20 25.03 0.01
11 25.02 0.02 21 25.05 0.02

Step 1: Mean and the Two Standard Deviations

=AVERAGE(A2:A21) returns 25.031 mm. Already that tells you something: the process is running 0.031 mm above the nominal 25.00, using nearly a third of the upper half of the tolerance before any variation is counted.

=STDEV.S(A2:A21) returns 0.01553 mm. That’s the overall standard deviation, and it goes into the Pp and Ppk cells.

The 19 moving ranges in B3:B21 add up to 0.37, so =AVERAGE(B3:B21) returns 0.01947. Dividing by 1.128 gives a within standard deviation of 0.01726 mm in E8. Notice it is a little larger than the overall figure here, which happens when consecutive readings jump around more than the long-run scatter would suggest. On a drifting process the opposite is normal.

Step 2: Cp, CPU, Cpl and Cpk

With the within sigma of 0.01726 in E8:

  • Cp = (25.10 minus 24.90) / (6 times 0.01726) = 0.20 / 0.1036 = 1.93
  • Cpu = (25.10 minus 25.031) / (3 times 0.01726) = 0.069 / 0.0518 = 1.33
  • Cpl = (25.031 minus 24.90) / (3 times 0.01726) = 0.131 / 0.0518 = 2.53
  • Cpk = MIN(1.33, 2.53) = 1.33

Running the same arithmetic with the overall sigma of 0.01553 gives Pp = 2.15, Ppu = 1.48, Ppl = 2.81 and Ppk = 1.48. Your E9 to E16 cells should show exactly these values, give or take rounding in the last digit.

Step 3: What the Numbers Say

A Cpk of 1.33 scrapes the common acceptance line. The Cp of 1.93 says the spread itself is comfortably small; the process could sit inside the tolerance with room to spare. Everything between 1.93 and 1.33 is centering. Plugging into the NIST relationship, k = (25.031 minus 25.00) / 0.10 = 0.31, and Cp(1 minus k) = 1.93 times 0.69 = 1.33, which matches. Move the mean back to 25.00 and Cpk would rise to about 1.93 with no change to the machine’s repeatability.

To estimate the fallout, Excel’s NORM.DIST does the job: =1-NORM.DIST(E3,E5,E8,TRUE)+NORM.DIST(E2,E5,E8,TRUE) returns the expected fraction outside both limits, assuming a normal distribution. For this dataset it comes to roughly 0.00003, or about 32 parts per million, nearly all of it over the upper limit. NIST’s reference table gives 64 ppm for a centered process at Cp 1.33, so a shifted process at Cpk 1.33 landing in the same neighborhood is what you’d expect.

STDEV.S, STDEV.P or the Moving Range: Which Sigma Belongs in a Cpk

A true Cpk uses the within-subgroup sigma, the average moving range divided by 1.128 for individual readings or R-bar over d2 for subgrouped data, not STDEV.S or STDEV.P. Three candidate standard deviations exist, and each one produces a different number from the same column of data.

What Each Excel Function Actually Does

Microsoft’s reference page for STDEV.S says the function estimates standard deviation based on a sample, using the n minus 1 method, and that empty cells, text and logical values inside a referenced range are ignored. STDEV.P, by contrast, assumes its arguments are the entire population and uses the n method, so it always returns a slightly smaller number. On the shaft data STDEV.P gives 0.01513 against STDEV.S’s 0.01553, which nudges Ppk from 1.48 up to 1.52. A capability study is a sample of an ongoing process by definition, so STDEV.P is the wrong choice for either index, even though it looks like the natural pick for a complete column of numbers.

The moving range route doesn’t use a built-in deviation function at all. Its estimate comes from the average distance between consecutive readings, which captures only short-term, part-to-part variation and excludes slow drift, tool wear, or batch-to-batch shifts. That is the “within” sigma.

Who Says What, and Who to Believe

Bill McNeese’s Process Capability Part 3 article at SPC for Excel is blunt: the only difference between Cpk and Ppk is how the standard deviation is calculated. Cpk uses sigma estimated from a control chart (average range divided by d2, or average moving range with d2 = 1.128), while Ppk uses the calculated standard deviation of the individual values, “the value you obtain using Microsoft Excel’s built-in function STDEV.” QI Macros’ formula page draws the same line, listing pooled standard deviation, s-bar over c4, and R-bar over d2 as the acceptable sigma estimators for Cp and Cpk, and reserving plain standard deviation for Pp and Ppk. Minitab follows the same convention, which is why a January 2023 Microsoft Q&A poster comparing their own Excel code with Minitab saw Minitab report Cpk 1.07 and Ppk 1.28 from one dataset while their code returned a single 1.28 for both.

The NIST handbook, meanwhile, writes its sample estimator of Cpk with the plain sample standard deviation s and never mentions Ppk. For the definition of the index, NIST is the more authoritative source, and its formula is the one to quote. On the within-versus-overall naming convention, the SPC software vendors and Minitab reflect working practice in manufacturing quality departments, so that is the convention to follow if a customer’s supplier quality engineer will read your spreadsheet. The safe habit is to calculate both, label them honestly, and be able to say which sigma sits underneath each one.

Cpk, Cp, Pp and Ppk Compared

Cp and Cpk use the within, short-term sigma, while Pp and Ppk use the overall, long-term sigma, and only Cpk and Ppk account for centering. A mislabeled cell mixing these up is the fastest way to lose an argument with a customer auditor.

Index Accounts for centering Sigma used Excel source of sigma Answers the question
Cp No Within (short-term) =AVERAGE(moving ranges)/1.128 or R-bar/d2 Could the spread fit the tolerance if centered
Cpk Yes Within (short-term) Same as Cp Does the process fit right now, given where it’s centered
Pp No Overall (long-term) =STDEV.S(all readings) Could the total historical spread fit if centered
Ppk Yes Overall (long-term) =STDEV.S(all readings) How did the process actually perform over the period

McNeese’s advice is that if the process is in control, Cpk and Ppk will be essentially the same, and a large gap between them is itself a diagnostic: it means the process moves around between readings more than it scatters within them. QI Macros states that a value above 1.33 is desirable for both Cpk and Ppk, and Hessing notes that Six Sigma programs push for 1.50 on all four to reach a 3.4 ppm defect target.

Subgrouped Data: R-bar over d2 Instead of the Moving Range

Individual readings are the common case in a small shop, but if you sample in subgroups (five consecutive parts every 30 minutes, for instance) the within sigma comes from the average of the subgroup ranges divided by the d2 constant for that subgroup size. NIST’s page on Shewhart X-bar and R charts gives the relation sigma = R-bar / d2 and tables the related A2, D3 and D4 factors for n = 2 to 10. QI Macros notes that R-bar/d2 is used for subgroup sizes up to four, s-bar/c4 above four, and pooled standard deviation whenever n is greater than two. In Excel, lay the subgroups out one per row, compute each row’s range with =MAX(B2:F2)-MIN(B2:F2), average that column, then divide by the d2 for your n. The Cp, Cpu, Cpl and Cpk formulas are unchanged; only the E8 cell changes.

How to Read the Cpk Value You Get

A Cpk is a count of how many “three sigma” widths fit between the process mean and the nearest limit. Values below 1.0 mean the three-sigma edge of the distribution has already crossed a limit. The interpretation column in the table below follows Hessing’s guide, and the reject rates are from NIST’s process capability table, which assumes a normal, centered process (so they apply to Cp, and to Cpk only when the process is centered).

Cpk (centered) Rejects (NIST, centered normal process) Interpretation
Below 0 Over half out of spec on one side Mean is outside the specification limits
1.00 0.27 percent Three-sigma tails just touch the limits
1.33 64 ppm Common customer minimum; 75 percent of the tolerance used
1.66 0.6 ppm 60 percent of the tolerance used
2.00 2 ppb Process width could double before failing; 50 percent of tolerance used

Hessing’s guide describes a Cpk of 0.5 as a process with a high likelihood of hitting a specification boundary, 2.0 as one that could double its width before failing, and 3.0 as one that could triple it. Those descriptions are useful because they turn an abstract ratio into a physical margin.

Common Mistakes and What to Do if the Number Looks Wrong

Common causes are the wrong standard deviation for Cpk, text-formatted numbers, a misaligned moving range column, units typed into a limit cell, too few readings, and non-normal data.

Excel and Minitab Disagree

The Microsoft Q&A thread from January 2023 is a textbook case. That poster’s code used one standard deviation for Cp and Cpk and for Pp and Ppk, so Cpk and Ppk came out identical at 1.28 while Minitab reported 1.07 and 1.28. Minitab’s Cpk was lower because it used a within estimate that was larger than the overall one on that data. If your Cpk matches the software’s Ppk, you have used STDEV.S where the software used the moving range or R-bar/d2. Change E8 and the numbers should line up.

Numbers Stored as Text

Readings pasted from a gauge printout or a PDF often arrive as text. AVERAGE and STDEV.S silently ignore text inside a range, per Microsoft’s STDEV.S page, so the mean and sigma get calculated on a subset of the data with no error message. Check that =COUNT(A2:A21) in E4 equals the number of rows you expect; if it comes up short, select the column and use Data, Text to Columns, Finish, or multiply the column by 1 in a helper column.

The Moving Range Column Is Misaligned

The formula in B3 must reference A3 and A2, not A2 and A1 (the header). A one-row offset drags the header into the ABS calculation and produces a #VALUE error, or, if you started the formula in B2, an oversized first range. Confirm that the moving range column has exactly one fewer entry than the data column.

Limits Typed with Units or as a Range

E2 and E3 must be numbers. “24.90 mm” or “24.90 to 25.10” in a single cell returns a #VALUE error in every downstream formula. One-sided specifications are a related trap: with only an upper limit, report Cpu alone and leave Cpl out rather than typing a made-up LSL, because the MIN in E12 would otherwise pick whichever side you invented.

Too Few Readings or an Unstable Process

Hessing’s guide suggests 30 readings may be sufficient when data are normal and the process is stable, and NIST’s control chart material is built around confirming stability first. A Cpk from eight parts pulled off one pallet is a guess, not a capability. Plot the readings as a simple line chart (Insert, Charts, Line) and look for a trend or a step before quoting the index. If the chart shows a shift partway through, calculate the indices separately for each stretch.

Non-normal Data

The formula assumes a bell-shaped distribution, as NIST states. Measurements bounded at zero (flatness, runout, surface finish, concentricity) often pile up near the bound and stretch out on one side, which inflates the standard deviation and understates the true capability. Excel’s SKEW function on the data column is a quick screen; a value far from zero is a signal to look at a transformation or a non-normal capability method in dedicated software rather than reporting the plain Cpk.

Using the Analysis ToolPak for the Descriptive Numbers

If you’d rather have Excel print the mean, standard deviation, minimum and maximum in one block, the Analysis ToolPak’s Descriptive Statistics tool does it. Microsoft’s help page for loading the add-in, which applies to Excel for Microsoft 365, Excel 2024 and Excel 2021 as of September 2026, gives the Windows path as File, Options, Add-Ins, then Excel Add-ins in the Manage box, Go, tick Analysis ToolPak, OK. On Mac it is Tools, Excel Add-ins, tick Analysis ToolPak, OK, then quit and reopen Excel. The Data Analysis command then appears at the right end of the Data tab. Its standard deviation is the sample (n minus 1) version, identical to STDEV.S, so it feeds Ppk, not Cpk; the moving range cell still has to be built by hand.

Frequently Asked Questions

Is There a Built-in CPK Function in Excel?

No. Excel has no CPK, CP or PPK function in any version, including Microsoft 365 as of September 2026. The index is assembled from AVERAGE, a standard deviation estimate and MIN, exactly as shown in the cell table above, or bought as an add-in such as SPC for Excel or QI Macros, both of which are commercial products from the vendors cited in this article.

Can Cpk Be Negative?

Yes. Cpk goes negative when the process mean sits outside one of the specification limits, because either USL minus the mean or the mean minus LSL becomes negative. Hessing’s guide describes a negative Cpk as a process regularly producing output outside the limits. Excel’s MIN will return the negative side correctly, so a minus sign in E12 is a real result, not a formula error.

What Cpk Value Do Customers Usually Require?

The most common contractual minimum is 1.33, cited by both QI Macros and Six Sigma Study Guide as the level most customers want, and NIST’s table equates a centered Cp of 1.33 to about 64 ppm rejects. Six Sigma programs often specify 1.50 or, for critical characteristics, 1.67 and above. Check the drawing note or the supplier quality manual for your specific customer rather than assuming.

Does the Order of the Readings Matter?

For Ppk, no, because STDEV.S treats the column as one set. With Cpk calculated from moving ranges, yes, since each range is the difference between consecutive parts in production order. Sorting the column smallest to largest would shrink every moving range, collapse the within sigma and produce a Cpk that looks spectacular and means nothing.

How Many Decimal Places Should Cpk Be Reported To?

Two is standard practice (1.33, not 1.3322594). The inputs rarely justify more: a micrometer reading to 0.01 mm and 20 samples don’t support a third decimal. Format E12 with two decimals through Home, Number, Increase Decimal or Decrease Decimal, but keep the underlying cell unrounded so downstream formulas stay exact.

Build the sheet once with the helper cells, keep the within sigma in E8 and the overall sigma in E6 clearly labeled, and you can answer any capability question a customer asks in a few seconds. When the two indices differ by a lot, look at a line chart of the readings before you look at anything else, because the gap almost always points to a process that is moving, not a spreadsheet that is wrong.

References

Author Profile

Eric Dawson
Eric Dawson
Eric Dawson is the writer and curious mind behind The Money Watch. Based in Columbus, Ohio, he has a habit of digging into everyday questions until he understands what is actually going on, whether the subject is technology, travel, shopping, food, business, consumer products, or one of life's oddly specific problems.

Rather than pretending to be an expert in everything he covers, Eric approaches each topic as a careful researcher and practical problem-solver. He favors original sources, clear explanations, and honest answers about uncertainty. His goal is simple: do the digging readers shouldn't have to do themselves and turn what he finds into useful information without wasting their time.