MS Excel Error Messages- What they mean and How to Fix Them
While we have all seen the error messages in MS Excel,many of our clients say they ere often confused about how to fix them. This post is designed to help you understand the common error messages you may see in MS Excel. Once you understand what they mean, it is easier to correct the problem.
There are 5 main error messages you may encounter in MS Excel. I will go through each in turn and suggest how to resolve them.
Error 1 #DIV/O!
The formula you have entered in the spreadsheet is trying to divide a number by 0. You may also see this error when the divider cell is empty.
In this example we are trying to divide C4 by D4 to calculate the hourly sales. The formula is =C4/D4
You could resolve this problem by using an If Statement. If you are new to If statements consider our MS Intermediate course
Type the following formula into cell E4 =IF(D4<=0,””,C4/D4) Or you could use the If function from the Formula Ribbon
Then if D4 was empty or zero, it would return a blank value.
If D4 was equal to a number it would preform the calculation.
Error 2 #N/A!
Many people find the #N/A error message, to be the most annoying.It looks horrid and suggests that you have made a real mistake. The reality is, MS Excel’s is merely telling you that the data you’re looking for doesn’t exist in your spreadsheet, or at least not in the cell you have referenced.
This is a very common error in VLookup Tables.
In the example here, there error is shown because J4 is empty. Once I type a staff number into J4 it will return the result.
The error can also appear if there is a mismatch between the search data and the data in the Vlookup table.
Look for misspellings, extra leading or trailing spaces, numbers or dates that aren’t formatted correctly etc.
This is especially important if you’re importing or copying data from another source, because formatting differences are common and can be hard to spot.
MS Excel 2007 and 2010 are especially sensitive to formatting differences. You can use the Text functions to eliminate these differences.
Error 3 #Name?
In this example I have named cell H1 as VAT. Then I used the named cell in a formula to calculate the VAT amount due.
However, I mistyped and enter VATT instead of VAT in the formula.
Hence the #Name? error
Once I click into the formula bar I can type the correct cell name. The error message disappears.
Error 4 #REF
You will see this error when a cell reference is not valid. For example, you may have deleted cells that were referred to by other formulas, or you may have pasted cells on top of cells that were referred to by other formulas.
If I delete the column C I will get a #REF error.
This is because data referred to in the formula is missing.
You can avoid this by using Paste Values before deleting the column.
Error 5 ######
This error confuses many new users.
It occurs when the contents are too wide to fit in the cell. For eg. a long number like €25641231.00 typed into a normal cell will show ####s.
You can resolve this by widening the cell to accommodate the long number. Simply double click between E and F.
MS Excel has introduced the IfError function to deal with these error messages in MS Excel 2007 and MS Excel 2010. That is covered in a separate post.