Excel REF NAME and VALUE Errors Explained for Developers

An error code in a cell is a short message, and it is easy to misread. When a person typed the formula, the usual remedy is to look at the cell and fix it. When a script wrote the formulas, or when a script reads a workbook full of them, the error might be in the generated text, in the data the formula reads, or in a function the installed version does not have. This post explains the three commonest codes, #REF!, #NAME? and #VALUE!, shows how to find the cause when the workbook came from code, and gives the repair for each. It is a spoke of understanding Excel errors when data comes from code.

#REF!: the reference is gone

#REF! means a formula refers to a cell or range that does not exist. In Excel it appears when you delete a row, column or sheet that a formula used. The formula text shows it: =SUM(A1:#REF!) or =Sheet2!#REF!+B2.

In a generated workbook the causes are different.

• A deleted sheet. The script copied formulas that pointed at Data!B2, then removed or renamed the sheet Data. Every dependent formula becomes #REF!.

• A range off the sheet. Moving or inserting cells with openpyxl's move_range or insert_rows does not adjust formulas elsewhere unless you ask it to. A formula that pointed at row 5 still points at row 5 after the data moved down.

• A reference that never existed. =VLOOKUP(A2, B:C, 3, FALSE) asks for the third column of a two-column range. That one returns #REF! too.

• A copied formula running off the grid. A relative reference copied up from row 2 points at row 0.

Find the cause by looking at the formula, not the value. Press Ctrl+ (the grave accent) to show formulas, or select the cell and read the formula bar. If #REF!` is inside the text of the formula, the target is gone and you must restore it or rewrite the reference. If it is only the result, the reference is out of range for the function.

The repair is to put the formula back. Undo works only in the session where the deletion happened. In a pipeline, the lasting fix is to stop deleting sheets that formulas depend on, build formulas from the final layout, or write values instead of formulas for sheets that move.

#NAME?: Excel does not know the word

#NAME? means Excel cannot recognize something in the formula as a function, a defined name, or valid syntax. For a typed formula, the cause is usually a typo (=SUMM(A1:A5)), text without quotes (=IF(A1=yes, 1, 0)), or a named range that does not exist.

Four causes matter for code:

1. A function the version does not have. Open =XLOOKUP(...) in Excel 2016 and the cell shows #NAME?. The same workbook is fine in Microsoft 365. Check who will open the file.

2. **The missing _xlfn. prefix.** Functions added after Excel 2007 are stored in the file with a prefix, for example _xlfn.XLOOKUP or _xlfn.CONCAT. A script that writes =XLOOKUP(...) with openpyxl stores it without the prefix, and Excel shows #NAME? until the cell is re-entered. Write =_xlfn.XLOOKUP(A2, B:B, C:C) instead. The openpyxl documentation notes that formulas must use the English names and that newer functions need this prefix.

3. A localized function name. In a German Excel, you type SUMME. In the file it is always stored as SUM. A script that copies a localized string into a cell produces #NAME? everywhere. Always write English names, and separate arguments with commas, not the semicolons of some locales.

4. A defined name that did not come along. A workbook copied between files loses its named ranges, and every formula using them reports #NAME?.

To see what the script wrote, read it back:

from openpyxl import load_workbook

ws = load_workbook("report.xlsx")["Summary"]       # formulas, not values
print(ws["B2"].value)                              # '=XLOOKUP(A2,Data!A:A,Data!B:B)'

A bare =XLOOKUP( here is the sign of the missing prefix. If the stored text is right and Excel still shows #NAME?, the installed version is the cause.

#VALUE!: the wrong kind of argument

#VALUE! appears when a function receives an argument of a type it cannot use. Typical cases:

• Text where a number is expected. =A2+B2 where B2 holds "12 " with a trailing space, or "1,234" stored as text.

• A date stored as text. =B2-A2 fails if either is text that does not parse in the current locale.

• A range where a single value is expected. =A2:A5*2 in a version without dynamic arrays returns #VALUE! unless entered as an array formula.

• An empty string from another formula. =A2*2 where A2 is ="" is #VALUE!, since an empty string is text.

• Non-breaking spaces. They are text characters that TRIM does not remove (trimming spaces and non breaking spaces).

Find the offending cell with Formulas, Evaluate Formula, which steps through the calculation, or test the inputs with =ISNUMBER(B2) and =ISTEXT(B2). In data that came from code, nearly always the cause is a column that looks numeric and is text (fixing numbers stored as text in Excel).

Reading errors in pandas and openpyxl

When code reads a workbook, error cells need care. openpyxl returns the cached value when you load with data_only=True, and the error arrives as a string such as '#REF!'. pandas reads cached error values as strings the same way, for the default engine. That means an error column does not stop the pipeline; it flows through as text, and sums over it fail later or are quietly dropped.

Detect them on load:

import pandas as pd

df = pd.read_excel("report.xlsx", dtype=str)
errors = df.apply(lambda col: col.str.fullmatch(r"#(REF!|NAME\?|VALUE!|DIV/0!|N/A|NULL!|NUM!|SPILL!|CALC!)", na=False))
print(errors.sum().loc[lambda s: s > 0])

Any column with a nonzero count has error cells. Decide what to do: stop the run, replace with null and log, or fix the source. Do not let an error string sit in a numeric column. Note that a workbook written by a library but never opened in Excel has no cached values, so data_only=True returns None for formula cells (handling Excel formulas and cached values).

The related codes

Three others come up with generated data.

• #DIV/0! is a division by zero or by an empty cell. Guard with =IF(B2=0, "", A2/B2) or IFERROR when the empty result is right.

• #N/A means a lookup found nothing. It is informative, so do not hide it with IFERROR unless a blank is correct (VLOOKUP and XLOOKUP versus SQL JOIN).

• #SPILL! means a dynamic array formula cannot expand because something is in its way.

A debugging order

When a generated workbook shows errors, work in this order. Open it and count the distinct error codes; one code in many cells suggests one cause. Read the stored formulas in a few failing cells. Compare them with what the script meant to write. Check the inputs of one cell for type and spaces. Test the same formula in a fresh workbook on the same Excel version. Then fix the generator, not the output: a workbook you correct by hand will be wrong again at the next run.

When the error is not in the formula

The file is damaged. Errors in every cell, or Excel offering to repair the file, point to a damaged package (fixing Excel found unreadable content after a script export).

Calculation is manual. A workbook set to manual calculation may display stale values or no values. Press F9, or check Formulas, Calculation Options.

The data is wrong at the source. An error is sometimes the correct answer: a lookup that fails because the key does not exist is telling you about a missing record.

Regional settings differ. Decimal commas and list separators change how typed formulas are read, though not how stored ones are.

Microsoft documents the codes on its page about correcting a #REF! error, and openpyxl describes how formulas are stored in its simple usage guide. To inspect a small result as JSON, use the CSV to JSON converter.

The openpyxl documentation describes how formulas are stored.