In the New Formatting Rule dialog box, click Format only cells that contain.
Select the cell that contains the error, and on the Home tab, click Conditional Formatting.
The contents of the cell should now display 0 instead of the #DIV! error. The formula =B1/C1 becomes =IFERROR(B1/C1 ,0). Type ,0 ) – that is, a comma followed by a zero and a closing parenthesis. Move the cursor to the end of the formula. Select A1, and press F2 to edit the formula.Īfter the equal sign (=), type IFERROR followed by an opening parenthesis. Open a blank workbook, or create a new worksheet.Įnter 3 in cell B1, enter 0 in cell C1, and in cell A1, enter the formula =B1/C1. You can hide error values by converting them to a number such as 0, and then applying a conditional format that hides the value. Convert an error to zero and use a format to hide the value Error values include #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!. For example, division by 0 is not allowed, and if you enter the formula =1/0, Excel returns #DIV/0. There are many reasons why formulas can return errors. There are several ways to hide error values and error indicators in cells. Let's say that your spreadsheet formulas have errors that you anticipate and don't need to correct, but you want to improve the display of your results. Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 More.