Whether you’re a seasoned Excel professional, a casual spreadsheet user or anything in between, the chances are that you’ve experienced one or two problems when using Excel formulas. Errors in your formulas can get in the way of what you’re trying to do, make your spreadsheet look messy or just be plain annoying! That’s why we decided that it would be a good idea to demystify some of them for you and perhaps give you an idea on how to correct them. Read on to make sense of those formulas that just don’t look quite right. Welcome to The 'The Excel Experts' guide to Excel Errors…
1 – #NAME?
What’s in a #NAME? This error most frequently occurs when there is a syntax error in the formula. It would be advisable to check the syntax of your formula if you’re receiving one of these. One way of doing this is to use the function icon in the toolbar. Other common causes of the #NAME? error are entering text in a formula without using double quotation marks and missing a colon from a range of cells in a formula. For example:
The formula in cell A4 is supposed to read ‘=SUM(A1:A3)’, but there is a misspelling. Correct the formula and the #NAME? error disappears.
2 – #VALUE!
The value in knowing what #VALUE! means is that Excel forces you to check the data type of the cells your formula is referencing. So for example, if you’re asking Excel to add together the values in a series of cells that contains one or more text values, it’ll return the #NAME! error. Ensure your formula only deals with numeric data and the error will disappear. For example:
Attempting to add cells A1, A2 and A3 above returns the #VALUE! error in cell A4 because A3 contains a non-numeric value, ‘Hi’. Removing A3 from the formula, or changing the value in A3 to a numeric one will ensure the error disappears.
3 – #DIV/0!
If you remember your Maths class at primary school, you’ll probably recall being told that you cannot divide anything by zero – or at least if you do, you’ll get infinity. Excel agrees with this and since it cannot adequately express infinity it will happily inform you if you try to bend the rules of division! A way to overcome the #DIV/0! error is to use an IF function so that any potential errors that might be caused by attempting to divide by zero could be handled on your terms. For example:
To avoid the #DIV/0! In cell B3, you could type ‘=IF(A4=0,”Infinity!”, A3/A4)’. This would catch the error and place the word ‘Infinity!’ in cell B3. If cell A4 contained a numeric value that is not 0, the original formula of A3/A4 would calculate as expected.
4 – #N/A
The #N/A error is normally due to your formula failing to reference a value in a LOOKUP function. There are a couple of ways to deal with this error. You can take steps to ensure the missing data is referenced, or you can substitute the error for something else. In this latter case, it is commonplace to use the IFERROR() function. For example:
The cell below Price/Lb in column E is showing #N/A because the VLOOKUP formula in that cell cannot find a price for Banana in the Lookup Table. To correct the error, you could populate a line in the Lookup Table with the word ‘Banana’ and a Price/Lb to its right, ensuring the VLOOKUP range is extended to include the new entry. To remove the error, you could enclose the VLOOKUP formula in an IFERROR function. This could be formed using the following syntax:
The inclusion of the double quote marks (“”) at the end of the statement means the #N/A error will be replaced with a blank cell entry.
We hope this has helped you with some of the more Excel common errors. To find out more about what 'The Excel Experts' can do for you and your business, see the foot of this page for details on how to contact us.