Skip to main content

Kutools for Office — One Suite. Five Tools. Get More Done.

How to remove divide by zero errors (#DIV/0!) in Excel?

Author Kelly Last modified

divide by zero errors

When analyzing data in Excel, you might often encounter the #DIV/0! error, especially in calculations involving division. This error typically appears when a formula tries to divide a number by zero or by an empty cell, which Excel considers invalid. For example, in a sales analysis worksheet, if you need to calculate the average price for each fruit using a formula like =D2/(C2-B2), as shown in the illustration, the formula will return a #DIV/0! error whenever the value in Q2 (ending quantity) is equal to Q1 (starting quantity). Such errors can clutter your reports, making data harder to interpret or causing downstream calculation issues. There are various ways to avoid, remove, or manage these error messages, depending on your specific needs and whether you can adjust the formulas directly or prefer quick-fix methods.


Prevent divide by zero errors (#DIV/0!) with modifying formulas

Using formulas that automatically handle divide-by-zero situations is a proactive and systematic approach. For instance, the formula =D2/(C2-B2) will return a #DIV/0! error whenever Cell C2 equals Cell B2. To eliminate such errors in your reports, you can enhance your formula using the IFERROR function, which checks for any error in your calculation and allows you to substitute a blank or a customized message. This modification helps your worksheet remain tidy and avoids confusion caused by error messages.

In Cell E2, input the formula =IFERROR(D2/(C2-B2),""), and then drag the Fill Handle down through the Range E2:E15 to apply it to other cells where calculation is required.

With this adjustment, if a division by zero occurs or any other error is present, the formula displays a blank cell instead of the error message. This enables cleaner data presentation:

apply a formula to prevent divide by zero errors

Tips: You may also replace "" in the formula with a custom notice, such as "Not Available" or "Invalid" for easier identification of problematic entries. Just update the formula accordingly:

=IFERROR(D2/(C2-B2),"Not Available")

 

Note: While the IFERROR function is straightforward and effective, its broad error-capturing means it will hide all types of errors, not just #DIV/0!. If you use complex formulas where different errors require different handling, consider using IF with ISERROR or IF with ISERR to target errors more selectively. For example:

=IF((C2-B2)=0,"",D2/(C2-B2))
This formula specifically avoids division by zero, keeping other errors visible for troubleshooting.

 

However, adapting formulas can become cumbersome in large workbooks or when formula structure is not easily adjustable. In such cases, exploring other error-removal methods can be more convenient.


Remove divide by zero errors (#DIV/0!) with selecting all errors and deleting

If #DIV/0! If errors have already appeared in your range and modifying the formulas is not feasible, you can quickly locate and remove all error values using Kutools for Excel’s Select Cells with Error Value utility. This tool helps you batch-select cells containing any error values (including #DIV/0!, #N/A, etc.) so you can delete or handle them in bulk, which is especially useful in large and complex worksheets.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

Select the entire range where you wish to search for errors. Click on Kutools > Select > Select Cells with Error Value. This process quickly identifies all error-containing cells within your selection.
click Select Cells with Error Value feature of kutools

All error cells in the selected range are instantly highlighted, as shown in the screenshot. To remove these errors, simply press the Delete key. This clears the error cells and keeps your worksheet clean.

all errors in the selected range are selected, then press Delete key to remove them

Note: This method selects all errors in the chosen range, not just #DIV/0! errors. If you wish to target only certain error types, manually filter or use alternate techniques.

This technique is advantageous when you want quick data cleanup and formula adjustment is not possible. However, simply deleting cells may result in missing data and may not be suitable if downstream calculations depend on those values.


Remove divide by zero errors (#DIV/0!) with replacing errors with blank

For users who want to automatically replace error messages, including #DIV/0!, with blank cells but preserve the worksheet structure and underlying formulas, Kutools for Excel provides the Error Condition Wizard utility. This feature allows you to specify which error types to target and how they should be displayed, effectively hiding unsightly errors and offering a more streamlined, readable worksheet.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

Select your desired range where errors may exist. Navigate to Kutools > More > Error Condition Wizard.

click Error Condition Wizard feature of kutools

In the Error Condition Wizard dialog, open the Error types box and choose Any error value except #N/A from the dropdown. In the Error display section, check Nothing (a blank cell), and confirm by clicking OK.
set options in the Error Condition Wizard dialog box

This process will automatically update your formula cells within the range to display blanks instead of standard error messages, except for #N/A if you wish to keep those visible—for example, as intentional look-up errors. The outcome is shown below:

all errors except the #N/A errors are replaced with blanks

Tips: If preferred, you can change the display option to show custom error-notification text, such as "Invalid" or "Check Data" for easier troubleshooting. Take care that replacing errors with blanks may mask underlying data issues—periodically review potentially problematic formulas.

Note: This method will ignore #N/A errors by default unless you explicitly include them in your selection.

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It Now


VBA: Automatically scan and replace #DIV/0! errors with blank or custom messages

Sometimes, you may want to automate error removal across an entire worksheet or work with ranges where modifying formulas isn't practical. VBA (Visual Basic for Applications) allows you to create a quick macro that scans a selected range and replaces all #DIV/0! errors with either blanks or a custom message. This is especially useful for one-time cleanups or large datasets.

1. Go to Developer Tools > Visual Basic to open the VBA editor window. In the editor, click Insert > Module and paste the following code into the Module:

Sub ReplaceDiv0WithBlank()
    Dim WorkRng As Range
    Dim Rng As Range
    Dim xTitleId As String
    Dim ReplaceText As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set WorkRng = Application.Selection
    Set WorkRng = Application.InputBox("Select range to scan for #DIV/0! errors:", xTitleId, WorkRng.Address, Type:=8)
    
    ReplaceText = Application.InputBox("Enter message to replace #DIV/0! (leave blank for empty cell):", xTitleId, "", Type:=2)
    
    For Each Rng In WorkRng
        If IsError(Rng.Value) Then
            If Rng.Text = "#DIV/0!" Then
                Rng.Value = ReplaceText
            End If
        End If
    Next
End Sub

2. After entering the code, close the VBA editor. Select the range where you want to replace #DIV/0! errors in Excel, then press the F5 key or click Run. Follow the prompts to choose your range and enter your custom replacement text (or leave blank).

Notes and Tips: This code only affects #DIV/0! errors—other errors will be left untouched. If you are working with a large range, processing time may increase. If any protected cells exist, ensure they are unlocked, or the macro won't be able to make replacements there.


Demo: Remove divide by zero errors (#DIV/0!) errors

 

Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

Excel Word Outlook Tabs PowerPoint
  • All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
  • One installer, one license — set up in minutes (MSI-ready)
  • Works better together — streamlined productivity across Office apps
  • 30-day full-featured trial — no registration, no credit card
  • Best value — save vs buying individual add-in