Today’s topic is Excel formula Error Codes.
Not long after learning how to create formulas in Excel, you probably started to see the ever-so-annoying formula error codes. This post will hopefully demystify these error codes and give you some techniques on how to deal with them.
There are 8 formula error codes in Excel. I’ve created a table below with their definitions and common troubleshooting techniques. Some of the troubleshooting actions are pretty obvious, but I included them for completeness sake.
| Error Code | Definition | Possible Fix |
|---|---|---|
| ####### | Column is not wide enough to display all the characters in a cell, or a cell contains negative date or time values. | Widen the column or fix the date error. |
| #DIV/0! | A number is divided either by zero (0) or by a cell that contains no value. | Don’t divide by zero. |
| #N/A | A value is not available to a function or formula. | Check your formula carefully for misplaced commas or missing information. |
| #NAME? | Excel does not recognize text in a formula. | Make sure your formula or named range is spelled correctly. |
| #NULL! | You have specified an intersection of two areas that do not intersect. | This one is pretty rare, but you might have left out a comma in a SUM function or other similar function that uses multiple range inputs. |
| #NUM! | A formula or function contains invalid numeric values such has numbers too big or small for Excel. Also, using iterative functions (IRR or RATE) without iterations turned on. | Check: 1) Any formula errors that could result in ridiculous numbers 2) Any places where there is text when there should be a number 3) If iterative calculations are on. |
| #REF! | A cell reference is not valid. For example, you may have deleted cells that were referred to by other formulas. | If possible, undo any deletion or pasting of cells that caused the error. Failing that, close the file and don’t save your changes. |
| #VALUE! | Your formula includes cells that contain different data types. | Make sure there is no text where a number should be. |
I hope this table proves helpful to you and gives you more confidence to work in Excel and not be afraid of errors.
Loading ...