1. Home
  2. Excluding Individual Cells from Analysis (temporary missing values)

Excluding Individual Cells from Analysis (temporary missing values)

You can temporarily set individual cells to blank or ‘missing’ values to exclude them from an analysis. The cell values are retained in the spreadsheet but are set to missing in any calculations or statistical analysis. This can be useful, for example, in an analysis of variance where you may want to remove an outlier before running the analysis. If you used a filter to restrict out a row, the design could become unbalanced, but if you set a value to temporarily missing this would retain the balanced design.

Temporary missing values can also be set in the Bookmark Spreadsheet by Value dialog. A temporary missing value is just a special bookmark with a note where the first line starts with the text ‘*Missing’. As such you may add extra text to the end of this note to explain why you have excluded it from your statistical calculations.

The temporarily missing button  is located on the spreadsheet toolbar.

  1. Place the cursor inside the cell you want to exclude then click the temporarily missing button 

The cell text and background colour will change and a suffix of * will be appended to the cell contents.

Finding temporary missing values

In a large spreadsheet it can be tedious to track down by eye cells you’ve made temporarily missing. Instead you can jump between missing values and also switch their status from ‘missing’ to ‘normal’ as required.

Note: This procedure cannot find blank values in a spreadsheet: it can only locate values that have been deliberately set to temporarily missing.

  1. From the menu select Spread | Column | Temporary Missing Values.

  2. Click Find next to jump to the next missing value.
  3. Click Switch to reset it to normal.

Clearing a missing value

  1. To reverse the exclusion, place the cursor in the cell and click the temporarily missing button  again.

Clearing all missing values

  1. From the menu select Spread | Column | Temporary Missing Values.
  2. Click Clear all then click Close.

Customizing the colour of missing values

You can customize the cell background colour and text colour from the Tools | Spreadsheet Options Appearance tab. The items to change are Temporarily missing cells and Temporarily missing cells background.

Updated on March 4, 2019

Was this article helpful?