Why is my new workbook not recognizing empty cells as zeroes?

Copper Contributor

I started working on a new workbook recently.


When writing a formula, I noticed I was getting the "value" error message.  After some trial and error, I realized that the error message is occurring because my formula is referencing cells that are blank.  In the past this has never been an issue, blank cells have always been formulated as zeroes.  But not tonight for some reason...


Is there a setting somewhere where I can correct this?  I really don't want to manually fill in EVERY CELL with a zero, but that seems to be the only way I can get around this error message.



2 Replies
best response confirmed by DavidJonDavis (Copper Contributor)

@DavidJonDavis Then those cells aren't really empty. The may contain a single space, an empty sting ("") or another invisible character. Are these cells the result of another formula perhaps? Or were the copied and pasted from the web?


Best to fix this at the source and make sure the cells are really empty. Alternatively use the N function. For instance, if A1 could be empty or contain a number and you want to multiply it by 10, se this:

=10 * N(A1)


This will return 0 for anything in A1 that is not a number.