PopTools logo

Hide error values

This routine hides the results of formulas that return error values. It replaces formulas in the active worksheet that return errors by formulas that return a blank string when an error would normally result. For instance the formula:


will be replaced by:


The routine is useful for preparing templates. Say you want to prepare a sheet that performs some operations on a data set. You prepare a sheet with sample data and appropriate calculations in some part of the sheet. However, when you delete the sample data, the sheet has many numerical errors (eg, #DIV/0!), which are confusing for some users. If you use this routine on the sheet, it will hide the error values produced by both normal and array formulas. Note, the routine does not hide all formulas that can produce errors – only those that currently show as errors. Be careful with this routine. There is no undo and you might want to see some errors.

An alternative is use the PopTools function simply called “F”. This produces formulas that are easier to read.

Footer image - matrix analysis