- Applies to: Excel for Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007.
This blog post aims to teach you how to conceal error values and error indicators in cells. It will also explain why error values and indicators arise in your spreadsheet in the first place so you can guard against them in future.
Imagine that your spreadsheet formulas contain errors that you expect and have no urgency to rectify, yet you aim to enhance the presentation of your results. There are multiple ways to cover error values and error indicators in cells.
There are several reasons why formulas can throw up errors. For example, division by 0 is prohibited, and if you type the formula =1/0, Excel gets #DIV/0. Error values comprise #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!.
Convert an error to zero and use a format to hide the value
You can conceal error values by changing them to a number like 0. Then, setting a conditional format that masks the value.
Create an example error
- Access a blank workbook, or produce a new worksheet.
- Type 3 in cell B1, input 0 in cell C1, and in cell A1, write the formula =B1/C1.
The #DIV/0! error displays in cell A1.
- Press A1, and tap F2 to amend the formula.
- Following the equal sign (=), enter IFERROR then immediately insert an opening parenthesis.
- Relocate the cursor to the end of the formula.
- Input ,0) – that is, a comma coming after a zero and a closing parenthesis.
The formula =B1/C1 converts to =IFERROR(B1/C1,0).
- Press Enter to finish the formula.
The cell contents should now present 0 rather than the #DIV! error.
Set the conditional format
- Pick the cell that includes the error, and on the Home tab, choose Conditional Formatting.
- Select New Rule.
- In the New Formatting Rule dialogue box, press Format only cells that contain.
- Below Format only cells with, inspect that Cell Value displays in the first list box, equal to shows in the second list box. After this, enter 0 in the text box to the right.
- Select the Format button.
- Press the Number tab and then, beneath Category, pick Custom.
- In the Type box, type ;;; (three semicolons), and then choose OK. Press OK again.
The 0 in the cell vanishes. This occurs because the ;;; custom format results in any numbers in a cell to avoid being revealed. Despite this, the genuine value (0) is safeguarded in the cell.
Cover error values by turning the text white
Follow the listed procedure to structure cells that include errors to make those cells’ text appear in a white font. This effectively turns the error text in these cells practically invisible.
- Choose the range of cells that have the error value.
- On the Home tab, in the Styles group, select the arrow beside to Conditional Formatting. Then, pick Manage Rules.
The Conditional Formatting Rules Manager dialogue box displays.
- Press New Rule.
The New Formatting Rule dialogue box manifests.
- Below Select a Rule Type, choose Format only cells that contain.
- Beneath Edit the Rule Description, in the Format only cells with list, click Errors.
- Press Format. Then, select the Font tab.
- Press the arrow to launch the Colour list, and below Theme Colors, pick the white colour.
Display a dash, #N/A, or NA in place of an error value
There could be situations when you want to draw attention away from error values in cells, and instead opt for a text string like “#N/A,” a dash, or the string “NA” to be displayed. This can be executed by using the IFERROR and NA functions, like the illustrated example depicts.
IFERROR – Employ this function to regulate if a cell includes an error or if a formula’s results will bring back an error.
NA – Apply this function to yield the string #N/A in a cell. The syntax is =NA().
Hide error values in a PivotTable report
- Select the PivotTable report.
The PivotTable Tools launch.
- Excel 2016 and Excel 2013: On the Analyse tab, in the PivotTable group, select the arrow adjacent to Options. Next, choose Options. Excel 2010 and Excel 2007: On the Options tab, in the PivotTable group, press the arrow beside Options. After this, choose Options.
- Press the Layout & Format tab, and then take action in one or several of the following:
- Change error display – Click the For error values show checkbox below Format. In the box, enter your desired value to showcase rather than errors. To present errors as blank cells, erase any characters in the box.
- Change empty cell display – Choose the For empty cells show checkbox. In the box, input your preferred value to indicate in empty cells. To reveal blank cells, remove any characters in the box. To show zeroes, empty the check box.
Hide error indicators in cells
If a cell occupies an incorrect formula that leads to an error, a triangle (an error indicator) displays in the cell’s top-left corner. You can preclude these indicators from being shown by performing the following procedure.
Cell with a formula problem
- In Excel 2016, Excel 2013, and Excel 2010: Select File > Options >Formulas. In Excel 2007: Choose the Microsoft Office Button > Excel Options > Formulas.
- Below Error Checking, empty the Enable background error checking checkbox.