If Excel shows #VALUE! when you add cells that look blank, at least one cell usually contains text—often a formula returning an empty string, a space, or imported text—not a truly empty cell. A genuinely blank cell is treated as zero in ordinary addition, so try =SUM(A1:B1) for a total, then check the cells if the error persists.
What the error means when you add cells
The + operator performs arithmetic. Excel needs each value it adds to be a number, or something it can interpret as a number in that calculation. A physically empty cell works as zero in a direct arithmetic expression. But a cell that looks empty on the worksheet may hold a text value, a formula, or an error. If Excel cannot use that content as a number, the expression can return #VALUE!.
For example, with a number in A1 and a genuinely empty B1, the formula =A1+B1 normally returns the number in A1. If B1 instead contains a formula that displays nothing, or a space typed by someone, the same expression can return an error. The display alone does not reveal whether the cell is empty; a blank-looking cell is not necessarily a blank cell.
This distinction matters in budgets, inventory sheets, gradebooks, and other workbooks where users leave fields unfinished. The error is Excel’s way of saying that the values supplied to an arithmetic operation are not suitable numbers. It does not necessarily mean that the cells are formatted incorrectly or that Excel has lost the values.
True blank cells, empty strings, and spaces
A true blank contains no value and no formula. An empty string is text with zero characters, commonly produced by a formula such as =IF(A1=0,"",A1). That formula can make a cell appear empty while the cell still contains a formula. A space is also text, even if it is difficult to see. These cases behave differently in arithmetic:
- Truly empty cell: Direct addition treats it as zero.
- Formula returning
"": The cell displays nothing, but direct addition can return#VALUE!. - One or more spaces: The cell contains text, so adding it as a number can fail.
- Text that looks numeric: A value such as
12stored as text may be ignored bySUMor cause a problem with direct arithmetic, depending on how it is used. - An existing error: A referenced
#N/A,#VALUE!, or other error can flow into a calculation rather than behaving like a blank.
Formatting does not usually change a cell’s underlying value. Applying a number format to text does not automatically convert that text into a number, and using a white font or a custom format to hide a value does not make the cell empty.
Quick fixes, depending on what you need
If the goal is simply to total values in a range, use SUM. With the numbers or blank-looking cells in A1:B1, =SUM(A1:B1) adds numeric values and ignores text and empty cells in the referenced range. For example, if A1 contains 8 and B1 contains a formula returning "", this formula returns 8. This is often the cleanest fix for a total, but it can also skip numeric-looking text that you may have intended to include.
If you need to preserve the logic of adding two individual cells and treat both true blanks and empty strings as zero, use an explicit blank check. With the inputs in A1 and B1, =IF(A1="",0,A1)+IF(B1="",0,B1) returns 8 when A1 is 8 and B1 is truly blank or contains a formula returning an empty string. It does not fix arbitrary text such as a space or the word “pending”; those values still need to be cleaned or handled deliberately.
When the cells are supposed to contain numbers stored as text, convert the source data rather than silently treating every text value as zero. In a temporary helper column, if A1 contains a numeric string such as 12, =VALUE(A1) converts it to the number 12. Copy the converted results and use Paste Special > Values if you want to replace the original data. Check a sample before replacing a whole column, because nonnumeric text cannot be converted this way.
You can also use SUM with separate cell references. With values in A1 and B1, =SUM(A1,B1) totals the numeric contents of those cells while ignoring text values supplied as references. That is useful for a short list of cells that are not adjacent. As with a range, it may conceal unwanted text by omitting it, so confirm that ignored entries are genuinely meant to contribute nothing.
How to find the cell causing the error
Start by selecting each referenced cell and looking at the formula bar. If it shows a formula, inspect the formula’s result and determine whether it returns a number, an empty string, or another error. If it shows a value that looks blank, use a diagnostic formula or check the underlying imported data. The following checks are useful when troubleshooting:
- With the cell being checked in A1,
=ISBLANK(A1)returns TRUE only when A1 is genuinely empty. It returns FALSE for a cell containing a formula, including a formula that displays nothing. - With the suspected content in A1,
=LEN(A1)counts characters. A result greater than zero can reveal spaces or other text that is not visible at normal zoom. An empty-string result has a length of zero, so combine this check withISBLANKwhen you need to distinguish an empty cell from a formula returning"". - Look for a leading apostrophe or imported markers, and check whether values are aligned or flagged as numbers stored as text. Alignment is only a clue, not proof, because formatting can change alignment.
- If the formula refers to a larger calculation, inspect each precedent cell rather than assuming the visibly blank cell is the source. A different cell in the expression may already contain an error.
For example, suppose A1 contains 8 and B1 appears empty but is produced by another formula. If =ISBLANK(B1) returns FALSE, B1 is not truly blank. Inspect its formula and decide whether its empty-string output is intentional. If the source formula should return a numeric zero instead, changing that logic may be clearer than adding workarounds in every total.
Choosing between plus and SUM
+ and SUM are not interchangeable in every situation. Use + when you are performing a specific calculation and want Excel to flag unexpected text rather than quietly omit it. For example, a calculation involving a quantity and a rate may need to fail visibly if either input contains a word instead of a number. Fixing the source data is safer than masking that problem.
Use SUM when the task is to total a range of values and blanks or labels are expected. It ignores text in referenced cells, which makes it convenient for ordinary lists. However, this behavior can produce a plausible but incomplete answer if a number was accidentally imported as text. If the total seems too low, inspect the entries rather than assuming the formula is correct just because it no longer shows an error.
There is also a practical difference between an empty cell and zero. A zero is a numeric value and may affect averages, charts, and later calculations; a blank may be omitted by some calculations or displayed differently. Choose whether missing data should mean “no value yet” or “known to be zero.” Do not replace blanks with zeros automatically when the distinction matters to the workbook’s meaning.
Common complications and misleading fixes
Wrapping everything in IFERROR can hide the cause. With the inputs in A1 and B1, =IFERROR(A1+B1,0) returns zero whenever the addition produces an error. That may be acceptable when an error genuinely means “no contribution,” but it can also turn a misspelled status, bad import, or broken upstream formula into a believable zero. Use it only when that fallback matches the meaning of the data, and investigate errors during setup.
TRIM does not solve every invisible character. If A1 contains ordinary leading or trailing spaces, =TRIM(A1) can remove those extra spaces from the result. But copied web data may contain nonbreaking spaces or other characters that ordinary trimming does not remove. If cleanup is necessary, inspect a few examples and confirm the cleaned result before using it in calculations. Avoid replacing source data blindly, particularly when the original text has record-keeping value.
A formula that displays an empty string is still a formula. This is a common reason that ISBLANK returns FALSE for a cell that looks empty. It is not a contradiction: the formula is present, but its displayed result has no characters. Decide whether the source formula should return zero, an empty string, or a numeric value based on how downstream calculations should work.
Changing cell format is not the same as converting data. If an imported value is text, changing the format to Number may leave it as text. Use an appropriate conversion method, such as VALUE for clean numeric text, or correct the import process. If a column mixes numbers, labels, and blanks, identify which entries should be numbers before converting; forcing every entry into a number is not a safe general repair.
Preventing the error in shared workbooks
Prevention starts with deciding what each input column is allowed to contain. If users should enter numbers only, label the column clearly and consider Excel’s Data Validation controls to restrict entries. For Microsoft 365 on Windows, find these on the Data tab under Data Validation. Validation can reduce accidental text entry, though it does not guarantee clean data when users paste values from other sources.
For formulas that feed totals, choose a consistent treatment for missing information. A source formula can return a numeric zero when zero is the intended meaning, or return an empty string when the result should appear unfilled. Then make downstream formulas consistent with that choice. Add a note to the sheet explaining whether a blank means “not entered,” “not applicable,” or “zero”; those meanings should not be treated as interchangeable.
When importing data, check a small sample for numbers stored as text, extra spaces, and formula-generated blanks before building dependent calculations. Use clear input columns and separate them from calculated columns where practical. These habits make it easier to locate an error and reduce the temptation to hide it with a broad error-handling formula.
Frequently Asked Questions
Do blank cells count as zero in Excel addition?
A genuinely empty cell is treated as zero in a direct arithmetic expression such as =A1+B1. A formula returning an empty string or a cell containing a space is not a truly empty cell, which is why it can behave differently.
Why does Excel show #VALUE! when a cell looks empty?
The cell may contain a formula returning "", a space, imported text, or an existing error. Check the formula bar and use ISBLANK to distinguish a true blank from a cell containing a formula.
Will SUM ignore blank cells and text?SUM ignores empty cells and text in referenced cells, so it is useful for totaling a range with labels or blank-looking entries. It can also omit a number stored as text, so verify that your total includes every intended amount.
How do I make a formula treat an empty-string cell as zero?
For two inputs in A1 and B1, use =IF(A1="",0,A1)+IF(B1="",0,B1). This handles true blanks and empty strings, but not arbitrary text; clean or convert other contents separately.
Does formatting a cell as Number fix #VALUE!?
Not usually. Formatting changes how Excel displays a value; it does not reliably convert text into a number. Convert numeric text deliberately and check the result before relying on it in calculations.