Investigate Bottling SPC in Excel

Build an Excel SPC workbook to uncover hidden bottling-process variation.

Introduction

30 Second Summary

A page full of green pass results feels reassuring. A sudden process change can still hide inside those results.

In this project, you will build a statistical process control (SPC) investigation workbook in Microsoft Excel for 55 time-ordered bottle fills. You will use it to separate customer conformance from process stability before tracing sample 51 to a filler-pressure adjustment.

What You'll Build

You will present a workbook where all 55 bottles pass their specification check while paired control charts expose the hidden process signal at sample 51.

By the end of this project, you'll have:

  • A specification-check chart where you can see every bottle inside the customer specification limits, including the bottle that later triggers the investigation.
  • Paired Individuals chart plus Moving Range chart views that reveal the unusual change around sample 51 beside the filler-pressure event.
  • A defensible recommendation backed by a frozen baseline from samples 1 through 50 plus a carefully qualified screening Cpk estimate.
  • Secret Mission: Extend the monitor to a second shift with automatic alerts against the frozen historical limits.

Are there any prerequisites?

You only need basic computer skills plus access to Microsoft Excel on Windows. Every SPC idea and Excel formula is introduced in context.

Before We Start

Before you begin the investigation, lock in the distinction your workbook needs to prove. A bottle can meet customer requirements while its time-ordered process pattern still signals a change that needs investigation.

Set Up the SPC Workbook

A trustworthy SPC investigation depends on measurements staying in production order. A misplaced row can disconnect a signal from the event that caused it.

You'll use Microsoft Excel to preserve 55 fill measurements with their event log in one workbook.

A separate parameter block keeps the specification limits and control-chart constants in one visible place.

In this step, get ready to:
  • Create a saved workbook for the investigation.
  • Load 55 time-ordered fill measurements with their event log.
  • Add the specification limits and control-chart constants.
Create the investigation workbook
  • Press the Windows key to open the search bar.
  • Type Excel into the search bar.
  • Select Microsoft Excel from the search results.
  • Select Blank workbook.
  • Press Ctrl+S to save the workbook.
  • Choose a folder where you keep project files.
  • Enter Bottling_SPC.xlsx as the file name.
  • Select Save.

You'll see Bottling_SPC.xlsx in the Excel title bar.

  • Double-click the first worksheet tab at the bottom of the workbook.
  • Type SPC Investigation as the worksheet name.
  • Press Enter to apply the name.
  • Press Ctrl+S to save the renamed worksheet.

The first worksheet tab now reads SPC Investigation.

Why Excel for this investigation?

Excel keeps this first investigation inside a tool you already have. Entering the calculations manually will make later moving ranges and control limits visible.

Paste the time-ordered dataset

Time-ordered process data keeps each fill measurement attached to the production event recorded at that moment. The first block covers samples 1 through 28.

  • Select cell A1 on the SPC Investigation worksheet.
  • Paste the first dataset block into the worksheet using the tab-separated rows below:
Sample	Fill (mL)	Event
1	499.4	
2	500.1	
3	499.8	
4	500.5	
5	500.0	
6	499.6	
7	500.3	
8	499.9	
9	500.4	
10	499.7	
11	500.2	
12	499.5	
13	500.0	
14	500.6	
15	499.8	
16	500.1	
17	499.6	
18	500.3	
19	499.9	
20	500.4	
21	499.7	
22	500.2	
23	499.5	
24	500.1	
25	500.5	
26	499.9	
27	499.6	
28	500.2

What does this data block establish?

  • The headers place sample numbers in column A.
  • Fill measurements occupy column B in millilitres.
  • Column C preserves any event recorded beside a measurement.
  • Samples 1 through 28 remain in their original production sequence.
  • Press Ctrl+S to save the first dataset block.
  • Inspect row 29.

You'll see sample 28 in cell A29 with a fill of 500.2 in cell B29.

Did the data land in one column?

  • Undo the paste if every value appears in column A.
  • Select cell A1 again.
  • Copy the tab-separated block directly from the code box.

Ask for help if the columns still do not separate: Help me paste tab-separated data into three Excel columns.

The remaining rows complete the production sequence. They also preserve the pressure adjustment beside sample 51.

  • Select cell A30.
  • Paste the remaining dataset rows into the worksheet using the block below:
29	500.0	
30	500.4	
31	499.8	
32	500.3	
33	499.7	
34	500.1	
35	500.6	
36	499.9	
37	499.5	
38	500.2	
39	499.8	
40	500.4	
41	500.0	
42	499.6	
43	500.3	
44	499.7	
45	500.1	
46	500.5	
47	499.9	
48	500.2	
49	499.6	
50	500.0	
51	503.6	Filler pressure adjusted after changeover
52	500.1	Pressure returned to standard setting
53	499.7	
54	500.2	
55	499.8

Why preserve the event log?

Sample 51 records the pressure adjustment beside its fill measurement. Sample 52 records when the pressure returned to the standard setting.

This alignment lets you connect a later statistical signal to a specific process event.

  • Press Ctrl+S to save the complete dataset.
  • Inspect rows 52 and 53.

You'll see sample 51 with a fill of 503.6 in row 52. The next row contains sample 52 with the pressure-return event.

Are the final samples in the wrong rows?

  • Undo the second paste if sample 29 does not begin in row 30.
  • Select cell A30 before pasting the second block again.
  • Check that sample 55 ends in row 56.

Get help aligning the rows: Help me fix the row positions in my Excel dataset.

Add the process parameters

The parameter block gives later calculations one fixed place to read the customer limits and control-chart constants. Keeping these values together also makes the workbook easier to audit.

  • Select cell R1.
  • Paste the parameter block into the worksheet using the tab-separated rows below:
Parameter	Value
LSL	496
USL	504
d2	1.128
D3	0
D4	3.267

What do these parameters represent?

  • LSL stores the lower customer requirement of 496.
  • USL stores the upper customer requirement of 504.
  • d2 stores 1.128 for estimating process variation from two-point moving ranges.
  • D3 stores the lower range-chart factor of 0.
  • D4 stores the upper range-chart factor of 3.267.

Before you save and check, do you expect sample 51 to stay aligned with the pressure-adjustment event?

  • Press Ctrl+S to save the completed setup.
  • Inspect cells R1:S6.
  • Return to row 52.

You'll see LSL at 496 with USL at 504. The remaining parameter rows show d2 at 1.128, D3 at 0, and D4 at 3.267.

You've built the investigation's traceable foundation. Sample 51 still shows 503.6 beside Filler pressure adjusted after changeover.

Did the parameter block overwrite other cells?

  • Undo the paste if the parameter headers do not begin in cell R1.
  • Select cell R1 before pasting the block again.
  • Check that D4 ends in cell R6 with 3.267 in cell S6.

Get help placing the block correctly: Help me fix my Excel parameter block.

✔️ Awesome, I've got everything!

Your saved workbook contains all 55 measurements with their events. The parameter block is ready for the calculations ahead.

ⓧ I'd like to double check the workbook

The finished workbook setup has four parts.

  • Confirm the workbook is saved as Bottling_SPC.xlsx.
  • Confirm the first worksheet is named SPC Investigation.
  • Confirm A1:C56 contains the three headers with samples 1 through 55.
  • Confirm sample 51 has fill 503.6 with the filler-pressure adjustment event.
  • Confirm sample 52 has the event Pressure returned to standard setting.
  • Confirm R1:S6 contains LSL at 496 with USL at 504.
  • Confirm the parameter block contains d2 at 1.128, D3 at 0, and D4 at 3.267.

Your source data is saved with its process history intact. Next, you'll check every fill against the customer specification.

Check Bottles Against Specifications

Your workbook now holds 55 bottle measurements in collection order. The event log preserves what happened around each reading.

Customer specification limits define acceptable fill volume. A full set of passing results can still leave statistical stability unanswered.

In this step, get ready to:
  • Add a specification result to every bottle measurement.
  • Plot the fill measurements against the customer limits.
  • Document what the pass or fail result proves.
Add the specification check

Your event log currently occupies column C. Three new columns preserve those events while making room for the specification check.

  • Drag across column headers C through E to select three columns.
  • Right-click the selected column headers.
  • Select Insert to add three blank columns before Event.
  • Click cell C1.
  • Paste the following block into C1:
LSL	USL	Specification Result
=$S$2	=$S$3	=IF(AND(B2>=C2,B2<=D2),"PASS","FAIL")

What do these formulas do?

  • The formula in C2 reads the lower specification limit from S2.
  • The formula in D2 reads the upper specification limit from S3.
  • The dollar signs keep both parameter references fixed when you fill the formulas down.
  • The formula in E2 returns PASS when the fill is at or inside both limits.
  • Select the range C2:E2.
  • Drag the fill handle at the bottom-right corner of the selection down through row 56.
  • Press Ctrl+S to save Bottling_SPC.xlsx.

Before you check sample 51, do you expect its fill of 503.6 mL to fall inside or outside the customer specification limits?

  • Click cell E52 to check the result for sample 51.

You'll see PASS. Sample 51 meets the customer requirement despite the pressure-adjustment event recorded on the same row.

Not seeing PASS in E52?

  • Check that the original Event column moved to column F before you added the formulas.
  • Check that C52 displays 496.
  • Check that D52 displays 504.

Help me troubleshoot the specification formula in my Excel workbook.

Build the Specification Check chart

A line chart keeps the samples in time order. Plotting the fills with both specification limits makes conformance visible across the full run.

  • Select the range A1:D56.
  • Click Insert in the ribbon.
  • Click Insert Line or Area Chart.
  • Select Line with Markers.
  • Click the chart title.
  • Type Specification Check.

Before you inspect sample 51, where do you expect its point to sit relative to the LSL and USL lines?

  • Locate sample 51 on the chart's horizontal axis.

You'll see its fill measurement below the USL line. Every other fill measurement also remains between the LSL and USL lines.

  • Press Ctrl+S to save the chart.

Chart showing the wrong data?

  • Check that the selected range is A1:D56.
  • Check that the chart legend lists Fill (mL), LSL, and USL.
  • Check that the chart title reads Specification Check.

Help me fix the data series in my Excel line chart.

Record the conformance finding

Product conformance describes whether each observed bottle meets its specification. The chart cannot establish whether the process pattern stayed consistent over time.

  • Place the conclusion beside the chart by entering this statement:
All observed bottles meet specification, but this chart does not establish statistical stability.

Why record this conclusion?

The first clause records the conformance result from the observed data. The second protects the investigation from an unsupported stability claim.

  • Press Ctrl+S to save the conclusion.

Before the final scan, how many FAIL results do you expect the completed column to contain?

  • Scan E2:E56 for any result other than PASS.
  • Inspect the chart from sample 1 through sample 55.

You'll see PASS in every cell from E2 through E56, including sample 51 in E52. You'll also see every fill measurement between the LSL and USL lines.

✔️ Awesome, I've got everything!

Your specification formulas cover all 55 samples. Your chart shows the fill measurements between the customer limits.

ⓧ I'd like to double check the full code

Sample	Fill (mL)	LSL	USL	Specification Result
1	499.4	=$S$2	=$S$3	=IF(AND(B2>=C2,B2<=D2),"PASS","FAIL")
2	500.1	=$S$2	=$S$3	=IF(AND(B3>=C3,B3<=D3),"PASS","FAIL")
3	499.8	=$S$2	=$S$3	=IF(AND(B4>=C4,B4<=D4),"PASS","FAIL")
4	500.5	=$S$2	=$S$3	=IF(AND(B5>=C5,B5<=D5),"PASS","FAIL")
5	500.0	=$S$2	=$S$3	=IF(AND(B6>=C6,B6<=D6),"PASS","FAIL")
6	499.6	=$S$2	=$S$3	=IF(AND(B7>=C7,B7<=D7),"PASS","FAIL")
7	500.3	=$S$2	=$S$3	=IF(AND(B8>=C8,B8<=D8),"PASS","FAIL")
8	499.9	=$S$2	=$S$3	=IF(AND(B9>=C9,B9<=D9),"PASS","FAIL")
9	500.4	=$S$2	=$S$3	=IF(AND(B10>=C10,B10<=D10),"PASS","FAIL")
10	499.7	=$S$2	=$S$3	=IF(AND(B11>=C11,B11<=D11),"PASS","FAIL")
11	500.2	=$S$2	=$S$3	=IF(AND(B12>=C12,B12<=D12),"PASS","FAIL")
12	499.5	=$S$2	=$S$3	=IF(AND(B13>=C13,B13<=D13),"PASS","FAIL")
13	500.0	=$S$2	=$S$3	=IF(AND(B14>=C14,B14<=D14),"PASS","FAIL")
14	500.6	=$S$2	=$S$3	=IF(AND(B15>=C15,B15<=D15),"PASS","FAIL")
15	499.8	=$S$2	=$S$3	=IF(AND(B16>=C16,B16<=D16),"PASS","FAIL")
16	500.1	=$S$2	=$S$3	=IF(AND(B17>=C17,B17<=D17),"PASS","FAIL")
17	499.6	=$S$2	=$S$3	=IF(AND(B18>=C18,B18<=D18),"PASS","FAIL")
18	500.3	=$S$2	=$S$3	=IF(AND(B19>=C19,B19<=D19),"PASS","FAIL")
19	499.9	=$S$2	=$S$3	=IF(AND(B20>=C20,B20<=D20),"PASS","FAIL")
20	500.4	=$S$2	=$S$3	=IF(AND(B21>=C21,B21<=D21),"PASS","FAIL")
21	499.7	=$S$2	=$S$3	=IF(AND(B22>=C22,B22<=D22),"PASS","FAIL")
22	500.2	=$S$2	=$S$3	=IF(AND(B23>=C23,B23<=D23),"PASS","FAIL")
23	499.5	=$S$2	=$S$3	=IF(AND(B24>=C24,B24<=D24),"PASS","FAIL")
24	500.1	=$S$2	=$S$3	=IF(AND(B25>=C25,B25<=D25),"PASS","FAIL")
25	500.5	=$S$2	=$S$3	=IF(AND(B26>=C26,B26<=D26),"PASS","FAIL")
26	499.9	=$S$2	=$S$3	=IF(AND(B27>=C27,B27<=D27),"PASS","FAIL")
27	499.6	=$S$2	=$S$3	=IF(AND(B28>=C28,B28<=D28),"PASS","FAIL")
28	500.2	=$S$2	=$S$3	=IF(AND(B29>=C29,B29<=D29),"PASS","FAIL")
29	500.0	=$S$2	=$S$3	=IF(AND(B30>=C30,B30<=D30),"PASS","FAIL")
30	500.4	=$S$2	=$S$3	=IF(AND(B31>=C31,B31<=D31),"PASS","FAIL")
31	499.8	=$S$2	=$S$3	=IF(AND(B32>=C32,B32<=D32),"PASS","FAIL")
32	500.3	=$S$2	=$S$3	=IF(AND(B33>=C33,B33<=D33),"PASS","FAIL")
33	499.7	=$S$2	=$S$3	=IF(AND(B34>=C34,B34<=D34),"PASS","FAIL")
34	500.1	=$S$2	=$S$3	=IF(AND(B35>=C35,B35<=D35),"PASS","FAIL")
35	500.6	=$S$2	=$S$3	=IF(AND(B36>=C36,B36<=D36),"PASS","FAIL")
36	499.9	=$S$2	=$S$3	=IF(AND(B37>=C37,B37<=D37),"PASS","FAIL")
37	499.5	=$S$2	=$S$3	=IF(AND(B38>=C38,B38<=D38),"PASS","FAIL")
38	500.2	=$S$2	=$S$3	=IF(AND(B39>=C39,B39<=D39),"PASS","FAIL")
39	499.8	=$S$2	=$S$3	=IF(AND(B40>=C40,B40<=D40),"PASS","FAIL")
40	500.4	=$S$2	=$S$3	=IF(AND(B41>=C41,B41<=D41),"PASS","FAIL")
41	500.0	=$S$2	=$S$3	=IF(AND(B42>=C42,B42<=D42),"PASS","FAIL")
42	499.6	=$S$2	=$S$3	=IF(AND(B43>=C43,B43<=D43),"PASS","FAIL")
43	500.3	=$S$2	=$S$3	=IF(AND(B44>=C44,B44<=D44),"PASS","FAIL")
44	499.7	=$S$2	=$S$3	=IF(AND(B45>=C45,B45<=D45),"PASS","FAIL")
45	500.1	=$S$2	=$S$3	=IF(AND(B46>=C46,B46<=D46),"PASS","FAIL")
46	500.5	=$S$2	=$S$3	=IF(AND(B47>=C47,B47<=D47),"PASS","FAIL")
47	499.9	=$S$2	=$S$3	=IF(AND(B48>=C48,B48<=D48),"PASS","FAIL")
48	500.2	=$S$2	=$S$3	=IF(AND(B49>=C49,B49<=D49),"PASS","FAIL")
49	499.6	=$S$2	=$S$3	=IF(AND(B50>=C50,B50<=D50),"PASS","FAIL")
50	500.0	=$S$2	=$S$3	=IF(AND(B51>=C51,B51<=D51),"PASS","FAIL")
51	503.6	=$S$2	=$S$3	=IF(AND(B52>=C52,B52<=D52),"PASS","FAIL")
52	500.1	=$S$2	=$S$3	=IF(AND(B53>=C53,B53<=D53),"PASS","FAIL")
53	499.7	=$S$2	=$S$3	=IF(AND(B54>=C54,B54<=D54),"PASS","FAIL")
54	500.2	=$S$2	=$S$3	=IF(AND(B55>=C55,B55<=D55),"PASS","FAIL")
55	499.8	=$S$2	=$S$3	=IF(AND(B56>=C56,B56<=D56),"PASS","FAIL")

What does this reference show?

This reference shows the finished A1:E56 range after the formulas have been filled down. Each row compares its own fill measurement with the fixed limits in S2 and S3.

That is your first important result: every observed bottle conforms to the customer limits. Next, you'll test whether the time-ordered process pattern contains a signal that those limits cannot show.

Reveal Instability with an Individuals Chart

In the previous step, your specification check in Microsoft Excel proved that every observed bottle sits inside the customer specification limits. That all-green result leaves the process pattern unanswered.

An Individuals chart plots each fill in time order against control limits derived from observed variation. In this step, you'll use consecutive moving ranges to expose the hidden special-cause variation.

In this step, get ready to:
  • Calculate consecutive moving ranges.
  • Calculate the all-data Individuals limits.
  • Plot an Individuals chart that exposes sample 51.
Calculate consecutive moving ranges

A moving range is the absolute difference between one fill measurement and the measurement immediately before it. Large values reveal sudden jumps between consecutive bottles.

  • In the worksheet from earlier, select cell F1.
  • Type Moving Range.
  • Keep cell F2 blank because sample 1 has no previous measurement.
  • Select cell F3.
  • Calculate the first moving range by entering this formula:
=ABS(B3-B2)

What Does This Formula Do?

  • The formula subtracts the previous fill in B2 from the current fill in B3.
  • The ABS function removes the sign so the moving range records the size of the change.
  • Each copied formula shifts to the next pair of consecutive measurements.
  • Press Enter.
  • Select cell F3 again.
  • Drag the fill handle down through cell F56.
  • Save Bottling_SPC.xlsx by pressing Ctrl+S.
  • Select cell F52 to inspect the jump into sample 51.

You should see 3.6 in F52. This is much larger than the moving ranges around the earlier fills.

Moving Range Looks Wrong?

  • Confirm that F2 is blank because the first sample has no previous value.
  • Check that the formula in F3 subtracts B2 from B3 before applying ABS.

Help me troubleshoot my moving range formulas in Excel.

Calculate the Individuals limits

The mean becomes the center line for the Individuals chart. The average moving range estimates the process variation used to position the upper and lower limits.

Why Calculate This in Excel?

Manual formulas expose how moving ranges become control limits. You can inspect every calculation inside the workbook.

Purpose-built tools such as Minitab automate this workflow. Excel keeps this first investigation accessible while making the mechanics visible.

  • Select cell R7.
  • Add the all-data labels and formulas by pasting this tab-separated block into the selected cell:
Mean	=AVERAGE(B2:B56)
MR-bar	=AVERAGE(F3:F56)
I-UCL	=$S$7+3*$S$8/$S$4
I-LCL	=$S$7-3*$S$8/$S$4

How Are the Limits Calculated?

  • The formula in S7 calculates the mean of all 55 fill measurements.
  • The formula in S8 calculates the average of the consecutive moving ranges.
  • The formula in S9 places the I-UCL three estimated standard deviations above the mean.
  • The formula in S10 places the I-LCL three estimated standard deviations below the mean.
  • Save Bottling_SPC.xlsx by pressing Ctrl+S.
  • Select cells S7:S10 to inspect the calculated values.

You should see a mean near 500.069 and an I-UCL near 501.744. The I-UCL sits below sample 51's fill of 503.6.

Control Limits Not Calculating?

  • Confirm that the formulas landed in S7:S10 beside the labels in R7:R10.
  • Check that the moving range formulas extend through F56.
  • Verify that the formulas use the existing d2 value in S4.

Help me troubleshoot my Individuals control-limit formulas.

Plot the Individuals chart

The chart block keeps the time-ordered fills beside repeated center-line and limit values. A line chart turns those columns into a direct comparison between each measurement and the expected process band.

  • Select cell L1.
  • Create the chart block by pasting these headers and formulas into the selected cell:
Sample	Fill	Center Line	I-UCL	I-LCL
=A2	=B2	=$S$7	=$S$9	=$S$10

How Does the Chart Block Work?

  • The Sample column preserves the observation order on the horizontal axis.
  • The Fill column supplies the individual bottle measurements.
  • The absolute references repeat the calculated center line and control limits for every sample.
  • Select cells L2:P2.
  • Drag the fill handle down through row 56.
  • Select the completed range L1:P56.
  • Select Insert on the ribbon.
  • Open Insert Line or Area Chart.
  • Choose Line with Markers.
  • Select the chart title.
  • Type Individuals Chart.

Before you inspect the chart, do you think sample 51 will sit inside the process limits as it did inside the specification limits?

  • Inspect sample 51 against the I-UCL line.
  • Select cell E52 to compare the specification result.
  • Save Bottling_SPC.xlsx by pressing Ctrl+S.

You'll see sample 51 above the I-UCL on the Individuals chart. Cell E52 still reads PASS.

Chart Not Showing the Signal?

  • Confirm that the chart uses the full range L1:P56.
  • Check that cells N2:P56 contain repeated center-line and limit values.
  • Verify that the chart title reads Individuals Chart.

Help me troubleshoot my Individuals chart in Excel.

You found the instability hidden by the all-green specification check. Sample 51 passes the customer requirement while breaking the process's expected pattern.

The Individuals chart pinpoints a location signal at sample 51. Next, you'll use consecutive movement and the event log to confirm what happened.

Confirm and Investigate the Special Cause

Your Individuals chart has already exposed a change that the specification check missed. Sample 51 breaks the process pattern even though its fill result still reads PASS.

A Moving Range chart measures the jump between each pair of consecutive bottles. This paired chart can confirm special-cause variation and direct your investigation to the event log.

In this step, get ready to:
  • Calculate the upper and lower limits for the Moving Range chart.
  • Build a Moving Range chart from the time-ordered measurements.
  • Connect the chart signals to the filler-pressure event.
Calculate the Moving Range limits

Each moving range is the absolute difference between two consecutive fills. The chart uses the average moving range as its center line.

  • Switch back to the SPC Investigation worksheet from earlier.
  • Select cell R11.
  • Add the Moving Range limit labels and formulas by pasting this block:
MR-UCL	=$S$6*$S$8
MR-LCL	=$S$5*$S$8

How Do These Limits Work?

  • The MR-UCL formula multiplies the all-data average moving range by D4.
  • The MR-LCL formula uses D3, which equals 0 for two-observation ranges.
  • Absolute cell references keep both limits tied to the parameter block.
  • Inspect cells S11:S12 to confirm that both cells display numeric results.
  • Confirm that S12 displays 0.
  • Save Bottling_SPC.xlsx by pressing Ctrl+S.

Seeing Formula Text Instead of Values?

  • Check that each formula begins with an equals sign.
  • Set the affected cells to a numeric format if Excel treats the pasted content as text.
  • Confirm that D3 remains 0 in cell S5.

Help me fix my Moving Range limit formulas in Excel.

The chart needs a center line plus copies of both limits for every sample. Absolute references make those lines remain constant when you fill the formulas down.

  • Select cell H1.
  • Create the Moving Range limit block by pasting these headers and formulas:
MR Center	MR-UCL	MR-LCL
=$S$8	=$S$11	=$S$12

What Does This Block Do?

  • The MR Center column repeats the all-data average moving range.
  • The MR-UCL column repeats the upper limit from S11.
  • The MR-LCL column repeats the lower limit from S12.
  • Select cells H2:J2.
  • Drag the fill handle through row 56.
  • Inspect row 56 to confirm that all three limit columns contain values.
  • Save Bottling_SPC.xlsx by pressing Ctrl+S.

Do the Limits Change Down the Sheet?

  • Check that the formulas in row 2 contain dollar signs around the referenced cells.
  • Copy H2:J2 again if the fill handle started from a different row.

Help me correct limit lines that change by row.

Build the Moving Range chart

A dedicated source block keeps the sample numbers separate from the chart series. It also gives Excel one continuous range for the Moving Range chart.

  • Select cell U1.
  • Create the chart source block by pasting these headers and formulas:
Sample	Moving Range	MR Center	MR-UCL	MR-LCL
=A2	=F2	=H2	=I2	=J2

What Does This Chart Block Do?

  • The Sample column supplies the time-ordered horizontal axis.
  • The Moving Range column points to the consecutive differences in column F.
  • The remaining columns point to the repeated center line plus the upper and lower limits.
  • Cell V2 remains blank because the first bottle has no previous observation.
  • Select cells U2:Y2.
  • Drag the fill handle through row 56.
  • Inspect row 56 to confirm that the chart block extends through the final bottle.

Your source block now covers samples 1 through 55. The first Moving Range cell is blank by design.

  • Select the range U1:Y56.
  • Select the Insert tab in the Excel ribbon.
  • Select Insert Line or Area Chart.
  • Choose Line with Markers.
  • Select the chart title.
  • Enter Moving Range Chart.
  • Save Bottling_SPC.xlsx by pressing Ctrl+S.

Good work. Your workbook now has a second control chart with four plotted series across the bottle sequence.

Missing a Line on the Chart?

  • Confirm that the selected chart range is U1:Y56.
  • Check that columns W:Y contain values through row 56.
  • Verify that the chart type is Line with Markers.

Help me fix my Moving Range chart.

✔️ Awesome, I've got everything!

Your Moving Range formulas and chart source block match the required workbook structure.

ⓧ I'd like to double check the full code

MR-UCL	=$S$6*$S$8
MR-LCL	=$S$5*$S$8

MR Center	MR-UCL	MR-LCL
=$S$8	=$S$11	=$S$12

Sample	Moving Range	MR Center	MR-UCL	MR-LCL
=A2	=F2	=H2	=I2	=J2

How to Read This Reference

The first block belongs in R11:S12. The second starts in H1, while the third starts in U1.

Investigate the paired signals

One abnormal moving range can mark the start of a sudden change. A second abnormal range can appear when the process returns toward its earlier level.

Before you compare the charts, do you expect the pressure event to create one abnormal moving range or two?

  • Inspect samples 51 and 52 on the Moving Range Chart.
  • Compare both points with the MR-UCL line.
  • Inspect sample 51 on the Individuals Chart.
  • Compare that point with the I-UCL line.
  • Select cell E52 to confirm the specification result.
  • Inspect rows 52:53 across columns A:C.

You should see the moving ranges associated with samples 51 and 52 above the MR-UCL. Sample 51 also remains above the I-UCL while E52 still reads PASS.

Why Are There Two Moving Range Signals?

The moving range at sample 51 measures the upward jump from sample 50. The event log identifies Filler pressure adjusted after changeover at that point.

The moving range at sample 52 measures the return toward the earlier fill level. Its event log states Pressure returned to standard setting.

  • Select cell AA1.
  • Document the special-cause hypothesis by entering The pressure adjustment at sample 51 produced an unusual upward jump, followed by an unusual return at sample 52.
  • Leave the sample 51 observation unchanged.
  • Keep the all-data formulas in S7:S12 unchanged.
  • Save Bottling_SPC.xlsx by pressing Ctrl+S.

That completes the investigation. Your paired control charts now confirm the unusual jump and connect it to a recorded process event.

You have confirmed the special cause without deleting the evidence or recalculating around it. Next, you will freeze a stable baseline before estimating process capability.

Freeze the Baseline and Estimate Capability

Your Individuals chart exposed sample 51 as a process signal. Your Moving Range chart confirmed that the jump matched the pressure adjustment.

The current control limits include the pressure event. That special-cause variation inflates the limits used to judge the process.

You will use Microsoft Excel to freeze samples 1 through 50 as a frozen baseline. You will then calculate a screening Cpk without overstating what 50 synthetic observations can prove.

In this step, get ready to:
  • Calculate baseline statistics from samples 1 through 50.
  • Replace the chart-support formulas with frozen baseline references.
  • Document why the screening Cpk cannot qualify production.
Build the frozen baseline

A frozen baseline preserves the process pattern from a period judged to be stable. Samples 1 through 50 provide that working reference without allowing the pressure event to widen its own limits.

  • Select cell R14 in the parameter block.
  • Build the baseline block by pasting this table:
Baseline Mean	=AVERAGE(B2:B51)
Baseline MR-bar	=AVERAGE(F3:F51)
Baseline Sigma	=S15/$S$4
Screening Cpk	=MIN(($S$3-S14)/(3*S16),(S14-$S$2)/(3*S16))
Baseline I-UCL	=$S$14+3*$S$15/$S$4
Baseline I-LCL	=$S$14-3*$S$15/$S$4
Baseline MR-UCL	=$S$6*$S$15
Baseline MR-LCL	=$S$5*$S$15

What Do These Formulas Calculate?

  • Cell S14 calculates the mean fill from samples 1 through 50.
  • Cell S15 calculates the average moving range before the pressure event.
  • Cell S16 estimates baseline process sigma from the average moving range.
  • Cell S17 calculates the smaller capability estimate from the two specification boundaries.
  • Cells S18:S21 convert the stable baseline into frozen Individuals and Moving Range limits.
  • Press Ctrl+S to save Bottling_SPC.xlsx.
  • Select the range S17:S20.

You should see approximately 2.87 in S17. You should see approximately 501.397 in S18.

Cell S20 should show approximately 1.707.

Baseline Values Look Different?

  • Confirm the formula in S14 ends at B51.
  • Confirm the formula in S15 ends at F51.
  • Check that the formulas in S18:S21 retain their dollar signs.

Help me check why my frozen Excel baseline values differ from the expected results.

Freeze both chart baselines

Chart-support formulas determine which center lines appear across all 55 samples. Absolute references keep those lines anchored to the original stable period as later measurements arrive.

  • Select cell N2 in the L1:P56 chart block.
  • Replace the three chart-support formulas by pasting this row:
=$S$14	=$S$18	=$S$19

How Do These References Freeze the Chart?

  • Cell N2 locks the center line to the baseline mean in S14.
  • Cell O2 locks the upper Individuals limit to S18.
  • Cell P2 locks the lower Individuals limit to S19.
  • Use the fill handle to extend N2:P2 through row 56.
  • Inspect the Individuals Chart.

You will see sample 51 above the frozen I-UCL. Samples 52 through 55 sit inside the frozen Individuals limits.

  • Press Ctrl+S to save the frozen Individuals references.

Sample 51 Still Inside the Limit?

  • Confirm N2 references $S$14.
  • Confirm O2 references $S$18.
  • Confirm the new formulas reach row 56.

Help me find why sample 51 is not above the frozen I-UCL in my Individuals chart.

The Moving Range chart reads its limit values from columns H:J. Replacing those formulas updates the existing chart block in U:Y.

  • Select cell H2 in the H1:J56 limit block.
  • Replace the three Moving Range formulas by pasting this row:
=$S$15	=$S$20	=$S$21

How Do These References Update the Moving Range Chart?

  • Cell H2 locks the Moving Range center line to the baseline average in S15.
  • Cell I2 locks the Moving Range upper limit to S20.
  • Cell J2 locks the Moving Range lower limit to S21.
  • Use the fill handle to extend H2:J2 through row 56.
  • Inspect the Moving Range Chart.

You will see the moving ranges associated with samples 51 and 52 above the frozen MR-UCL.

  • Press Ctrl+S to save the frozen Moving Range references.

Moving Range Limits Did Not Update?

  • Confirm H2:J56 contains the frozen baseline references.
  • Confirm columns W:Y still reference columns H:J.

Help me find why my Moving Range chart is not using the frozen baseline limits.

Document the capability conclusion

Process capability compares a stable process with customer specification limits. The screening result needs clear boundaries because the baseline contains only 50 synthetic observations.

  • Add a conclusion box in a blank area of the SPC Investigation worksheet.
  • Paste this statement into the conclusion box:
Samples 1-50 form the working baseline. Sample 51 is a special-cause signal linked to a pressure adjustment. The baseline screening Cpk is high, but this is not a formal capability qualification because the synthetic dataset has only 50 baseline observations and normality was not formally tested. Collect at least 100 representative observations for a production capability study.

Why Qualify the Cpk Result?

Cpk assumes a stable process with normally distributed values. The workbook establishes a working stable period without formally testing normality.

Fifty baseline observations support a screening estimate. A production capability study generally needs at least 100 representative observations.

  • Press Ctrl+S to save the conclusion box.
  • Read the conclusion box from start to finish.

The box should preserve the complete recommendation without missing or truncated text.

Which sample do you expect to stay outside the frozen Individuals limits after recalculation?

  • Recalculate all changed formulas by pressing F9.
  • Inspect the range S17:S20.
  • Inspect the Individuals Chart.

Cell S18 is approximately 501.397. Cell S20 is approximately 1.707.

Cell S17 is approximately 2.87. Sample 51 remains above the frozen I-UCL.

Samples 52 through 55 remain inside the frozen Individuals limits. That's the hard judgment done: your workbook now separates the stable baseline from the event it must detect.

Final Results Do Not Match?

  • Confirm the baseline formulas use samples 1 through 50 only.
  • Confirm N2:P56 uses the frozen Individuals references.
  • Confirm H2:J56 uses the frozen Moving Range references.

Help me troubleshoot why my frozen baseline values or control-chart signals do not match the expected results.

Secret mission

Monitor a Second Shift with Automatic Alerts

Your frozen baseline has caught one special cause. Extend the workbook to a second shift. Make it flag new control-limit violations automatically.

Clean Up Your Resources

Clean Up Your Resources

Choose whether to keep Bottling_SPC.xlsx available, close it for later, or delete it. Your work lives in a local Microsoft Excel workbook, so keeping it creates no usage-based cloud charges.

Resources you used:

  • One local Bottling_SPC.xlsx workbook containing the completed SPC investigation.

Keep everything running

No action needed. Choose this if you want to keep testing new shifts against the frozen baseline.

  • Keep Bottling_SPC.xlsx in its current saved location.
  • Keep cells R14:S21 unchanged when you add future observations.
  • Use the existing alerts in column K to test each new observation against the frozen limits.

Pause - I'll come back to this later

This option gives you a stopping point while preserving every formula, chart, or alert for later.

  • Save Bottling_SPC.xlsx by pressing Ctrl+S.
  • Close Bottling_SPC.xlsx in Microsoft Excel.

That gives you a clean stopping point. Your full SPC investigation remains ready for the next session.

Delete - I don't want to use this again

Deleting Bottling_SPC.xlsx removes the only project file described in this section. Choose Pause if you want to preserve your charts or conclusion.

  • Close Bottling_SPC.xlsx in Microsoft Excel.
  • Return to the folder in Windows where you saved Bottling_SPC.xlsx.
  • Select Bottling_SPC.xlsx.
  • Delete the selected workbook.
  • Remove the workbook from the Windows deleted-files location to complete the deletion.
  • Search the original saved folder for Bottling_SPC.xlsx.

You should see no matching workbook in that folder. Your local project file is now removed.

Nice Work!

Nice Work!

That’s a wrap! Your Microsoft Excel workbook now turns time-ordered bottle fills into a complete statistical process control investigation.

You've learned how to:

  • Build a specification check that proves every observed bottle meets the customer specification limits.
  • Read paired Individuals charts and Moving Range charts to expose special-cause variation hidden by pass or fail inspection. Connect the signals at samples 51 and 52 to the filler-pressure event log.
  • Freeze a stable baseline from samples 1 through 50. Calculate a preliminary Cpk estimate without presenting it as a formal production qualification.
  • Complete the optional Secret Mission by extending the workbook to a second shift. Use automatic control alerts to flag samples 60 and 61 against the frozen limits.

Ready to quiz yourself?