How to replace # formula errors with 0, blank or certain text in Excel?

Excel users frequently encounter formula errors such as #DIV/0!, #VALUE!, #REF!, #N/A, #NUM!, #NAME?, and #NULL! in their data tables or calculation results. These error values not only affect the readability of reports but may also impact further data processing, analysis, and sharing. Often, for improved data presentation or downstream calculation logic, it becomes necessary to replace all or certain error types in worksheets with0 (zero), blank cells, or a specified text string that is easier for others to understand.

This article provides practical and easy-to-follow solutions for searching for and replacing # formula errors in Excel cells. Using a representative table shown below, we'll demonstrate how to efficiently replace those error values depending on your needs and workflow.

Replace formulas errors with 0, any specific values, or blank cells

• By uisng IFERROR function
• By using ERROR.TYPE function
• By using Go To command
• By uisng Kutools for Excel
• By using VBA code

Replace # formulas errors with 0, any specific values, or blank cells with IFERROR

Excel provides the IFERROR function, which is specifically designed to catch all common error types and let you substitute them with any value or message of your choice. This can simplify handling errors during calculations and improve worksheet clarity.
To use it, enter =IFERROR(value, value_if_error) in a corresponding cell. If value is an error, it will return your specified value_if_error; if value is not an error, it simply returns the calculated result.

In the above example, different formula error types such as #N/A have been replaced with either a blank cell, a numeric 0, or a custom text message. You can adjust value_if_error to suit your requirement—as illustrated below, enter actual values, empty string ("") for blank, or descriptive text as needed:

This approach is most suitable when you are building formulas and want to ensure error values are not displayed in your final tables, reports, dashboards, or when passing data to others. A practical tip is to wrap any complex or unstable calculation in IFERROR to preserve worksheet continuity.
Keep in mind that if you only want to handle specific types of errors (like only #N/A), consider using IFNA or combining IF and ISERROR/ISERR functions for more targeted handling. Also, make sure to copy down your formula for all relevant cells to cover the entire dataset.

Replace # formulas errors with specific numbers with ERROR.TYPE

The ERROR.TYPE function is another built-in Excel feature, which can be utilized to identify different error values by returning a unique number corresponding to each error type. This is particularly useful when you wish to distinguish between error types for further conditional logic in your formulas.
In the following example, using ERROR.TYPE in a blank cell next to the formula error returns a code (from 1 to 8).

Using the Fill Handle allows you to apply the ERROR.TYPE formula across a range. However, note that ERROR.TYPE is mainly for analyzing or mapping the error types, not for replacing them directly. You'd typically combine it with IF or CHOOSE to output more friendly substitutes. Additionally, remembering each error code may require reference to documentation or the table above.

If your scenario requires customized replacements based on error type, you can nest ERROR.TYPE in an IF or CHOOSE formula to output relevant information for each error condition.

Search and replace # formula errors with0, any specific values, or blank cells with Go to command

This method is suitable for users who want to batch process and directly overwrite error cells in an existing area, especially after calculations are final. Using Excel’s built-in Go To Special command, you can locate all error cells in a selection and replace them at once.

1. First, select the worksheet range containing possible formula errors.

2. Press F5 on your keyboard (or Ctrl + G) to bring up the Go To dialog.

3. Click Special to open the Go To Special options box.

4. Choose only the Formulas option, and within it, ensure only Errors is checked. This action will target all cells displaying error results in your selected range.

5. Click OK, and Excel will automatically highlight all such error cells.

6. Directly type 0, or your chosen replacement value, and use Ctrl + Enter so that Excel fills all selected error cells with the value. 

If you want to clear these error cells entirely, simply press the Delete key after selection to leave those cells blank.

Search and replace # formula errors with 0, any specific values, or blank cells with Kutools for Excel

The Error Condition Wizard of Kutools for Excel streamlines the process of managing error values. With this tool, users can flexibly replace all or specific error types with 0, blank cells, or personalized messages for presentation or downstream editing. This is especially convenient for non-formula experts or users handling large, complex datasets.

1. Begin by selecting the range where you want to replace error values. Then navigate to the menu and click Kutools > More > Error Condition Wizard.

2. In the Error Condition Wizard dialog, configure your preferences as follows:

(1) Under Error types, choose whether to apply action to Any error value, Only the #N/A error value, or Any error value except #N/A. Choose as appropriate for your scenario.

(2) In the Error display section, select Nothing (a blank cell) if you want errors shown as blank.

To substitute errors with zero or a message, select A message (text) and enter "0" or custom text in the field.

(3) Click OK to apply changes.

The utility will instantly process your selection, replacing error values across the area as per your setup. Below are the visual results:

Replace all error values with blank

Replace all error values with zero

Replace all error values with certain text

The Error Condition Wizard in Kutools for Excel is highly practical for repetitive cleanup tasks. You can also quickly undo (Ctrl + Z) changes if needed. Always review your selection before applying bulk operations, especially on large datasets.

Replace all error values with0, blank, or specified text via VBA code

For advanced scenarios such as automating cleanup for large worksheets or repeatedly handling specific error substitutions, using a simple VBA macro can save time and manual effort. Below, you'll find step-by-step instructions for using VBA to batch replace all error values in a selected range with your preferred alternative—0, blank cell, or a specific message.
This approach is highly scalable and suitable for users familiar with basic macro operations.

1. Launch the Visual Basic for Applications (VBA) editor by clicking Developer > Visual Basic. In the editor that appears, click Insert > Module, and copy-paste the following code into the blank module window:

Sub ReplaceErrorsWithValue()
    Dim WorkRng As Range
    Dim ReplaceWhat As String
    Dim Prompt As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select the range to process", xTitleId, WorkRng.Address, Type:=8)
    
    Prompt = "Enter the replacement value for errors:" & vbCrLf & "(Leave blank for empty cell; enter 0 or any text string as needed)"
    ReplaceWhat = Application.InputBox(Prompt, xTitleId, "", Type:=2)
    
    If Not WorkRng Is Nothing Then
        Dim cell As Range
        Application.ScreenUpdating = False
        
        For Each cell In WorkRng
            If IsError(cell.Value) Then
                cell.Value = ReplaceWhat
            End If
        Next
        
        Application.ScreenUpdating = True
    End If
End Sub

2. And then run the macro by clicking the button or pressing F5 in the VBA window. When prompted, select the target range, and then specify your desired replacement: leave the input box empty to clear error cells (leave blank), or enter "0" to replace with zeros, or type your custom label text.

Search and replace # formula errors with0 or blank with Kutools for Excel

Related article:

• How to change #DIV/0! error to the readable message in excel?