Stop treating blanks as zeros

Question

Is there a setting that tells Excel to stop treating blanks as zeros?

I'm not looking for a workaround like =IsBlank() but a universal setting that shuts-down the blank->0 feature.

Nkrumah

Yea, force the blank to be an actual blank using a single quote ' which forces the field to be interpret as a string – Ascidium

I consider that to be a workaround (unless there's a way to tell excel to treat all blanks as strings?) So the solution would not involve editing cells. – Nkrumah

Answer 1

I think this question is misleading. Excel doesn't treat blanks as zeros, certain functions do that depending on the way you build your formulas.

It is not a workaround to test if a cell is blank, it is necessary depending on the functions you are employing. So I think the answer to your question is "no". You will always have to specifically decide how to treat empty cells based on the formulas you are constructing. It's not always necessary to do so.

What you're calling a problem is actually the diverse nature of the program at your disposal.

If you have a specific formula you find troublesome, post it as a new question to see if there is perhaps an alternate method to your goal. More diversity!

(added as an answer as requested).

Chader

The question isn't misleading at all. This as default behavior is insane. In SQL if you do a calculation that includes any NULL value... you typically get NULL as the output, because in 99% of cases, that's a much safer + more sensible thing to do than just assume NULL/empty == 0. For the rare cases where you do want empty/null to just be treated as zero, you could always write the conditions there. But having to manually write conditions for the 99% of cases where you don't want that is massively painful, and also dangerous. It's scary how much of the world is running on this garbage. – Adp

This is nothing but poor programming of Excel. A blank cell is not zero, in different languages it has different names but a blank cell is mostly considered an NA, so the minimum logic expected from the designers of the primitive coding system of Excel is do it so that you don not SUM several blank cell and end up in a 0. Now imagine trying to do a pairwise correlation with this behavior. – Ireful

Answer 2

The result of this "diverse nature" (colorful adjectives are not solutions and just wreaks of fanboy or paid representative), is that charting blank cells leads to ugly 0s. In that respect, MS Excel is indeed treating blank cells as zeros.

Undesigned

Answer 3

If your function/reference is expected to return a string, placing =""& in front of your function/reference will force excel to understand that the string is the result and will then display a null/empty cell.

Cymophane

This is the most beautifully elegant solution I've ever found to this pervasive annoyance. – Greenheart

Answer 4

I also have this annoying problem. My solution is to execute the calculation throughout the column; then delete the zeros. Excel is a bit quirky on this score. For example, even though the column is formatted at number with two decimal points, Xcel in Find and Replace mode won't recognise the 0.00 entries generated by the calculation. But it will recognise 0, even though the entries appear as 0.00. The strange and wonderful thing is that this approach gets rid of the 0.00s where I want a blank (because there is no entry in the adjacent cell used for the calculation) but retains 0.00 where there is an entry in the adjacent column but that value is so small that the calculated answer is less than 0.01.

Precipitation

I forgot to mention: I have to copy and past the column is questions using Paste Values as the Find and Replace script doesn't seem to like formulas. – Precipitation

It would be much better if Xcel provided a Treat 0s as blanks button! – Precipitation

Answer 5

So, if I think I understand the question right, here is your solution

Instead of having =A1-A2, for example, which if are both blank would return 0, have this-

=IF(A1-A2=0, " ", A1-A2)

If both cells are blank, the formula will return 'blank' (In reality a space, but visually a blank). If the cells have an actual value then it will return that value. However, if the value is actually 0 (e.g. if both cells have the value 2) then it will still return blank, which may be an issue for you, I don't know

If you like you can replace the A1-A2=0 part with something like A1=0 OR A2=0, or A1=0 AND A2=0

Just depends what you want out of your sheet

Geier

Answer 6

There is a under "Options", "Advanced", "Display options for this worksheet" a setting that says "Show a zero in cells that have zero value"

If you uncheck that, all those zeroes that have been derived from blanks will be shown as blanks.

HOWEVER, it will also show zeroes that are genuinely zeroes (and not blanks) as blank as well. So depending on what you're after, this isn't necessarily what you want.

The only other way around it I've found is an If statement using ISBLANK. But yeah, it's a bit of a pain.

Robbert

Answer 7

it's damn annoying you can't tell it not to replace null values with 0 on basic =cs1

here for people:

=IF(ISBLANK(CS1),"",CS1)

that works for what you want :)

Starch

Answer 8

Use an “OR” function within the “IF” logic test to isolate both blank and GREAT THAN (>) “0” entries. Return blank (“”) in the “if true” field, then show the desired true value in the “if false” field.

It’s a bit counterintuitive however, it yields no outcome for both blank and greater than zero entries. Zero becomes the only possible correct value.

For example:

=IF(OR(A1=“”,A1>0),””,”correct value if 0 is entered”)

Basic:

=IF logic test(OR logic test(condition1 and condition2),this if true, this if false)

Xiaoximena

Answer 9

For number fields:

=IFERROR(VALUE(CS1&""), "")

In my case =CS1&"" broke the conditional formating. It is a little bit longer, but no repeating as in =IF(LEN(CS1),"",CS1)

For text fields:

=CS1&""

Lemieux