In this second half of my Excel Error Messages training, we’ll explore #N/A, FALSE and circular cell references.
#N/A actually means no answer. The most common place you’ll see an #N/A is when you use a VLookUp function. For those unfamiliar with the term VLookUp, it's a function that allows you to look up a selected text string in a list and return some information about it. For example, you can look up a customer number and be returned a customer name. There is an optional argument in VLookUp that will respond #N/A if it could not find the string.
One of the most common questions is how to get Excel to tell you something other than #N/A. Meet the Excel function: ISNA. ISNA is a question that asks, “Will this formula resolve to no answer or N/A?” So, instead of returning #N/A if an old product number doesn’t show up in a new product list, it can show TRUE or FALSE under the Discontinued column.
Access more time-saving tips in the concise, colorful Executive Summary of my training session on Microsoft Excel Get your copy now...
Sometimes we just see that FALSE message show up in a cell and we don’t know why. While it can be a legitimate cell value, as in the above ISNA example, it can sometimes show up when we make a mistake typing in a formula. If, for example, we try to type in =A1>B1 and it is not the case, then Excel tells us FALSE. In this case I would wonder if the formula writer meant to type in A1/B1. If you look at your keyboard, those two keys are pretty close.
Circular Cell References
One of the most aggravating messages to get is the circular cell reference warning. A circular cell reference warning occurs when some part of the formula is referring back to the cell that the formula is being typed into. So, for example, in this case, we are trying to add C1 to A1 and B1, but we’re typing the formula into C1.
It would be awesome if all circular cell references were this easy to resolve. If it’s not obvious to you, click OK and Excel will attempt to show you where the problem is.
Just in case you are curious, there are applications for a circular cell reference, which is why it gives you the option. Most of the time, however, it is an error.
Don't let Excel error messages bring your project to a halt! With Microsoft Excel: Time-Savers for Every Skill Level, you'll learn:
- How the Autofill, Autocorrect and Custom Lists functions can save you time
- The many uses of macros
- How to import data into Excel
- How templates will give you success every time
- Ways to dissect and troubleshoot formulas to understand what’s really going on
- VLookUp – an Excel guru’s secret weapon
- And Much More!