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.
- 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.
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.
- From the menu select Spread | Column | Temporary Missing Values.
- Click Find next to jump to the next missing value.
- Click Switch to reset it to normal.
- To reverse the exclusion, place the cursor in the cell and click the temporarily missing button again.
- From the menu select Spread | Column | Temporary Missing Values.
- Click Clear all then click Close.
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.