Microsoft Excel — #VALUE! Error — What It Means & How to Fix It

What Does This Error Mean?

The #VALUE! error means Excel received the wrong type of data in a formula. For example, a formula that expects numbers was given text instead. Think of it like trying to add the word 'apple' to the number 5 — it simply does not compute. Excel cannot perform the calculation, so it shows #VALUE! to let you know something in the formula does not make sense.

Affected Models

Microsoft Excel 2016
Microsoft Excel 2019
Microsoft Excel 2021
Microsoft 365 Excel

Common Causes

A formula is trying to do math on a cell that contains text instead of a number
A date cell is stored as text (looks like a date but Excel does not recognize it as one)
Extra spaces are hidden inside a cell, making a number look like text to Excel
An array formula is being used without pressing Ctrl + Shift + Enter to confirm it
A cell referenced in the formula is blank or contains a special character instead of a number

How to Fix It

Click the cell showing #VALUE! and read the formula in the formula bar. Identify which cells it is referencing — for example, =A2+B2. Then click each of those referenced cells and look at what is actually in them.

Look for cells that seem to have numbers but are left-aligned — left-aligned numbers in Excel are often stored as text, which causes #VALUE! errors.
Select the cells that contain the numbers you are working with. Go to Data > Text to Columns, click Finish without changing anything. This forces Excel to re-read those cells and convert text-formatted numbers to real numbers.

This quick trick often fixes the most common cause of #VALUE! — numbers that were imported or pasted as text.
Use the TRIM function to remove hidden spaces. In an empty cell, type =TRIM(A2) where A2 is the problem cell. If TRIM gives you a clean number, copy that result and paste it as a value back into the original cell.

Hidden spaces — especially ones imported from other systems — are one of the sneakiest causes of #VALUE! errors.
If you suspect a date is stored as text, select the date cells and go to Format Cells (Ctrl + 1). Under the Number tab, make sure the category is set to Date, not Text. Then re-enter one of the dates manually to confirm Excel is accepting it as a real date.

Dates stored as text will not work in any date calculations — Excel treats them like plain words.
Wrap your formula with IFERROR to handle the error gracefully while you investigate. For example, change =A2+B2 to =IFERROR(A2+B2, 'Check input'). This shows a readable message instead of #VALUE! and lets the rest of your spreadsheet keep working.

IFERROR is a workaround, not a permanent fix. Use it to keep the spreadsheet usable while you track down the real problem.

When to Call a Professional

The #VALUE! error is almost always a data formatting issue, not a software problem. You should be able to fix it yourself by checking the cells referenced in the formula. If you are working with a complex imported dataset and the errors appear across thousands of cells, a data analyst or Excel consultant can help clean the data quickly.

Frequently Asked Questions

Why does #VALUE! appear only in some cells and not others in the same column?

The cells without errors probably contain real numbers, while the ones with errors contain text that looks like numbers. This commonly happens when data is imported from a CSV file, a database, or copy-pasted from a website. Some rows come in as real numbers, others as text — the formatting is inconsistent. The Text to Columns trick (Step 2 above) fixes the whole column at once.

My formula looks completely correct but still shows #VALUE!. What am I missing?

Check for invisible characters. Sometimes a cell that appears empty actually contains a space, a line break, or a special character that was pasted in. Click the suspect cell and press Delete to clear it completely, then retype the value. Also try the TRIM and CLEAN functions — CLEAN removes non-printable characters that TRIM cannot catch.

Can I use a formula that ignores #VALUE! errors automatically?

Yes. The IFERROR function wraps around any formula and catches errors. For math operations, you can also use SUMIF or AGGREGATE functions which have built-in options to ignore errors. For example, =AGGREGATE(9,6,A1:A10) sums a range while skipping any cells that contain errors.