How-To

How to Ignore All Errors in Microsoft Excel

Working in Excel

An error in Excel is a sign that a calculation or formula has failed to provide a result. You can hide Excel errors in a few different ways. Here’s how.

The perfect Microsoft Excel spreadsheet doesn’t contain errors—or does it?

Excel will throw out an error message when it can’t complete a calculation. There are a number of reasons why, but it’ll be up to you to figure it out and solve it. Not every error can be solved, however.

If you don’t want to fix the problem, or you simply can’t, you can decide to ignore the Excel errors instead. You might decide to do this if the error doesn’t change the results you’re seeing, but you don’t want to see the message.

If you’re unsure how to ignore all errors in Microsoft Excel, follow the steps below.

How to Hide Error Indicators in Excel

Used a formula incorrectly? Rather than return an incorrect result, Excel will throw up an error message. For example, you might see a #DIV/0 error message if you try to divide a value by zero. You may also see other on-screen error indicators, such as warning icons next to the cell containing the error.

You can’t hide the error message without altering the formula or function you’re using but you can hide the error indicator. This will make it less obvious that the spreadsheet data is incorrect.

To quickly hide error indicators in Excel:

  1. Open your Excel spreadsheet.
  2. Select the cell (or cells) containing the error messages.
  3. Click the warning icon that appears next to the cells when selected.
  4. From the drop-down, select Ignore Error.
    Ignoring errors in Excel

The warning icon will disappear, ensuring the error appears more discreetly in your spreadsheet. If you want to hide the error itself, you’ll need to follow the steps below.

How to Use IFERROR in Excel to Hide Errors

The best way to stop error messages from appearing in Excel is to use the IFERROR function. IFERROR uses IF logic to check a formula before returning a result.

For example, if a cell returns an error, return a value. If it doesn’t return an error, return the correct result. You can use IFERROR to hide error messages and make your Excel spreadsheet error-free (visually, at least).

The structure for an IFERROR formula is =IFERROR(value,value_if_error). You’ll need to replace value with a nested function or calculation that may contain an error. Replace value_if_error with the message or value that Excel should return instead of an error message.

If you’d rather see no error message appearing, use an empty text string (eg. “”) instead.

To use IFERROR in Excel:

  1. Open your Excel spreadsheet.
  2. Select an empty cell.
  3. In the formula bar, type your IFERROR formula (eg. =IFERROR(10/0,””)
  4. Press Enter to view the result.
    IFERROR formula in Excel

IFERROR is a simple but powerful tool for hiding Excel errors. You can nest multiple functions within it, but to test it out, make sure the function you’re using is designed to return an error. If the error doesn’t appear, you’ll know it works.

How to Disable Error Reporting in Excel

If you want to disable error reporting in Excel completely, you can. This ensures that your spreadsheet remains free of errors, but you don’t need to use workarounds like IFERROR to do it.

You might decide to do this to prepare a spreadsheet for printing (even if there are errors). As your data might become incomplete or incorrect with error reporting disabled in Excel, we wouldn’t recommend disabling it for production use.

To disable error reporting in Excel:

  1. Open your Excel document.
  2. On the ribbon, press File.
    Excel file menu
  3. In File, press Options (or More > Options).
    Excel File options menu
  4. In Excel Options, press Formulas.
  5. Uncheck the Enable background error checking checkbox.
  6. Press OK to save.
    Disable error checking in Excel

Resolving Issues in Microsoft Excel

If you’ve followed the steps above, you should be able to ignore all errors in an Excel spreadsheet. While it isn’t always possible to fix a problem in Excel, you don’t need to see it. IFERROR works well, but if you want a quick fix, you can always disable error reporting entirely.

Excel is a powerful tool, but only if it’s working properly. You may need to troubleshoot further if Excel keeps crashing, but an update or restart will usually fix it.

If you have a Microsoft 365 subscription, you can also try repairing your Office installation. If the file’s at fault, try repairing your document files instead.

Click to comment

Leave a Reply

Your email address will not be published. Required fields are marked *

 

To Top