ExcelFrog Clear Excel Tutorials

home about

Excel: Six Common Errors and How to Fix Them

If you've been using Microsoft Excel for a while, you must have seen those cryptic error messages appearing in your cells. Something like #REF! or #NULL!. They are supposed to help us fix mistakes, but their exact meaning is far from obvious.

In this short tutorial we will cover in detail the 6 most common error messages you can get in Excel, and how to fix them!

The #### Error

This one is easy, it means that there is not enough space in the cell to display the value.

To fix it, simply increase the size of the cell.

The #NAME? Error

This error appears when you are using a function that doesn't exist, which probably means that you made a spelling mistake.

So proofread the function name to find the mistake, and fix it!

The #DIV/0! Error

Do you know that it's impossible to divide a number by zero? And when you try to do just that in Excel, you get the #DIV/0! error.

If that happens you have 2 solutions:

• Do not divide by zero.
• Add an IF function to do the division only if it's not by zero. For example you could do something like this: =IF(E2<>0, D2/E2, "")

The #NULL! Error

This usually happens when you have an error in the syntax of your formula. Maybe you forgot a commas or a plus sign somewhere.

The only way to fix that is to have a close look at your formula to see what's wrong.

The #REF! Error

I don't like seeing this error, it means that a reference (cell or range) you are using is not valid. That can happen when you delete a column, or past stuff on top of data.

There's no easy fix, usually the best way is to start ths formula from scratch to make sure you didn't delete important data.

The #VALUE! Error

This is a classic mistake. This happens when your formula includes cells with different data types, like if you try to multiply a number by a text.

So check all the cells/range in your formula to find which cells should not be there, and remove them.

Bonus

If you really can't fix your error, it's possible to "hide" it with the IFERROR function:

=IFERROR(value, value-if-error)

Put your formula in the first parameter, and if there's an error, the second parameter will be executed.

But try to avoid this solution as much as you can, because it's much better to actually fix the error than to "hide" it like that.

Conclusion

With all this information you can be quicker to debug your spreadsheet because you know exactly what the problem is :-)

Here's a quick recap of all the errors we saw with their meaning:

• ####, not enough space to display the value.
• #NAME?, the function does not exist.
• #DIV/0!, trying to divide by zero.
• #NULL!, there is a syntax error.
• #REF!, some cells do not exist anymore.
• #VALUE!, cells have diffent types of data.

See all the tutorials on the homepage