Excel #VALUE! and #DIV/0! Errors: How to Fix Them

Excel errors such as #VALUE! and #DIV/0! usually mean a formula received an unexpected type of data or tried to divide by zero. These steps help you find the cause, correct the formula or source data, and prevent the same error from returning.

What You’ll Need

The workbook containing the error, preferably with a separate copy saved before making broad changes.
Excel for Windows, Mac, or the web. Menu names can vary slightly by version.
Permission to edit the affected cells, if the sheet is protected or shared.

Step-by-Step

Identify the exact error and affected formula. Select a cell showing an error and read the formula in the formula bar. Note the error text and the referenced cells. If you see a green triangle, select the cell and review Excel’s warning, but do not accept a suggested conversion without checking that it is appropriate.
Trace the formula’s inputs. Select the cell, then choose Formulas > Trace Precedents to display arrows to cells the formula uses. Follow the arrows and inspect each input, including hidden rows or columns. You can also use Formulas > Evaluate Formula to step through a complicated calculation and see where it goes wrong.
Fix #DIV/0! by checking the divisor. This error appears when a formula divides by zero or by a blank cell that Excel treats as zero. For a formula such as =A2/B2, check whether B2 is blank, contains 0, or calculates to 0. Enter the intended nonzero value if one is missing. If zero is a valid situation, decide what the result should mean rather than changing the data just to hide the error.
Handle a zero divisor when it is expected. If the result should remain blank until a divisor is entered, use =IF(B2=0,"",A2/B2). To display a message instead, use =IF(B2=0,"No denominator",A2/B2). In either formula, replace the example references with your own. If the divisor is calculated, check that calculation too: a rounded or filtered value may be zero even when its source data is not.
Fix #VALUE! by checking data types and formula arguments. This error often means a formula expected a number or date but received text, a space, or an incompatible value. Check the referenced cells for entries such as 12 kg, numbers stored as text, a leading apostrophe, or a hidden space. Remove units typed into numeric cells and put them in a separate label or cell. If appropriate, convert text numbers with =VALUE(A2), or use Data > Text to Columns and finish the wizard to convert a whole column. Test conversions on a copy first.
Check text and date formulas. A function that joins text, adds numbers, or calculates dates may fail if one input is not in the expected format. Check for missing quotation marks or parentheses, and ensure text is enclosed in quotation marks when required. Excel dates need to be valid date values, not ambiguous text; re-enter the date in a format recognized by your Excel settings or convert the source column using Data > Text to Columns. Avoid relying on a date format that may be interpreted differently by another regional setting.
Investigate other common errors. For #N/A, check whether a lookup value exists and whether lookup ranges and match settings are correct. For #REF!, edit or rebuild the formula after checking whether a referenced row, column, or sheet was deleted. For #NAME?, check function spelling, named ranges, and quotation marks around text. For #NUM!, check that the inputs are in the valid range for the calculation. For #NULL!, review range separators and spaces in the formula.
Use IFERROR only when hiding every error is acceptable. For example, =IFERROR(A2/B2,"") displays a blank if any error occurs. This can make a report cleaner, but it can also conceal a broken reference or bad input. Prefer a targeted test, such as the earlier IF formula for a zero divisor, when you know which condition should be handled.
Recalculate and verify the result. Press Enter after editing the formula, then check that the answer is plausible and that dependent cells update. If results do not change, select Formulas > Calculation Options > Automatic, if that option is available. Save the workbook, close and reopen it if needed, and confirm the error has not returned.

Common Mistakes

Replacing source values with guesses: Do not put 1 in a blank divisor just to remove #DIV/0!. That changes the calculation and may make the output misleading.
Removing every error with IFERROR: A blank result can hide a misspelled function, missing lookup, or deleted reference. Use error handling only when the fallback is meaningful.
Assuming a cell that looks numeric is a number: Text can look identical to a number. Check the formula bar, cell format, and whether a conversion succeeds.
Changing formats instead of values: Applying a number format does not always convert text into a numeric value. Use a deliberate conversion and verify the result.
Editing a shared formula without checking its range: Copying a fix across a column can shift relative references. Test one row, then inspect the copied formulas and any absolute references such as $A$1.

Tips

Keep raw imported data separate from cleaned data so you can compare values and undo conversions safely.
Use Formulas > Show Formulas to review formulas across a sheet; select it again to return to calculated results.
Use Home > Find & Select > Go To Special > Formulas and select errors to find formula cells with errors on a sheet.
For formulas copied down a table, use consistent input types and check the first, middle, and last rows after filling.
When sharing a workbook, include a clear note for intentional blanks or error messages so another person does not mistake them for missing work.

Troubleshooting

The error remains after correcting the visible cell: A referenced formula may still return an error or zero. Trace precedents and inspect each step, including cells on other sheets. If there are several layers, use Evaluate Formula to locate the first incorrect value.

VALUE does not convert a text entry: The cell may contain extra spaces, a nonbreaking space, a currency symbol, or a decimal separator that conflicts with your regional settings. Try cleaning a copy of the text, check the separator, and use Text to Columns with the correct import settings. Do not remove punctuation unless you know what it represents.

A lookup still returns #N/A: Compare the lookup value with the source value for extra spaces, different data types, or small spelling differences. Confirm that the lookup range includes the desired result column and that approximate matching is not being used unintentionally.

Excel displays old results: Check whether calculation is set to Automatic. If the workbook is in Manual mode, choose Formulas > Calculation Options > Automatic, then recalculate. On some keyboards, F9 recalculates formulas; laptop shortcuts may require a function key.

You cannot edit the formula: The sheet may be protected, the file may be read-only, or another user may have restricted editing. Check the workbook status and ask its owner for access rather than trying to bypass protection.

Frequently Asked Questions

What is the difference between #VALUE! and #DIV/0!?
#VALUE! usually indicates an incompatible value or argument, such as text where a number is expected. #DIV/0! means the formula divides by zero or a blank divisor.

Should I use IFERROR to remove Excel errors?
Use it when you have chosen a meaningful fallback and are comfortable treating all errors the same way. Otherwise, correct the cause or use a targeted test so useful errors remain visible.

Why does a cell show a number but Excel treats it as text?
It may have been imported with a leading apostrophe, spaces, or formatting that preserved text. Convert it deliberately, then check that a calculation recognizes it as a number.

How can I find all error cells in a worksheet?
Choose Home > Find & Select > Go To Special > Formulas, select Errors, and choose OK. Excel selects formula cells that currently contain errors.

Why does #DIV/0! appear when the divisor looks nonzero?
The divisor may be a formula that returns zero, or a small value rounded or displayed as zero. Inspect its full value and formula rather than relying only on the displayed number.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →