Excel practice › Excel errors › #VALUE! error

#VALUE! error in Excel: what it means and how to fix it

#VALUE! means a formula was given the wrong kind of value, and the usual case is text where it needs a number. One cell in the calculation holds a word, a note like n/a, or a date that Excel didn't recognize, and +, -, * and / can't work with it.

Find the cell that isn't a number and correct it. If the text belongs there, add with a function and not with plus signs: =SUM(B3:D3) skips text, where =B3+C3+D3 stops on it.

Skip to the 3 exercises ↓

The causes, in the order worth checking

  1. A cell in the calculation holds text: a word, a note, a number typed with its unit.
  2. A cell that looks empty holds a space.
  3. A date that is really text.
  4. A plus sign used to join text.
  5. Ranges of different sizes in a function that needs them equal, like SUMIFS.

1. A cell in the calculation holds text

Marketing's February figure wasn't ready, and someone typed n/a in its place. Every formula that does arithmetic on that cell now shows #VALUE!:

Live example#VALUE!: n/a in the middle of an addition
E3fx
ABCDE
1DeptJanFebMarQ1 total
2Sales42003900465012750
3Marketing2800n/a3100#VALUE!
4IT1500150017504750
5Facilities90095011002950
6HR6507007202070

SUM gets you a number, but decide whether it's the number you want: 5900 is two months, not a quarter. The same goes for a figure typed with its unit, like 12 boxes: to Excel that's text, and the fix is to keep the number in one cell and the unit in the header.

To find the cell in a long row in Excel, test the suspects with =ISTEXT(C3): TRUE means that cell is text, whatever it looks like. (ISTEXT isn't among the functions the sheets on this page know.)

2. A cell that looks empty holds a space

A cell someone "cleared" by pressing the space bar isn't empty. It holds a space, which is text, and arithmetic on it gives #VALUE!. A really empty cell counts as 0 and causes no error.

Select the cell and press Delete. To clean a whole column, Microsoft's method is Find & Replace: put a single space in "Find what", leave "Replace with" empty, and choose Replace All. Do that on the selected column only, or it removes the spaces inside your text too.

3. A date that is really text

Excel stores a date as a number, which is why subtracting two dates gives days. A date it didn't recognize when it was typed or imported stays text, and the subtraction fails. Here the shipping date of order 5003 was typed day first:

Live example#VALUE!: subtracting a date that is text
D4fx
ABCD
1OrderOrderedShippedDays
250013/2/20263/5/20263
350023/3/20263/9/20266
450033/20/202625/3/2026#VALUE!
550043/6/20263/10/20264

4. A plus sign used to join text

+ adds numbers and nothing else. To put a label in front of a number, join them with &:

Live example#VALUE!: text + number
G2fx
ABCDEFG
1DeptJanFebMarQ1 totalLabel
2Sales42003900465012750#VALUE!
3Marketing2800n/a31005900
4IT1500150017504750
5Facilities90095011002950
6HR6507007202070

5. Ranges of different sizes

SUMIFS goes down its ranges side by side, row by row, so they must have the same number of rows. Here the amounts run to row 8 and the departments stop at row 7:

Live example#VALUE!: C2:C8 against A2:A7
F2fx
ABCDEF
1DeptCategoryAmountDeptTotal
2SalesTravel1240IT#VALUE!
3ITSoftware890
4SalesMeals310
5MarketingAds2200
6ITHardware1650
7SalesTravel780
8ITSoftware420

Practice: 3 formulas to fix

Type the formula in the box and press Enter. Clicking a cell inserts its address. Each answer is checked on the data you see and again on changed data.

Exercise 1A total that survives text

=B3+C3+D3 shows #VALUE! because C3 holds the text n/a. In E3, write a formula for Marketing's total that works whether C3 holds text or a number.

E3fx
ABCDE
1DeptJanFebMarQ1 total
2Sales42003900465012750
3Marketing2800n/a3100
4IT1500150017504750
5Facilities90095011002950
6HR6507007202070
Show the answer

=SUM(B3:D3) gives 5900.

SUM adds the numbers in B3:D3 and ignores cells that hold text. When C3 gets its figure, the same formula includes it.

Exercise 2Ranges that don't line up

=SUMIFS(C2:C8,A2:A7,E2) should total the amounts for the department in E2. It shows #VALUE!. In F2, write the formula that works.

F2fx
ABCDEF
1DeptCategoryAmountDeptTotal
2SalesTravel1240IT
3ITSoftware890
4SalesMeals310
5MarketingAds2200
6ITHardware1650
7SalesTravel780
8ITSoftware420
Show the answer

=SUMIFS(C2:C8,A2:A8,E2) gives 2960.

Both ranges now cover rows 2 to 8, so SUMIFS can pair each department with its amount.

Exercise 3A label and a number in one cell

="Q1 total: "+E2 shows #VALUE!. In G2, write the formula that returns the label followed by the number in E2, like Q1 total: 12750.

G2fx
ABCDEFG
1DeptJanFebMarQ1 totalLabel
2Sales42003900465012750
3Marketing2800n/a31005900
4IT1500150017504750
5Facilities90095011002950
6HR6507007202070
Show the answer

="Q1 total: "&E2 gives Q1 total: 12750.

& joins values as text, so a label and a number can sit in one cell. CONCAT does the same: =CONCAT("Q1 total: ",E2).

Questions people ask

What does #VALUE! mean in Excel?

The formula got a value of the wrong type. In practice: text in a place where a number is needed. Excel shows it when you add, subtract, multiply or divide a cell that holds a word, a space, or a date it stored as text.

Why do I get #VALUE! when the cell looks empty?

The cell holds a space, or another character you can't see. Select it and press Delete, then check the formula again. A really empty cell counts as zero and doesn't cause the error.

Why does SUM work when adding with + gives #VALUE!?

SUM ignores text cells in the range it's given. The + operator doesn't ignore anything: one text cell and the whole formula is #VALUE!. =SUM(B3:D3) works where =B3+C3+D3 fails.

How do I find the cell that causes #VALUE!?

Look at each cell the formula uses. Text sits on the left of its cell and numbers on the right, unless the alignment was changed. For a cell you can't judge by eye, =ISTEXT(C3) returns TRUE when it holds text.

Can I hide #VALUE! with IFERROR?

You can: =IFERROR(B3+C3+D3,0) shows 0 where the error was. But 0 is now a wrong total that looks right. Fix the cell or the formula first, and keep IFERROR for cases where you know why the error happens and what should show.

Keep going

Ten questions in a real spreadsheet, from SUM to INDEX/MATCH, with a level at the end and the lesson to start from. Free, no sign-up.

Related

IFERROR and IFNA, with live examplesSUMIFS function, with live examplesCONCAT function and the & operator, with live examples#NAME? error in Excel: meaning and fixes#DIV/0! error in Excel: meaning and fixesSUMIFS and COUNTIFS practice (Pro)Excel formula errors, fixed liveExcel practice online

Written and tested by Nico, who builds SheetDrill (about). Every formula on this page ran through the engine that checks the exercises before the page was published. Checked against: Microsoft Support: How to correct a #VALUE! error, Microsoft Support: How to correct a #VALUE! error in the SUMIF/SUMIFS function. Last checked November 2026.