​​​​Iferror

Checks if the value given as first argument is any type of error. If it is an error, the second argument is used as value. If it is not an error, the cell will be filled with the value given as first argument.

Iferror syntax

=IFERROR(Value, Value if error)

Argument  Argument description
1Value The value that is checked to be an error. If it is not an error, this value will be returned.
2Value if errorThe output that will be returned by Excel if the first argument is an error.

Try it for yourself

When calculating ratios, you may not want to show errors when dividing by 0. Try it out for yourself below.

Note that cell C3 in the spreadsheet below would normally contain a division by 0 error.

Explanation

Calculating the ratio between two things requires dividing the two things. In this case, dividing the number of chairs by the number of people to get the chairs per person ratio. If we have 0 people, we end up dividing by 0. Dividing by 0 is not allowed in mathematics, so Excel shows an error.

Then, we use IFERROR to change the value of the cell to 0 if this error is shown. This way, the errors will not perpetuate throughout the workbook.

Questions

Is there an IFNOTERROR function?

No, there is not. However, you could copy the formula in the answer below to accomplish the same thing.

Can I change the value if it isn’t an error?

Yes, although there is no function in Excel to do this, you can create your own formula. You could use something like:
=IF(ISERROR(A1), "Error detected", "No error here")
This formula checks cell A1 for errors. If there is an error, the value will be “Error detected”. If there is no error, the value will be “No error here”. We have also made an IFNOTERROR exercise, where you can try this for yourself.

What error values are checked by IFERROR?

#DIV/0, #N/A, #REF, #VALUE, #NUM, #NAME and #NULL.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"36c04":{"name":"Main Accent","parent":-1},"3a8fd":{"name":"Accent Light","parent":"36c04","lock":{"saturation":1,"lightness":1}}},"gradients":[]},"palettes":[{"name":"Default","value":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]},"original":{"colors":{"36c04":{"val":"rgb(255, 204, 102)","hsl":{"h":40,"s":1,"l":0.7}},"3a8fd":{"val":"rgb(241, 241, 241)","hsl_parent_dependency":{"h":0,"s":0,"l":0.94}}},"gradients":[]}}]}__CONFIG_colors_palette__

​Get Good at Excel on Autopilot

Sign up to our newsletter and receive ​Excel articles, tips and tricks delivered straight to your inbox. All you have to do is read them! ​Plus, to get you started, you'll receive a PDF with 200+ Excel shortcuts.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"2dd0d":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"2dd0d":{"val":"rgb(45, 164, 92)","hsl":{"h":143,"s":0.57,"l":0.41}}},"gradients":[]},"original":{"colors":{"2dd0d":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Join List
>