Hiding Error Values On-Screen and in Print
Sometimes, you'll want to suppress the display of those nasty-looking error values in a worksheet until you get all the data entered that you need to make all the error values really disappear. (Error values running throughout the worksheet can tend to make the client a little nervous.) When it comes to suppressing their display, you have a couple choices:
- You can nip them in the bud so to speak by building formulas that suppress the display and trap any possible errors before they spread anywhere else in the worksheet.
- You use the Conditional Formatting feature to blank them out.
Follow these steps to use the Conditional Formatting feature:
- Select the cell or range of cells containing the error values you want to blank out.
- Choose Format → Conditional Formatting.
- Click the drop-down button in the box that reads Cell Value Is and select Formula Is from its pop-up menu.
- Type =ISERROR( in the text box to the right.
- Reselect the cell or range of cells containing the error values you want blanked out or type their addresses.
If you want to drag through the cells to select this range but the Conditional Formatting dialog box gets in the way, minimize the dialog box by clicking its Condense button (the one with the tiny worksheet icon). - Type ) to close the parentheses for the ISERROR function.
- Click the Format button to open the Format Cells dialog box.
- On the Font tab, click the Color drop-down button and then select the white square on the pop-up palette that appears. ( The white square is at the intersection of the very last column and row of the upper palette.) Then click OK.
- Click OK to close the Conditional Formatting dialog box.
As soon as you click OK, Excel replaces all the error values in the cell or cell range you selected with white, effectively blanking out their error value results without disturbing their formulas.
If all you want to do is suppress the display of error values in a printout of the worksheet, you don't have to go to all that trouble of first blanking out their display by setting up a conditional format. All you have to do is follow these simple steps prior to sending the worksheet to the printer:
- Choose File → Page Setup to open the Page Setup dialog box.
- On the Sheet tab, click the Cell Errors As dropdown button to open its drop-down list.
- From the drop-down list, select <blank>, -- (dashes), or #N/A (the Not Available value) to replace the default value displayed in the Cell Errors As box.
- Click OK to close the Page Setup dialog box or click Print if you're ready to print your worksheet.
Click the Print Preview button rather than the Print button if you want to make sure that all the error values are really gone from the printout before you send the job to the printer.
