Methods for hiding errors on Excel worksheets
October 30, 2007
The IF() function can be used to help beautify your workbooks. If you are seeing error messages because formulas rely on data that is not yet entered, try the following. Select the cell that is producing the error message. Look at the original function. Click between the = sign and the original function and type IF(ISERROR( then click after the original function and type ),"", then type the original function again and finish with a ). In other words, the original function that was producing an error will look like this:
=IF(ISERROR(original function),"",original function)
This little trick tells Excel to return a blank cell if the function is producing an error, and to return the result of the function if the function is ok.
In addition to 'wrapping' a function in an IF(ISERROR()) function, such as:
=IF(ISERROR(function),"",function)
you can also use the following methods to hide errors:
(Excel 2002+) Choose File → Page Seutp and click the Sheet tab. Select an option from the drop-down list that allows you to display Cell errors as.
Use conditional formatting. Select a range (e.g. the whole sheet by pressing CTRL+A and pay attention to what cell is 'active' (not highlighted) in your selection. In the conditional formatting, set Formula is and type =ISERROR(A1) (where A1 is the address of the active cell in your selection). Then click the Format button and set the font color to white. Then, if a cell is an error, it will display, but with a font color that matches the background of your sheet. Tricky, eh?
Create a presentation column, row, or sheet. The idea here is that you create a column, row, or sheet which uses a function such as =IF(ISERROR(A1),"",A1), where A1 points to the cell where all the real work is happening. This "presentation" cell simply decides whether or not to show the results of the "real" cell. You can then hide the real cell. You are basically using two cells to do the work--one does the calculation, one decides how to present it.
About the Author
You May Also Like