How to Fix the #VALUE! Error in Excel
The #VALUE! error in Excel usually means a formula received the wrong data type. Learn how to find the cause and repair the formula.
#VALUE! appears when a formula receives a value it cannot use. The most common cause is text where Excel expected a number, date, time, or clean range. It can also happen when a formula combines ranges with incompatible shapes.
The fix is not to wrap everything in IFERROR. Start by finding which input is the wrong type, then either clean the source data or make the formula handle the real input.
Check for text where numbers should be
If a formula adds two cells and one contains text, Excel may return #VALUE!.
=A2+B2This breaks when A2 is a number but B2 contains text like "N/A", "ten", or a number stored as text with hidden characters.
To convert a clean text number, use:
=VALUE(B2)If the text includes currency symbols or spaces, clean it first:
=VALUE(SUBSTITUTE(B2,"$",""))In an expense tracker or small business budget, this often happens after copying transaction data from a bank export where amounts look numeric but are stored as text.
Check dates and times
Dates and times are numbers in Excel. If a date was typed as text, formulas that calculate durations or deadlines can fail.
For example:
=B2-A2If A2 or B2 is not a real date, Excel may return #VALUE!.
Try selecting the date column and applying a date format. If the values do not change, they may be text. Use DATEVALUE for text dates:
=DATEVALUE(A2)Use TIMEVALUE for text times:
=TIMEVALUE(A2)Check formulas that combine text and numbers
Concatenation is usually safer with & or TEXT. If you need to display a number inside a sentence, format it explicitly:
="Invoice total: "&TEXT(B2,"$0.00")This avoids accidental type handling and makes the output readable in invoice templates and client reports.
Common causes and fixes
| Cause | Example | Fix |
|---|---|---|
| Number stored as text | "1250" instead of 1250 | Use VALUE or clean the imported data |
| Hidden spaces | "1250 " | Use TRIM or clean the column |
| Text date | "June 2, 2026" stored as text | Use DATEVALUE or re-enter as a date |
| Bad range shape | Adding one column to a differently sized range | Match the range dimensions |
| Text in arithmetic | =A2+B2 where B2 is "n/a" | Replace text placeholders with blanks or zeros |
TIP
The Griddy way
#VALUE! errors are tedious because the formula may look correct while one imported value is dirty. The repair usually lives in the data, not the formula.
"Fix the #VALUE! errors in this expense sheet and show me which cells had text numbers or invalid dates"
Griddy can find the bad inputs, clean the affected columns, and update the formulas so the report calculates again.
Skip the manual work
Describe it. Griddy does it.
Instead of writing this formula yourself, just tell Griddy what you need in plain English. Works in Excel and Google Sheets.
Use this on real templates
Clean imported values before they break formulas
VALUE errors usually come from dirty text numbers, invalid dates, or inconsistent exports inside finance sheets that need dependable calculations.
Expense Tracker
Log every expense, track receipts, and generate category summaries. Free template for personal or business use.
Open templateFinanceSmall Business Budget
Plan revenue, direct costs, overhead, and EBITDA in one compact operating budget. Keep H1 totals, margin, and owner notes visible without building a giant finance model.
Open templateFinanceInvoice Template
Professional invoice template with automatic subtotal, tax, and total calculations. Customise with your logo and send in minutes.
Open templateFinanceExpense Tracker for Consultants
Track consultant travel, software, subcontractors, client costs, receipts, and tax categories in one free expense spreadsheet.
Open template