How to Replace # Errors with Zero or Any Specific Value in Excel

This post will guide you how to replace #VALUE! Or #DIV/0! Errors with zero or any specific value in Excel. How do I replace all Formula Errors in your worksheet with Go to Special feature in Excel.

1. Replace # Formula Errors with Zero

Assuming that you have a list of data in range A1:B4, which contain some Errors, such as: #DIV/0!, #VALUE! Or other Errors. And you need to replace all Error message with zero or any other specific value in your selected range in Excel. How to do it. You can use the Go To Special feature to select all cells that contain Error value. Then you can type zero in formula bar, and press Ctrl + Enter keys to apply the same formula to replace errors with zero value. Just do the following steps:

Step1: select the range of cells that you need to replace formula errors.

replace formula error1

Step2: go to HOME tab, click Find & Select command under Editing group. And select Go To Special from the drop down menu list. And the Go To Special dialog will open.

replace formula error2

Step3: select Formulas radio button in the Select section, and only check Errors checkbox. And then click Ok button.

replace formula error3

Step4: all of the formula errors will be selected in the selected range of cells.

replace formula error4

Step5: type zero (0) value or any other specific value that you want to replace the errors in selected range. And then press Ctrl + Enter keys to replace all Errors with zero value.

replace formula error5

2. Video: Replace # Formula Errors with Zero

This video will demonstrate how to replace # errors with zero or any specific value in Excel using simple and easy-to-follow steps.

Leave a Reply