Formatting cells based on statistical criteria

You can format cells in the worksheet based on a variety of statistical criteria. For example, you can apply red shading to cells in a column that contain outliers.

Outliers

Use this option to format cells that contain values that are unusually large or small.

  1. Do one of the following to open the dialog box:
    • Choose Data > Conditional Formatting > Statistical > Outlier.
    • Click in the column, then choose Editor > Conditional Formatting > Statistical > Outlier.
    • Click in the column, right-click, then choose Conditional Formatting > Statistical > Outlier.
  2. In Column, enter the column to apply the conditional formatting to. This option appears only if you opened the dialog box from the Data menu.
  3. In Format cells that contain outliers using this rule, select one of the following:
    • Boxplot: Format cells that contain values that are beyond the whiskers of a boxplot.
    • Standard deviations: Format cells that contain values that are more than the specified number of standard deviations away from the mean.
  4. From Style, select the format for the cells that meet the condition.
  5. If you selected Custom for the style, select a Background color, a Font Color, and a Font Style for the cell formatting.

Points that are out of control

Use this option to format cells that contain data for subgroups that differ from the process mean by more than three standard deviations. Data must be in chronological order.

Minitab identifies out-of-control points as follows:

Minitab estimates the standard deviation according to the options in Tools > Options > Control Charts and Quality Tools > Estimating Standard Deviation. Minitab applies the format to all cells in an out-of-control subgroup.

  1. Do one of the following to open the dialog box:
    • Choose Data > Conditional Formatting > Statistical > Out of Control.
    • Click in the column, then choose Editor > Conditional Formatting > Statistical > Out of Control.
    • Click in the column, right-click, then choose Conditional Formatting > Statistical > Out of Control.
  2. In Column, enter the column to apply the conditional formatting to. This option appears only if you opened the dialog box from the Data menu.
  3. From Data type, select the type of data in the column:
    • Continuous: Continuous measurements, such as the length of a part, or the temperature of an oven.
    • Attribute (defectives): Counts of defective items, such as the number of parts that are rejected. All values must be integers, and the subgroup size must be at least as large as the largest value in the column.
  4. In Subgroup size, do one of the following:
    • If your data are collected in subgroups, then enter the number of measurements for each subgroup. For example, if the data for each subgroup is recorded in 5 consecutive rows, then enter 5.
    • If your data are not collected in subgroups, then enter 1.
  5. From Style, select the format for the cells that meet the condition.
  6. If you selected Custom for the style, select a Background color, a Font Color, and a Font Style for the cell formatting.

Points that are out of specification

Use this option to format cells that contain values that are outside of the limits that you specify.

  1. Do one of the following to open the dialog box:
    • Choose Data > Conditional Formatting > Statistical > Out of Specification.
    • Click in the column, then choose Editor > Conditional Formatting > Statistical > Out of Specification.
    • Click in the column, right-click, then choose Conditional Formatting > Statistical > Out of Specification.
  2. In Column, enter the column to apply the conditional formatting to. This option appears only if you opened the dialog box from the Data menu.
  3. In Format cells that are outside of the specification limits, enter a Lower spec, an Upper spec, or both.
  4. From Style, select the format for the cells that meet the condition.
  5. If you selected Custom for the style, select a Background color, a Font Color, and a Font Style for the cell formatting.

Large residuals

Use this option to format the cells that contain response values that are associated with large standardized residuals. You must analyze the response variable with an appropriate linear model before you can apply this conditional formatting. The conditional formatting applies only to a model that is current. For more information, go to Which types of residuals are included in Minitab?.

  1. Do one of the following to open the dialog box:
    • Choose Data > Conditional Formatting > Statistical > Large Residual.
    • Click in the column, then choose Editor > Conditional Formatting > Statistical > Large Residual.
    • Click in the column, right-click, then choose Conditional Formatting > Statistical > Large Residual.
  2. In Column, enter the column to apply the conditional formatting to. This option appears only if you opened the dialog box from the Data menu.
  3. In Absolute value of standardized residual greater than, enter the threshold for formatting a cell. The default threshold is 2. Minitab formats all cells that correspond to standardized residuals that are greater than 2 or less than -2.
  4. From Style, select the format for the cells that meet the condition.
  5. If you selected Custom for the style, select a Background color, a Font Color, and a Font Style for the cell formatting.

Unusual Xs

Use this option to format cells that contain response values that are associated with unusual predictor values, as measured by leverage (Hi). You must analyze the response variable with an appropriate linear model before you can apply this conditional formatting. For more information, go to Unusual observations.

Minitab formats cells associated with leverage values that exceed 3p/n or .99, whichever is smaller, where p is the number of model terms (including the constant) and n is the number of observations.

  1. Do one of the following to open the dialog box:
    • Choose Data > Conditional Formatting > Statistical > Unusual X.
    • Click in the column, then choose Editor > Conditional Formatting > Statistical > Unusual X.
    • Click in the column, right-click, then choose Conditional Formatting > Statistical > Unusual X.
  2. In Column, enter the column to apply the conditional formatting to. This option appears only if you opened the dialog box from the Data menu.
  3. From Style, select the format for the cells that meet the condition.
  4. If you selected Custom for the style, select a Background color, a Font Color, and a Font Style for the cell formatting.
By using this site you agree to the use of cookies for analytics and personalized content.  Read our policy