Machines & humans don’t speak the same language. Even the very imagination when filmed leads to mass-scale destruction (We all would have watched at least one film from the Terminator series).
Also read: How to Fix #REF Error on VLOOKUP in MS Excel?
But here’s the catch! Humans don’t do well when it comes to recording massive amounts of data & machines don’t do well in taking humane decisions based on the inferences extracted from the recorded data.
But we all have names! These names are not limited to us humans, but also to all things that we interact with on a daily basis leaving no exception. Think about wanting something without a name!
Ever wonder what happens if we use these names incorrectly in MS Excel? You’ve guessed it right, the #NAME error appears. Let’s explore further in detail about this.
Fixing the #NAME Error:
Listed below are some of the reasons which cause the #NAME error to appear & the technique to fix each of them.
- Misspelt Formula
- Incorrect References
- Exclusion of Double Quotes
Misspelt Formula –
Remove a letter or add something to the name of the formula & MS Excel shall go bizarre in trying to identify what it means, ending up in the #NAME error. In the below example the formula SUM is misspelt with an additional ‘U’ as SUUM that has caused the error.
Remove the additional ‘U’ & the error shall be cleared.
Incorrect References –
When the cell address referred to within the formula is entered incorrectly, that would also end up in the #NAME error. Have a look at the below example in which, the cell reference to find the average of the given set of numbers has AI given in the range.
All cell addresses in MS Excel are given as a combination of an alphabet for the column position & a number for the row position. But in this case, it could be seen that the cell given in the selected range contains all alphabets & no numbers.
The objective here is to find the average of numbers from AI3 to AI6. So, let us replace AI with AI6 in the above formula & see what happens.
Now it works like a charm giving us the result for the average.
Exclusion of Double Quotes –
All formulae in MS Excel are sensitive to the usage of double quotes within them. It creates a great deal of difference in the functionality of the formula when a pair of double quotes are included or excluded.
In the below example, let us see how the exclusion of double quotes shall affect the functioning of an IF statement. The objective here is to display ‘YES’ when the value in cell AI3 is greater than 5 & ‘NO’ if it ain’t.
But, both these values YES & NO have not been included within the double quotes. What happens now is the absence of double quotes would indicate to MS Excel that there is another formula nested within the IF statement.
It is known by all that there is no formula by the name of YES or NO in MS Excel. After some searching, MS Excel too reconfirms this & would display the #NAME error.
To get this sorted, include YES & NO within a pair of double quotes. Hit ENTER & the result shall be displayed in no time.
Conclusion:
Now that we have reached the end of this article, hope it has elaborated on the ways to tackle #NAME error in MS Excel. Here’s another article which details how to sort out #REF error on VLOOKUP. There are numerous other articles too in QuickExcel that can come in handy for those who are in looking to level up their skills in MS Excel. Ciao!