Microsoft Excel supports a wide range of complicated formulas and functions, but you have to be precise when typing them in and selecting the various cells to use. If you get anything wrong, even a minor typo, you might see an error message appear.
This guide explores common Excel formula errors and ways to fix them.
Contents
Error 1. #VALUE!
This error appears for a few different reasons. One of the most common explanations is if you’re trying to use a formula that needs numbers, but you’ve selected a cell that contains text. Like in the basic example above, where cell C1 is trying to add together “100” and “WORD,” which obviously doesn’t make sense. #VALUE! also appears due to misplaced commas or incorrect value types.
How to Fix
To fix this, look at the function you’re trying to use in the error cell. Then go through each corresponding cell referenced within the function to find whatever is causing the issue. Common reasons for this error could be a data type mismatch (e.g., a piece of text where a number should be), hidden characters (e.g., extra spaces), and incorrect cell reference (formula referencing the wrong cells).
Error 2. #NAME?
This appears when Excel doesn’t recognize the name of a function or formula you tried to use. It’s almost always the result of a typo, like typing “SUMM” instead of “SUM” or “AVERGE” instead of “AVERAGE”.
How to Fix
To fix this, double-check the spelling of whatever formula you want to use. Consult this official list of Excel formulas to confirm the right spelling, and then fix it.
Error 3. #NUM!
This error shows when a number is too big or otherwise impossible for Excel to display in a cell. It can happen when you’re working with very large calculations or trying to do something mathematically impossible, like working out the square root of a negative number (as in the screenshot).
How to Fix
To fix this, you either need to adjust the numbers or formulas you’re working with to create a smaller or realistic end value. Or click “File,” then “Options,” then “Formulas,” and click to enable the “iterative calculation” setting.
Error 4. #REF!
This one can be complicated to work out, but it happens because you are referencing a cell or range that no longer exists or isn’t valid. You might have, for example, made the formula with a certain set of cells, then deleted one of the columns or rows involved in the calculation, leading to a #REF! error as Excel doesn’t have access to the cells it needs anymore.
How to Fix
Fixing this can be tricky, but if you’ve just seen the #REF! error pop up, press Ctrl and Z to undo whatever change you made that caused it to appear. Then, reassess how you can change your spreadsheet without impacting the existing formula. If you don’t know when the error showed up, you need to closely examine the formula in the cell displaying the error and check the relevant cell references to see which part doesn’t add up.
Error 5. #DIV/0!
This is one of the simpler error messages to figure out, and it’s self-explanatory. It shows up when you’re trying to divide something by zero, which isn’t possible.
How to Fix
Find the offending “0” and either remove it, adjust it to another number, or rewrite your formula so it doesn’t involve any division by zero.
Error 6. #CALC!
This error code represents a calculation problem or impossibility. E.g., when you’re trying to use a function without specifying appropriate cells and especially when you’re working with “LAMBDA” or “FILTER” functions.
How to Fix
If you’re using “LAMBDA,” make sure to include a sample cell group within parentheses to give this function data to work with. If using “FILTER,” add an “if_empty” argument to take care of situations where the FILTER function might return an empty array. This helps prevent the #CALC! error from affecting subsequent calculations. For other formulas, go through them bit by bit and check for any inconsistencies causing the error.
Error 7. #NULL!
This error can show up for a few reasons, but it’s most commonly the result of the user forgetting a comma or colon and typed a space instead. Like in the example above, where the “SUM” function should have a comma separating the two groups – B2:C5 and D2:D4 – but a space was used, so Excel doesn’t know which cells it needs to draw from.
How to Fix
Look through the formula you’re working with and make sure you use the correct separators – usually commas and sometimes colons – between cell groups, not spaces.
