Skip to content

Home / Statistical Tools / Control Charts / Create Control Charts

Create Control Charts

From Excel click...

QXL Stat Tools Tab > Control Charts > any chart

Every control chart in Quantum XL is created the same way, through the same two windows. This page describes that shared path once. Each chart's own Options page then covers only what differs on that chart: which columns it asks for, which sigma estimators it offers, and what its panels are called.

The path is always:

  1. Choose a chart from the ribbon.
  2. The Data Selection window opens. Give it the cells to read.
  3. The chart dialog opens. Set the options across its tabs and press Finish.
  4. The chart is written to a new worksheet and the Control Chart pane opens beside it. Each chart has its own Task Pane page describing that pane.

Selecting your data

Selecting the data

Choosing any control chart from the ribbon opens the Data Selection window before the chart dialog appears. It is the same window for every chart.

Control Type Default What it does
Selected Range: Text box The block Quantum XL expands around the current selection The cells the analysis reads. It keeps tracking the Excel selection while the window is open, so you can select on the sheet instead of typing.
Shrink and expand button Button, tooltip "Shrink/Expand form" Expanded Collapses everything below the range box so you can see the sheet. It expands again on its own when you change the Excel selection.
Data in Columns Radio button Selected when the range has at least as many rows as columns, or when the range is inside an Excel Table Each selected column becomes one column of data.
Data in Rows Radio button Selected when the range has more columns than rows and is not inside an Excel Table Each selected row becomes one column of data. The block is transposed before the dialog opens.
One Group Data Radio button Not selected Treats the whole rectangular block as a single column of data, named by the Dataset Name box. Available on every control chart.
First Row/Column is Header Check box Checked when the range is inside an Excel Table with Data in Columns, or when the first cell holds text; unchecked otherwise Uses the first row (or first column, in Data in Rows) as the column names. Header text is cleaned of line breaks and tabs, trimmed, and truncated to 50 characters.
Exclude Data in Hidden Cells Check box Checked Drops hidden rows and hidden columns before the data reaches the analysis. Cells you have marked as outliers stay attached to the right values.
Dataset Name: Text box Dataset Names the single column of data in One Group Data mode. Maximum 30 characters, and the characters : \ / ? * [ ] are not allowed. Left blank it reverts to Dataset.
Help Button Opens the Data Selection help page, and falls back to the copy installed with Quantum XL if no internet connection is available.
Cancel Button Closes the window. The analysis never starts.
Next > Button Validates the selection and opens the chart dialog.

Two of these controls appear only in certain states:

  • Dataset Name: is visible only when One Group Data is selected.
  • First Row/Column is Header is hidden entirely when One Group Data is selected, because that mode ignores headers.

An illustration beside the orientation radios changes to match the orientation you pick. Nothing is validated while you type: the checks below all run when you press Next >.

What Data Selection checks

Pressing Next > runs these checks in order. Any failure returns you to the window with your entries still in place.

Condition Message
No range entered "Please select a range of cells for the analysis."
Header row hidden while First Row/Column is Header and Exclude Data in Hidden Cells are both on "Cannot omit hidden rows when first row contains headers. The header row is hidden."
Header column hidden, same combination, in Data in Rows "Cannot omit hidden columns when first column contains headers. The header column is hidden."
The range contains merged cells "The selection contains merged cells at {address}. Please unmerge cells before continuing."
Nothing selected at all "(Data in Columns) You must select 1 or more columns for this analysis." or "(Data in Rows) You must select 1 or more rows for this analysis."
Several separate areas selected, unequal heights (Data in Columns) "(Data in Columns) All non-contiguous areas must have the same number of rows."
Several separate areas selected, different starting rows (Data in Columns) "(Data in Columns) All non-contiguous areas must start at the same row."
Several separate areas selected, unequal widths (Data in Rows) "(Data in Rows) All non-contiguous areas must have the same number of columns."
Several separate areas selected, different starting columns (Data in Rows) "(Data in Rows) All non-contiguous areas must start at the same column."
Two selected areas overlap "(Data in Columns) Non-contiguous areas cannot overlap."
Illegal character in Dataset Name: "The characters :\/?*[] cannot be used as the name of a dataset."
Dataset Name: longer than 30 characters "The maximum length of the dataset name is 30 characters, the current length is N characters."

The two dataset-name checks apply only in One Group Data mode, the only mode in which that box is used. Merged cells are the one condition worth checking before you start: the analysis cannot read them at all, and unmerging is the only remedy.

How your data should be laid out

How the block is read

Quantum XL reads a rectangular block of cells and turns it into named columns. The three orientation choices in the Data Selection window decide how that block is read.

Orientation What the block becomes Use it when
Data in Columns Each selected column is one column of data Your measurements run down the sheet, one variable per column
Data in Rows Each selected row is one column of data Your measurements run across the sheet, one variable per row
One Group Data The whole block becomes a single column The values are spread over several columns but are all the same measurement

In the examples that follow, the right-hand column is an explanation. It is not a column you add to your worksheet.

One measurement column, fixed subgroup size

The most common layout. One column of measurements, and you tell Quantum XL how many consecutive rows make a subgroup by setting Constant size: with n = 3.

Thickness Lands on point
2.01 1
2.03 1
1.99 1
2.05 2
2.02 2
2.04 2
2.00 3
2.06 3

Rows are taken in sheet order. A trailing group of fewer than n rows is not charted at all, so the eight rows above with n = 3 give two points, and the last two rows are unused.

A column that names the subgroup

When the subgroup is already recorded in your data, select that column as the Subgroup ID column: instead of setting a size. Rows sharing a value form one point.

Batch Thickness Lands on point
A 2.01 1
A 2.03 1
A 1.99 1
B 2.05 2
B 2.02 2
C 2.04 3
C 2.00 3
C 2.06 3
C 2.02 3

Here the subgroups hold 3, 2 and 4 values. Unequal sizes are supported, and they change how the chart looks: the control limits step in where a subgroup is large and out where it is small, instead of running as straight lines. Any data type is accepted, because the values are compared as the text they display, so A and a are different subgroups.

One subgroup per row, spread across columns

When each row already holds a complete subgroup, choose Subgroups across columns and check every measurement column.

Cavity 1 Cavity 2 Cavity 3 Cavity 4 Lands on point
2.01 2.03 1.99 2.02 1
2.05 2.02 2.04 2.00 2
2.04 2.06 2.01 3

This mode is the one that changes what a second checked column means. In the other two modes each checked column produces its own separate chart. Here the checked columns are observations inside the same subgroup, and the whole selection produces exactly one chart. It needs at least two checked columns.

Blanks are allowed. Point 3 above has three values instead of four, which again makes the limits step at that point. A row left with only one usable value still plots a mean, but it has no range and no standard deviation, so that position is empty on the lower panel.

Counting rather than measuring

The attribute and rare-event charts do not use subgroups. They read a count, and most of them also need a denominator saying how large each sample was.

Chart What you select Denominator
p, np The column of defective units Sample Size, either a column or one constant
c, u The column of defects Inspection Size, either a column or one constant
G, T The column holding the value for each event None. Each point is one event
Sample Size Defectives Plotted proportion
100 4 0.04
120 3 0.025
90 6 0.067

A defectives count counts whole units, so a unit with three defects on it counts once and the count can never exceed the sample size for that row. A defects count counts defects, so one item may contribute several and the count may exceed the number of items inspected. A denominator that varies from row to row makes the limits step, exactly as an unequal subgroup size does.

The chart dialog

The tabs

The dialog is a set of tabs with one set of buttons underneath. Data is always first and always the tab the dialog opens on.

Tab What it holds
Data What is charted: the data source, the data types, the columns, and how rows are collected into points
Limits & Estimation How the center line and the control limits are worked out: the sigma method, the limit type and multiplier, historical values, the baseline, any transformation, and the phase splits
Rules & Display The out-of-control tests and what is drawn on the chart
Capability Specification limits and the capability summary. Present only on the charts whose page says so

Switching tabs never recalculates anything, so you can move between them freely and read your settings back. Only a change you make triggers the rebuild.

The Data tab

The Data tab

The Data tab is always the first tab, and it is where you choose what is charted. It holds, from the top:

  • The Excel and GroupBy radio buttons, which decide how the rows are divided.
  • A red message line that appears when a selection cannot be honored. The text clears itself after about five seconds, so read it when it appears.
  • The Data Types list, one row per column.
  • The column slot lists, which differ by chart family: what is measured or counted, the optional X-axis labels, and any extra role the chart needs.
  • The GroupBy region, visible only in GroupBy mode.
  • A metadata line at the bottom describing the column you last clicked.

Every slot list is a small scrolling box of check boxes. Three behaviors are the same in all of them:

  • A long column name is shortened with an ellipsis; hover it to see the full name.
  • A column that is not available is disabled, not hidden, so you can see that it exists and that something else is using it or that its data type does not fit.
  • A disabled check box does not say why it is disabled. The usual reason is that the column is already doing another job on this chart.

The type drop-downs inside these lists ignore the mouse wheel and let the list scroll instead, so scrolling past a drop-down cannot silently change a data type.

Excel or GroupBy

Option Default What it produces
Excel Selected The rows are used in sheet order. Each column you check produces its own chart.
GroupBy The rows are divided by the value of one or more grouping columns, and the same checked columns are charted once per group, so the number of charts is multiplied by the number of groups.

The GroupBy region is shown only while GroupBy is selected.

Switching back to Excel discards your GroupBy selection. The grouping columns are cleared, not remembered, so returning to GroupBy means building the list again. Nothing warns you before this happens.

Choosing GroupBy and leaving the order list empty is not caught on the Data tab. It fails when the chart is built, with "GroupBy mode selected but no GroupBy columns specified."

Data Types

The Data Types list shows every column with a drop-down holding the type Quantum XL guessed from its cells. Changing a type here changes it for the whole analysis. The drop-down always offers the same five choices, in this order:

Type Use it for
Continuous Measurements that can take any decimal value
Nominal Text categories
DateTime Cells holding a real Excel date and time
Count Whole numbers that cannot be negative, such as counts of defects or units
Integer Whole numbers, negative values allowed

Changing a type has three effects worth knowing before you use it:

  • If the new type is one the chart cannot measure, the column is disabled in the slot list and, if it was checked, it is unchecked for you.
  • If the new type is one the chart can measure, the column becomes available again.
  • The Invalid count on the metadata line is counted against the type currently chosen, so the same column reports a different Invalid count under a different type.

A type change also triggers the preview cycle described under "Why the dialog closes and reopens", so the worksheets are rewritten for the new type immediately.

Measurement Columns

Check the columns holding the measured values. This is the only required selection on the Data tab.

  • Accepted types: Continuous, Integer and Count. A Nominal or DateTime column is offered but disabled.
  • The first available column is checked for you when the dialog opens. Everything else starts cleared.
  • More than one column can be checked. On charts with no Subgroups group each checked column produces its own chart. On charts that have one, the subgroup mode decides whether a second checked column is a second chart or a second observation inside every subgroup.
  • Retyping a checked column to a type the chart cannot measure unchecks it for you.
  • A checked column is not available as the X-axis label, the split column, the subgroup ID, the part column, or a grouping column.

With nothing checked, the chart cannot be built. Pressing Finish in that state stops the analysis with a message naming the missing selection and reopens the dialog with your settings intact.

X-Axis Labels (Optional)

Check one column here to label the points along the horizontal axis. Any data type is accepted: dates, text, or numbers.

  • Nothing is checked by default, and leaving it empty is normal. The points are then numbered.
  • The list is drawn as check boxes but behaves as a single choice: checking a second column silently unchecks the first.
  • The choice only relabels the axis. It never changes what is plotted, how subgroups are formed, or how the limits are estimated.
  • A column used as the X-axis label is not available for any other job on the chart.

Subgroups

Subgroups

The Subgroups group decides how rows are collected into the points on the chart. It is three radio buttons, and the choice you make changes what the check boxes in the measurement list mean. Read this table before you check a second column.

Mode How a subgroup is formed Charts produced What a second checked column means Columns required
Constant size: Consecutive rows, n at a time, within each column One chart per checked column Another, separate chart 1
Subgroup ID column: Rows that share the same value in the ID column One chart per checked column, all using the same ID column Another, separate chart 1
Subgroups across columns Each row is one subgroup, and the checked columns are the observations inside it Exactly one chart Another observation inside every subgroup, not another chart 2

Constant size: is selected by default.

Details attached to each mode:

  • Constant size: shows the n = box beside it. Rows are taken in sheet order, and the number of points is the row count divided by n, discarding the remainder: a trailing group of fewer than n rows is not charted at all. With 52 rows and n set to 5 the chart has 10 points and the last two rows are unused. If a partly filled last subgroup matters, adjust the range instead. An X-axis label, if you chose one, is read from the first row of each subgroup.
  • Subgroup ID column: shows a drop-down beside it. Any data type is accepted, because the values are compared as the text they display. Leaving the mode releases the column and sets the drop-down back to empty, so returning to this mode means choosing the column again.
  • Subgroups across columns needs at least two checked columns. Choosing it with fewer raises "Subgroups across columns needs at least 2 selected measurement columns." in the red line. The message appears only at the moment you click the radio button: if you uncheck columns down to one afterwards, no message appears and Finish simply stops working. Its tooltip reads "Each row is one subgroup; every selected measurement column is a measurement within it."

The chart does not redraw while a mode is incomplete. Choosing Subgroup ID column: before picking a column, or Subgroups across columns with only one column checked, deliberately leaves the preview alone until the choice can be honored.

Subgroup size

The n = box beside Constant size: sets how many consecutive rows make one subgroup. Its tooltip reads "Number of consecutive observations per subgroup".

  • Type: whole number. Default: 2.
  • It is visible only while Constant size: is selected.
  • It is read when you leave the box, not as you type.

  • The lowest value it accepts depends on the chart, and a value outside the range is refused with a message box naming the range, after which the box shows the value it held before:

    The subgroup size must be a whole number from 2 to 100.

Chart Lowest n Why
Xbar-R, Xbar-S, I-MR-R/S 2 A subgroup of one has no range and no standard deviation, so these charts cannot be built from it
EWMA, CUSUM 1 Both are legitimate charts of individual values
  • The upper limit is 100, which is as far as the Shewhart constant tables reach. Earlier versions had no upper limit and pushed a value below the floor back to the floor without saying anything.
  • There is no check against how many rows you actually have. A value larger than your data passes the dialog and fails when the chart is built.
  • Non-numeric text is rejected by reverting the box when you leave it, again with no message.

If a value you typed is not the one showing after you click elsewhere, it was below the floor for this chart.

The floor differs by chart, and each chart's Options page states its own.

Splitting the data into groups

The GroupBy region

Visible only while GroupBy is selected. It has two lists and six buttons.

Control What it does
Available Items Every column that can be grouped on
> and >> Move the selected items, or all enabled items, into the order list
< and << Move items back out
GroupBy Order The grouping columns, numbered 1., 2., and so on
^ and v Move the highlighted item up or down

The order matters: the numbers show the nesting, so 1. divides the data first and each later column subdivides what the one above it produced. Every combination of values that actually occurs becomes one chart.

Two rules disable an entry in Available Items:

  • More than 100 distinct values. A column with more distinct values than that cannot be grouped on at all, because it would produce more charts than the report can hold. The restriction does not lift.
  • Already used elsewhere. A column that is already the thing being measured, the X-axis label, the split column, the subgroup ID, or the part column is disabled here until you release it from that job.

>> moves only the enabled items, so it skips a disabled entry without telling you. Removing a column from GroupBy Order puts it back in Available Items and clears any bucket you chose for it.

Grouping by a DateTime column buckets the rows by year, quarter, month, week or day. Those buckets are described on DateTime Grouping.

What the sheet records about your settings

The User input box sits in the top left of the sheet, bordered as one block, and holds in order:

  • the analysis title, the Quantum XL version and a link to the help site
  • Data source, naming the range or the GroupBy group the sheet was built from
  • the columns you selected, under their role headings
  • a Settings block stating the options that produced the sheet, and where the chart has out-of-control rules, a Rules in effect block under it

A control chart sheet carries no Calculation type row. A control chart always reads the worksheet, so a row saying so would state nothing about the run.

The User input box does not only echo the columns you picked. It also states the settings that produced the numbers below it, so a sheet opened a year later says what it was built from, and a colleague reading it does not have to ask you which options were on.

Two rules decide what appears there, and knowing them saves you reading the box the wrong way:

  • Every selector and checkbox is printed on every run, whether or not you changed it from the default. A reader who sees nothing cannot tell a default from a setting the product simply does not report, so the box states all of them.
  • A value you had to type is printed only when you typed it. Its absence is the answer: no historical mean row means the run did not use one.

There is a third rule worth knowing, because it stops the box from claiming more than the run did. A setting the engine did not actually use is not printed, even when the dialog offered it. A sheet showing only a dispersion panel lists no out-of-control rules 5 to 8, because those reach the location panel alone and never ran.

The Settings rows

The list below is the one shared by I-MR, Xbar-R, Xbar-S and I-MR-R/S. The other charts print what their own engine reads, which is often much less, and each chart's page says what its sheet carries.

The rows sit under a Settings heading inside the same bordered box, in this order. Rows marked when typed appear only if you entered a value.

Row What it reads
Subgroups Constant size followed by the size you set, By ID column or Across columns. Subgrouped charts only
Sigma estimate on an individuals chart Average moving range, Median moving range or Mean squared successive difference; on a subgrouped chart R-bar, S-bar or Pooled standard deviation
MR length the moving range length. Individuals charts only, and not on the mean squared successive difference method, which has no moving range
Nelson corr. On or Off. Same two conditions as MR length
Unbiased within On or Off
Unbiased overall On or Off
Transformation None, Box-Cox or Johnson
Box-Cox lambda the lambda, only when the transformation is Box-Cox and you overrode it
Control limits Shewhart, Manual or None
k (std devs) the limit multiple
Limits from All subgroups, or First N subgroups, with the count you set in place of N. A count of 0 reads All subgroups, because that is what the run used. A sheet saved before the dialog refused an empty First N box can hold that state, and earlier versions printed "First 0 subgroups" there, naming a baseline the chart never used.
Split limits None, By column or Manual break points
Break points the row numbers, comma separated, only with manual break points
Historical mean, Historical SD, Within SD, Between mean, Between SD when typed
Manual UCL 1, Manual center 1, Manual LCL 1, and the same for panels 2 and 3 when typed

Three charts add their own defining parameters at the top of the block, because those are the first thing a reader checks:

Chart Rows added first
EWMA EWMA weight
CUSUM Allowance (k), Decision (h), Target when typed, Use FIR, FIR headstart when the fast initial response is on, and Reset on signal

A CUSUM sheet prints no k (std devs) row and no rules at all: its engine raises the out-of-control flag straight from the accumulator against the decision interval, and never runs the rule detector.

Rules in effect

Below the settings, a Rules in effect heading lists one row per enabled rule that the engine actually applied to a panel on this sheet, with the thresholds the run used rather than the defaults. A rule you enabled but which reached no displayed panel is not listed, because listing it would state something about the run that did not happen.

The rows read as the rule does, so Rule 2 followed by 9 same side of center means nine consecutive points on one side flagged the run. Rule 1 has no threshold of its own: it flags at the control limits' own k, so it reads beyond 3 sigma on a three sigma chart.

A rule that did not run is not listed, and two things stop one running. Rules 5 through 8 read the zone lines, so they are left out on a dispersion panel, which has none, and on any chart drawn with the sigma multiplier at something other than 3, where no zone lines exist at all. On a Levey-Jennings chart the same is true of the three Westgard rules that read a zone: R-4s, 2-2s and 4-1s.

The worksheet is protected

Under the last block in column A, in small italic text, the sheet carries one note:

This worksheet is protected to prevent accidental changes. To unprotect it, select the Review tab and then Unprotect Sheet.

Every worksheet Quantum XL writes for an analysis is protected, and every one carries this note, in the same wording. It is there so that a click or a stray keystroke cannot overwrite a number the chart is drawn from, or move a chart off its anchor.

Formatting cells, columns and rows is still allowed while the sheet is protected, so you can widen a column or change a number format without unprotecting anything. Editing a value or a chart is not. For those, unprotect the sheet from Excel's Review tab as the note says; the protection carries no password, so it comes off without one being asked for.

Levey-Jennings lists the six Westgard rules by their own names rather than the Nelson set, because those are the rules its dialog offers and its engine runs.

Finishing, and reopening later

Why the dialog closes and reopens

The chart you see while the dialog is open is real, not a sketch. So when you commit a change on the Data tab, Quantum XL closes the dialog, rewrites the worksheets for the new settings, and reopens the dialog with everything you had set still in place. A brief flicker is that cycle, not an error, and nothing you entered is lost.

What counts as committing a change:

  • Checking or unchecking a column, choosing a mode, or moving a column into or out of GroupBy Order commits immediately.
  • Changing a data type commits immediately.
  • Typing in a numeric box commits when you leave the box, and only if the value actually changed after any limit was applied to it.

The cycle is deliberately held back while a choice cannot be honored, so the chart is never rebuilt from a half-made selection: choosing a subgroup ID mode before picking the ID column, or an across-columns mode with only one column checked, waits until the choice is complete.

Reopening the dialog later

Finish stores your dialog settings on the worksheet it creates, and Modify Existing Analysis reads them back so you can change one option without rebuilding the chart from scratch.

Column selections are restored by column name, not by position. Two consequences follow:

  • Renaming a column in your source data, or changing the header setting so the names become Col A, Col B and so on, means the stored selection no longer matches and that selection comes back empty.
  • Reordering or inserting columns is safe. The names still match, so the same columns are restored even though they have moved.

A stored grouping column is restored even if it currently looks unavailable, and is skipped only when it genuinely cannot be grouped on any more, such as when it now holds more than 100 distinct values.

See Also