Hide all the error marks that are displayed in Excel

Excel often identifies error values for a variety of reasons, such as "#DIV/0!", "#N/A", and so on. How do you make these error value IDs no longer displayed?

First, use conditional formatting

First select the range of cells that contains the error values, the small triangle under conditional formatting in the Styles feature group on the Start tab of the Ribbon, and the highlight cell rules → other rules command in the pop-up menu, as shown in Figure 1.

Figure 1 Other rules

Opens the New Formatting Rule dialog box. In the Select Rule Type list, select "Error" in the list by selecting "format only" for cells that contain the following, click the Drop-down button below for formatting cells that meet the following criteria, as shown in Figure 2.

Figure 2 Selecting a rule type

The "Format" button at the bottom right of the point. Open the Format Cells dialog box, click the Font tab, point the Color Drop-down button, specify the same color as the background for the cell value, as shown in Figure 3, and then OK.

Figure 3 Formatting cells

We can also use formulas to set conditional formatting. First select any cell (whether it is an error value), such as the D3 cell, the small triangle below the conditional formatting feature button, and the new Rule command in the pop-up menu, and open the dialog box shown in Figure 2. In the Select Rule Type list above, select Use formulas to determine which cells to format, and enter the formula "=iserror (D3)" In the input box under format for values that match this formula, as shown in Figure 4. Click Format button to format in the Open dialog box.

Figure 4 Format

When you are sure to select the cell (D3 cell), click the Format Painter button in the Clipboard feature group on the Start tab of the Ribbon, and then select the other range of cells you want to set conditional formatting, and then copy the formatted conditional formatting to the past.

Two, using the IF function and the ISERROR function

For example, we would use the formula "=B2/A2" to calculate the quotient of B2 cells divided by A2 cells. If cell A2 is zero or null, the #DIV/0! is displayed Error identification. But we can modify the formula to "=if" (ISERROR (B2/A2), "", B2/A2) "so that no error marks appear.

The role of ISERROR (B2/A2) is to determine whether B2/A2 is an arbitrary error value. If so, return "True" or Return "FALSE".

The syntax rule of the IF function is if (condition is set up, the result displayed when the condition is set up, the display result when the condition is not tenable). Therefore, in the example above =if (ISERROR (B2/A2), "", b2/a2) the meaning is to determine whether the B2/A2 returns the error value, if it is, then show "" (Not show anything), if not, then display B2/A2 results.

Third, using Iferror function

The Iferror function is simpler than the above method. Suppose we still compute b2/a2. Write the formula "=iferror (B2/A2," ")". Then, if there is no error, the result of the B2/A2 is displayed, and if there is an error, nothing is displayed.