Excel Formula Not Working? Fix #REF!, #VALUE! and #NAME?
Breaks the formula into parts, finds separator, locale and range problems, and explains what each part returns. Systematic diagnosis for #REF!, #VALUE! and blanks.
My Excel formula does not work. Do not rewrite it — first find out why it is broken. EXCEL VERSION: [VERSION] UI LANGUAGE: [LANGUAGE] SEPARATOR: [SEPARATOR] ERROR: [ERROR] FORMULA: [FORMULA] DATA STRUCTURE: [DATA] STEP 1 — DECOMPOSE Break the formula into its nesting structure. Per function: name, arguments received, which argument points at which range. STEP 2 — THE FOUR CLASSIC FAILURES 1. SEPARATOR: does my Excel use commas or semicolons? Does the formula match? 2. FUNCTION NAME LANGUAGE: is the function name valid in my UI language? (English names return #NAME? in a localised Excel.) 3. RANGE: hard-coded range, table, or named range? Has a row been inserted? Is the range shifting? 4. TYPE: is a number stored as text, is a date stored as text, is there a stray space? STEP 3 — DIAGNOSIS State the cause in one sentence. If there is more than one, order them most likely first. STEP 4 — CORRECTED FORMULA Give the fixed formula. If you suggest a newer function (XLOOKUP, FILTER, LET), say that it will not work in files shared with accounting or suppliers, and give the legacy alternative too. STEP 5 — CHECK Tell me the expected result and one test row: which row should I try it on? Give the formula on one line, copy-pasteable. Keep the explanation separate.
Ask for the formula inside a code block so copy-paste preserves the leading equals sign.
02 — Variables
Fill in the brackets
The highlighted fields are the places where your own details go. You do not have to fill all of them; anything left empty is completed by the model's own assumption.
[VERSION]
[LANGUAGE]
[SEPARATOR]
[ERROR]
[FORMULA]
[DATA]
03 — How to use
How to use it, step by step
- 01
State your UI language and separator — half of all AI formula failures are those two differences.
- 02
Paste the formula exactly as it appears, including the leading =.
- 03
Describe the data column by column. A hint like "amount looks like text" speeds up diagnosis dramatically.
- 04
Test the corrected formula on a single row before dragging it down a column.
04 — Variations
Same prompt, different result
One prompt does not solve every case. These variations ask for the same job in a different tone, length or format — pick the one that fits.
Locale converter
Translate the formula below for a localised Excel: translate function names and convert separators to semicolons (or to the convention I specify). Then translate it back and check. Show both versions side by side and say which function translation is riskiest. Formula: [FORMULA]
For language-caused errors.
Google Sheets
Do the same job in Google Sheets. If a function does not exist there, give the alternative. If an array formula (ARRAYFORMULA) is needed, show it. Separator: comma. Formula: [FORMULA].
For porting a workbook.
05 — Tips
Notes that take the result up a level
- A #NAME? error is almost always a language problem: an English function name in a non-English interface.
- Numbers stored as text are the sneakiest cause. Clean them with VALUE() or Paste Special → Values.
- In large workbooks, volatile functions (INDIRECT, OFFSET) lock the file while recalculating. AI loves to suggest them.
06 — FAQ
Frequently asked about AI Errors
Why does the AI formula return #NAME?
Because AI writes formulas with English function names (IF, COUNTIF, VLOOKUP). In a localised Excel the same functions have local names, so the English name is unrecognised and returns #NAME?. The fix is not the formula but the function names — step 2 checks for exactly that.
Comma or semicolon?
It depends on the UI locale: locales where the decimal separator is a comma use semicolons as argument separators. English (US) uses commas. If the file opens on another machine, the difference shows up as #VALUE! or #NAME? — stating the separator up front removes the error.
What usually causes #VALUE!?
Three things: text that looks like a number (leading or trailing space, apostrophe, thousands separator), a date stored as text, and mismatched separators. Test in order: =ISNUMBER() on the cell, then =LEN() to find invisible spaces.
Next
You may also find these useful
Prompt That Stops AI Hallucinations (Claim Labelling)
Splits the answer into three layers: verifiable fact, inference and guess. The model learns to say "I don't know", and you see which sentences you can trust.
Intermediate ChatGPTClaudeChatGPT Refuses to Answer? A Prompt That Opens the Refusal
Separates the cause of a refusal (safety filter, over-caution, vague request) and builds the right frame for each. It gets your work done without wandering.
Intermediate ChatGPTClaudePrompt for When the Model Forgets Your Instructions Mid-Chat
The model writes your rules back in its own words, then checks every reply against that list. Still working 40 messages later.
Intermediate ChatGPTClaudePrompt That Turns a Generic AI Answer Into Real Analysis
Bans empty lines like "it is important" and demands a claim, a counter-claim and the missing side for each point. Real analysis in three rounds.
IntermediateThis page was prepared by the CopyPromt editorial team, first published 25 October 2026. Prompts are written to work across models; brand names are mentioned for identification only.