This article will cover all you must know about identifying ways to fix and prevent #NAME? mistakes in Excel.
If you’ve been working with Excel formulas for some time it’s likely that you’ve encountered the common #NAME? errors. Excel displays this error in order to help us solve the issue with the formula, however it’s not clear what’s wrong with the formula.
The error ‘#NAME?’ occurs in the cell in case Excel does not recognize your formula or the arguments in your formula. It means that something wrong or not working in the formula’s characters employed and needs to be fixed.
There are a variety of reasons you might see #NAME? mistakes in Excel. The most common reason is simple spelling error of the formula , or function. However, there are other causes as well, such as typing the wrong name of the range, incorrect spelling of the cell range with quotation marks missing within the text of the formula, a missing the colon in a cell range or formula that is not the correct version. This article will discuss the most frequent problems that could result in a #Name error in Excel and the best way to correct the problem.
Table of Contents
Misspelled Formula or Function Name
The most frequent reason for #Name errors is the spelling error of the name of the function or when the function does not exist. If you enter an incorrect syntax for a function or formula the error #Name is visible in the cell where the formula was entered.
The following illustration in the following example, in the following example, COUTIF function is used to determine how many times that an item (A1) is repeated within the table (column A). However, the name of the function “COUNIF” is misspelled as “COUNTIIF” with double ‘II and ‘. This is why it returns the name of the item? error.
All you need to do is change your spelling, and then the error will be corrected.
To avoid this mistake to avoid this error, you can follow the formula suggestions instead of manually entering the formula. When you begin writing the formula Excel displays a list of functions that match the formula you’re typing in the manner the image below.
Double-click on any from the functions suggested, or hit TAB to accept the suggestion of a function by autocomplete. Enter the argument and hit Enter.
Incorrect Cell Range
Another reason for the error #Name is due to the range of cells being not entered correctly. This error can be caused by a mistake in which you did not include the colon (:) in a range or if you used the incorrect combination of numbers and letters to create the range.
In the following example, the range reference does not have the colon (A1A6 instead of A1:A6) The result is a #NAME error.
In the same instance the cell range contains the incorrect combination of numbers and letters and returns the error #NAME.
The range that was used in cell A7 has been adjusted to give the correct outcome:
Misspelled Named Range
Named ranges are descriptive term that is used to describe specific cells or a range of cells, instead of the cell’s address. If you do not spell a named number in the formula, or use a name that isn’t defined within your Excel spreadsheet then the formula will produce the #NAME? Error.
In the following example C4:C11, the range is referred to as “Weight”. If we attempt to apply this name to calculate the cell’s range we receive the #Name? error. The reason is that the name of the range “Weight” is misspelled “Wieght” and the SUM function in B2 produces the #NAME? error.
In this case, we see the #Name error because we attempted to use the name range that isn’t defined “Load” in the formula. Named area “Load” doesn’t exist in the sheet we are working on, therefore we get an error #NAME.
Below Correcting your spelling for the specified cell range corrects the problem and displays 46525 as the weight total for the meat.
To avoid this mistake, you could use the ‘Paste Name dialog box to add names of ranges in the formula instead of writing the name. If you have to enter your range’s name in your formula, use the function key F3 to display the names of the ranges within your workbook. In the Paste Name dialog box, choose the name, then click OK to insert named ranges into the formula.
So you don’t need to type in the name manually, to prevent the error from occurring.
Check the Scope of Named Range
Another reason why you may encounter an error message stating “#NAME? ” is when you attempt to refer to a locally-scoped named range from a different worksheet in the workbook. If you’re defining the name of a range, you can decide whether you want the name range to be a part of the range to be a whole workbook or just to one particular sheet.
If you’ve defined an area of scope for the name range on a specific sheet, and then try to access it from a different worksheet you’ll be able to see the #NAME? Error.
To determine the scope for the names of ranges, select the “Name Manager option on the tab ‘Formula’ and press the keys Ctrl+F3. It will display all named ranges and table names within the workbook. You can then create names, edit or delete the names already in place.
While you can examine the scope of the named ranges using the “Name Manager’ dialog box, you aren’t able to alter the scope. You are able to set the scope while creating the named range. Make the correct changes to the named range or create a new named range in order to correct the problem.
Text Without Double Quotes (” “)
If you enter a text value that is not enclosed in double quotes within formulas can also trigger an error code #NAME. If you type in any text value in formulas, make sure you include the values in two quotation marks (” “), even if you’re using the space.
For instance this formula attempts to find the amount of “Pig” in the table by using the VLOOKUP function. However, in B13 the text string ‘Pig is entered with no any double quotation marks (” “) in the formula. This formula will return the #NAME? error, as illustrated below.
If there are quotation marks around a number, Excel will treat it as an unstructured text string. However, if a text string isn’t enclosed in double quotes, Excel considers it as an unnamed formula or range. If that named range or function cannot be identified, Excel returns the #NAME? error.
Simply wrap the text “Pig” in double-quotes in the formula, and the error #NAME will go away. Once quotes are added after which the VLOOKUP function will return the number of the Pig as ’15’..
Important: the text value must be enclosed by Straight double quotation marks (i.e. “Dog”). If you type in the text with clever quotes (i.e. “Dog”), Excel will not recognize them as quotes, and instead produce the #NAME? error.
Using New Version Formulas in Older Excel Versions
The new functions in the latest Excel version aren’t compatible with earlier Excel versions. For example, new functions like CONCAT, TEXTJOIN IFS, SWITCH etc. were introduced in Excel in 2016 and 2019.
If you attempt to use these new functions in earlier Excel versions such as Excel 2007 or 2010, 2013 or open a document with these formulas in older versions that you’ve tried, you’ll likely encounter an error code #NAME. Excel does not recognise these functions since they’re not available in the version.
Unfortunately, there’s no solution to this problem. It is impossible to use more recent formulas in the older versions of Excel. If you’re opening a workbook using an older version of Excel, make sure that you do not include any of the latest functions within the document.
Additionally, if you save a file that contains a macro with an equation using the ‘Save As option, but you didn’t activate the macros on the file you saved You’ll probably encounter an error code #NAME.
Finding all #NAME? Errors in Excel
Let’s say that you get an enormous spreadsheet from a friend and you’re unable to do some calculations because of mistakes. If you’re not sure the exact location where your errors occur There are two methods you can employ to identify #NAME errors in Excel.
Using the Go To Special Tool
If you’re looking to identify any and every error on your worksheet You can do this using Go To Special. Go To Special feature. Its Go To Special Tool finds not just the #NAME? mistakes, but also all sorts of mistakes in spreadsheets. This is how to fix them:
In the spreadsheet, open it up and select the cells you wish to select cells that are not correct Then, click the “Find and Select” icon within the Editing section of the tab ‘Home’.
Alternately, press F5 to or F5 to open the Go To dialog, then click the option ‘Special.
In either case, it opens the “Go To Special’ dialog box. Select the option ‘Formulas and then de-select the other options available under Formulas and finally, leave the option that says “Errors” selected. Then, click ‘OK’.
This will pick all cells with any type of error as illustrated below. Once the cells with errors are chosen, you are able to deal with them in the way you like.
Using Find and Replace
If you are only looking to identify the errors that are #NAME on the sheet You can make use of Find and Replace. Find as well as Replace tool. The steps are as follows:
Then, you must select the range or all worksheets (by using Ctrl +A) where you wish to locate the error in the Name. After that, select ‘Find and Select’ under the Home tab, and choose ‘Find’, and press the Ctrl+F.
Within the Find and Replace dialog box in the dialog box, type #NAME? in the ‘Find What’ field, then click the ‘Options’ tab.
Choose “Values” from the ‘Look in’ drop-down and then select the option of ‘Find Next or Find All’.
If you choose “Find Next”, Excel chooses the cells one at a time with the name error, which is able to be dealt with in a separate manner. If you choose the option ‘Find All’, a second box will be displayed beneath the Find and Replace dialog that shows all cells that have the error #NAME.
Avoiding #NAME? Errors in Excel
We’ve seen the most frequent cause of the #NAME error in Excel and the best way to avoid and fix these errors. The best method to avoid the #NAME errors is to utilize functions in the Wizard to input formulas into the spreadsheet.
Excel Function Wizard allows you to create functions quickly and efficiently. It gives you the list of functions that have syntax (range and criteria) that you can apply. Here’s how:
The first step is to select the cell in which you wish to place the formula. You can then navigate to the Formulas tab and then click on the “Insert Function option’ on the Function Library group , or click on the Function Wizard button ‘fx’ found on the toolbar next to the bar for formulas.
You can also select an option from any of the categories under the Function Library’ under the tab ‘Formulas.
In the Add Function Dialog box click the drop-down menu beside “select a category” and select one of the 13 categories that are listed. The functions that fall under the category you choose will be displayed in the ‘Select a Function’ box. Choose the function you wish to add and then select “OK”
Alternately, enter the formula (you could use the formula as a partial title) within the search for Functions field and look it up. Double-click on the function or select ‘OK’.
This will open this Function Arguments Dialog box. In this dialog box, you must input the arguments of the function. For instance, we would like to find the amount of the ‘Pig’ the table by using the VLOOKUP function.
The Look_value entered is ‘Pig’. For Table_array, you may directly type in the table’s range (A1:D9) within the fields, or click the upward arrow inside the field to choose the range. The Co_index_num field is set to as ‘3’, and Range_lookup is set to “TRUE”. After you have entered all arguments, click the OK button.
The result will be displayed in the cell you have selected and the formula that you have completed in the Formula bar.
Utilizing the Formula Wizard can save you lots of time and also help you avoid the #NAME? mistakes in Excel.