Quick answer
The #REF!, #VALUE! and #N/A errors in Excel usually occur because of deleted cells that a formula points to, text instead of a number, or a value that doesn't exist in the lookup table. Fix them by tracing the references in the formula, checking data types, and using functions like IFERROR and XLOOKUP with a clear message instead of an error.
How to recognize the problem
- A cell shows #REF! instead of the calculated value
- The formula returns #VALUE! even though all cells are filled
- VLOOKUP shows #N/A for data that clearly exists
- The error appears only in some rows, not others
- The entire report or pivot table shows errors after copying
Most common causes
Deleted rows or columns
When you delete a cell, row, or column that a formula points to, Excel no longer knows where to get the data from and shows #REF!.
Text instead of a number
#VALUE! occurs when a formula expects a number but the cell contains text, a space, or a number stored as text.
Value not found in the lookup table
#N/A means the lookup function couldn't find the requested value, usually due to differences in spaces, hidden characters, or data type.
Misaligned references after copying
Copying a formula without absolute references shifts the lookup range and returns a wrong result or an error.
Empty cells in mathematical operations
Division by zero gives #DIV/0!, and an empty cell in some functions can cause #VALUE! or an incorrect result.
Step-by-step fix
- 1
Identify which error it is
Click the cell with the error and look at the small triangle in the top-left corner of the cell. When you hover over it, Excel shows a short explanation of the error. For more details, go to the
Formulastab and selectError Checking. - 2
Trace the references in the formula
Double-click the cell to enter edit mode. Excel colors the references in different colors, so you can immediately see which cells the formula uses. If a reference isn't colored, the cell has been deleted and that's where #REF! came from.
- 3
Fix #REF! by restoring the reference
Replace the part of the formula that contains
#REF!with the correct cell or range address. If you don't know which cell it was, undo the last change withCtrl+Zand clear the cell contents instead of deleting the entire row or column. - 4
Check the data type for #VALUE!
Select the cells the formula uses and on the
Hometab check whether the format isGeneralorNumber. Numbers stored as text are recognized by a green triangle; select the range, thenData > Text to Columns > Finishto convert them to numbers. Make a copy of the file before conversion, as leading zeros may be lost. - 5
Remove spaces and hidden characters
For #N/A, first check whether the cells have leading or trailing spaces. The
=TRIM(A2)function removes extra spaces, and=CLEAN(A2)removes hidden characters. Compare the result with the lookup table and try again. - 6
Lock the lookup range
If you use VLOOKUP or XLOOKUP, press
F4on the lookup table reference to get an absolute reference, for example$A$2:$D$100. That way, copying the formula to other rows won't shift the range and cause #N/A. - 7
Hide errors with the IFERROR function
Instead of a raw error, enter
=IFERROR(A2/B2;"no data")or=IFERROR(XLOOKUP(...);"not found"). The table remains readable, and you can immediately see where data is missing. Be careful not to hide the real problem that needs fixing. - 8
Test the formula on a small sample
Copy the formula to a helper column and apply it to a few rows with known results. When it matches, copy it to the entire range. This way you avoid the error affecting hundreds of rows at once.
When the error persists after fixing
If the error returns after fixing, check whether the sheet is protected or whether an external file that has been moved is involved in the formula. For shared workbooks in Microsoft 365, pay attention to whether someone else is changing the same range at the same time. For large tables with thousands of rows, use the filter option on the Data tab and filter by errors, then fix them in batches. If the workbook was created by merging multiple files, check whether the formulas were transferred with incorrect references. Save a copy of the file before major changes so you can revert to the previous state.
How to prevent it
- Use absolute references for lookup tables and named ranges
- Instead of deleting rows and columns, clear only the cell contents
- Import data with `Data > From Text/CSV` instead of copying from other programs
- Occasionally check formulas with `Formulas > Show Formulas` before sharing the file
When to call a professional
If errors affect a large number of formulas across multiple sheets or the workbook contains macros and external links, contact IT support to review the file structure before major changes.
IT infrastructure maintenance · Servers, networks, backup, security and helpdeskFAQ
What does #REF! mean in Excel?
#REF! means that the formula points to a cell, row, or column that no longer exists, usually because it was deleted. Fix the reference in the formula or undo the deletion with `Ctrl+Z`.
Why does VLOOKUP return #N/A when the value exists?
The most common reasons are differences in spaces, hidden characters, data type, or an incorrectly set fourth argument for exact match. Clean the values with the `TRIM` and `CLEAN` functions and check that both sides are of the same type.
Is it safe to use IFERROR?
Yes, but IFERROR hides the error and can mask the real problem in the data. Use it to display a clear message, not as a permanent solution for incorrect references.