How to fix the #NAME? error in Excel
Formula (Excel and Google Sheets)
=AVERAGE(B2:B9)#NAME? means a word in the formula isn't recognized, usually a misspelled function: =AVERGE(B2:B9) fails, =AVERAGE(B2:B9) works.
- Result on the example
- 75.5 in
D2 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in D2. Change any input and the result updates, or edit the formula to experiment.
How it works
Every word in a formula has to be a function, a named range or a cell reference. AVERGE is none of these, so the formula stops with #NAME? before calculating anything. Spelled correctly, AVERAGE returns 75.5. Picking the function from the list that appears as you type avoids most typos.
=AVERAGE(B2:B9)Result75.5
B2:B98 rows × 1 col: 78, 64, 91, 85, …A range: 8 cells in one column.
AVERAGE(B2:B9)75.5AVERAGE: Arithmetic mean of the numbers.- number1
B2:B98 rows × 1 col: 78, 64, 91, 85, …
- number1
In Google Sheets
The same formula works in Google Sheets: =AVERAGE(B2:B9). We ran it in Google Sheets mode on the same data and it returns 75.5.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(B2:B9)/COUNT(B2:B9) | 75.5 | The same average from SUM and COUNT. |
Common mistakes
- Text without quotes is read as a name too:
=COUNTIF(A2:A9,Paid)needs "Paid". - A function your version doesn't have, such as XLOOKUP in Excel 2016, also returns #NAME?.
- A range missing its colon (
B2B9) or a named range that was deleted gives the same error.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Fix the #NAME? error
D2, calculate the average score in B2:B9, with the function name spelled correctly.Questions
How do I find the part Excel doesn't recognize?
Use Formulas, Evaluate Formula to step through it, or retype the function names and watch for the tooltip that appears for real functions.
Why does Google Sheets show #NAME? for a formula that works in Excel?
Some functions exist in only one app, such as TEXTSPLIT in Excel and QUERY in Google Sheets. Use the other app's equivalent.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.