Excel Error Messages
Excel error messages can throw a wrench into your plans. Let’s walk through some of the most frequent errors you will encounter, and how to prevent them in the first place.
#N/A Error
The value error occurs when your formula is unable to return a result or cannot execute. Usually, this is due to a misplaced character (check your commas and colons), logical command that doesn’t make sense, or a lookup/match that doesn’t exist.
An easy solution to the value error is to use formula builder to test complex formulas and confirm that it returns a value. In the following example, the HLOOKUP formula can’t find July since it isn’t in the table. The VLOOKUP formula can’t find benefits in a related issue since it isn’t in the table.
#NAME Error
The #NAME error occurs when Excel can’t read a reference in a cell. If you put quotes around a cell reference, Excel will interpret it as text instead of a reference. Alternatively, if you mistype part of a formula, you may get this reference (example: SMUIF() or VLOKUP()). Similar to #N/A, an easy solution to the value error is to use formula builder to test complex formulas and confirm that it returns a value.
#DIV/0 Error
One of the most common errors in Excel, #DIV/0 occurs if you attempt to divide a value by 0. It is very common in spreadsheets with dynamic data such as a Profit and Loss statement where not every cell populates. An easy solution is to use the formula =IFERROR() in any formulas with division. I would recommend returning either “0” or “n/a.”
#REF Error
This error occurs when a reference in a formula no longer exists. If you linked a formula to A2 and delete the A column, it will return this error. The common cause is deleting rows or cells. To prevent the error, update formulas pointing to a row or column prior to deleting.
Have any questions on Excel error messages? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.
