How to Fix the #NAME? Error in Google Sheets

The Identity Crisis: Why the Error Happens

The #NAME? error is a sign that Google Sheets is confused. It encountered a word in your formula that it doesn't recognize as a valid command or a known location. Unlike a math error (like dividing by zero), this is a vocabulary problem. The spreadsheet engine is essentially telling you, "I don't know what this word means."

#NAME?

Common Root Causes

Most #NAME? issues stem from four specific mistakes:

• Misspelled Functions: Writing =VLOOKUPP() instead of =VLOOKUP().
• Naked Text: Forgetting to wrap words in double quotes, which makes Sheets think the text is a hidden variable.
• Deleted Named Ranges: Referencing a range like =SUM(Q4_Revenue) after you've deleted that name from the manager.
• Unsaved Scripts: Calling a custom Apps Script function that hasn't been saved or authorized yet.

Fix 1: Catch Typos with Autocomplete

Spelling mistakes are the most common culprit. If you're rushing and type =AVERGE(B2:B50), Sheets won't realize you meant AVERAGE. It will simply fail.

The solution: Let the software do the work. When you start typing a function, wait for the blue autocomplete menu to pop up. If you don't see your function listed after the first three letters, you've likely started with a typo. Always click the suggestion to ensure the spelling is perfect.

// Incorrect: Sheets thinks "SUMM" is a named range
=SUMM(A1:A50)

// Correct: The standard math function
=SUM(A1:A50)

Fix 2: Put Your Text in Quotes

This is a classic trap. If you want to check if a cell contains the word Pending, you might write =IF(A1=Pending, 1, 0). Without quotes, Sheets looks for a function or a Named Range called "Pending." When it finds neither, it throws the #NAME? error.

The solution: Always wrap literal text in double quotes (" "). This tells the engine to treat the characters as a simple string of text, not a command.

// Incorrect: Throws #NAME? because 'Paid' isn't defined
=IF(C2=Paid, "Yes", "No")

// Correct: Uses quotes to identify the text string
=IF(C2="Paid", "Yes", "No")

Fix 3: Audit Your Named Ranges

Named ranges make formulas like =SUM(Tax_2023) much easier to read than =SUM(Sheet2!G2:G100). However, if you accidentally delete that range or misspell it in your formula, the calculation breaks instantly.

How to verify:

• Open the Data menu and select Named ranges.
• Scan the sidebar to ensure your range exists and matches your formula's spelling exactly.
• Check for underscores. If your range is Total_Sales but you typed TotalSales, the formula will fail.

Fix 4: Sync Your Apps Script

Custom functions created in Apps Script are powerful, but they are prone to #NAME? errors if the spreadsheet and the script editor lose sync. This often happens in new projects.

Troubleshooting steps:

• Navigate to Extensions > Apps Script.
• Check your function name. JavaScript is case-sensitive; calculateTax() is not the same as calculatetax().
• Hit the Save icon (diskette) in the script editor. The spreadsheet cannot "see" your code until it is saved to the Google server.
• Refresh your browser tab to force the sheet to reload the custom function library.

/**
 * Simple custom function example
 */
function MULTIPLY_BY_TEN(input) {
  return input * 10;
}

// Use in your sheet as: =MULTIPLY_BY_TEN(15)

Fix 5: Watch for Regional Syntax

Spreadsheets are localized. In the US, we use commas to separate arguments: =SUM(10, 20). In many European countries, the comma is used as a decimal point, so the function separator becomes a semicolon: =SUM(10; 20).

If you paste a formula from a tutorial and get a #NAME? error, check your locale settings in File > Settings. You may need to swap your commas for semicolons to match your region's requirements.

Pro Tips for Error-Free Formulas

• Use the F1 Help Box: Click inside a function's parentheses and press F1. If the help documentation doesn't appear, Sheets doesn't recognize the function name.
• Paste as Plain Text: When copying formulas from websites, use Ctrl+Shift+V. This prevents hidden HTML formatting from sneaking into your formula bar and breaking the syntax.
• Check the Formula Bar: Sometimes a stray period or an accidental space at the very beginning of a formula (e.g., = .SUM) triggers the error.