# Why is Excel doing this (displays zero instead of blank)?
**URL:** https://boards.straightdope.com/t/why-is-excel-doing-this-displays-zero-instead-of-blank/768510
**Category:** Factual Questions
**Created:** [October 14, 2016, 2:46pm UTC](https://boards.straightdope.com/t/why-is-excel-doing-this-displays-zero-instead-of-blank/768510 "2016-10-14T14:46:43Z")
**Posts on this page:** 9
**Page:** 1
### Author:  [@Lumpy](https://boards.straightdope.com/u/Lumpy)
#### Post date: [October 14, 2016, 2:46pm UTC](https://boards.straightdope.com/t/why-is-excel-doing-this-displays-zero-instead-of-blank/768510/1 "2016-10-14T14:46:43Z")
I’ve searched extensively and haven’t found any online help about this particular problem, so I hope the Excel gurus here can help.
I have an IF function following the standard structure of IF(Argument, resulttrue,resultfalse) where the false result is “” i.e., display nothing. So far so good. However, I want to append the results of that function with other things using the ampersand (&). The problem is that using ampersand makes the function display 0 instead of a blank, and this appears to override _ **every** _ method of hiding zeros- I’ve found nothing that works.
Any way to fix this?
---
### Author:  [@tullsterx](https://boards.straightdope.com/u/tullsterx)
#### Post date: [October 14, 2016, 2:52pm UTC](https://boards.straightdope.com/t/why-is-excel-doing-this-displays-zero-instead-of-blank/768510/2 "2016-10-14T14:52:48Z")
> [@Lumpy](#):
>
> I’ve searched extensively and haven’t found any online help about this particular problem, so I hope the Excel gurus here can help.
>
> I have an IF function following the standard structure of IF(Argument, resulttrue,resultfalse) where the false result is “” i.e., display nothing. So far so good. However, I want to append the results of that function with other things using the ampersand (&). The problem is that using ampersand makes the function display 0 instead of a blank, and this appears to override _ **every** _ method of hiding zeros- I’ve found nothing that works.
>
> Any way to fix this?
So far, I’m not able to duplicate your problem. Are you sure your & data is not evaluating to 0? Can you post your data/formula?
---
### Author:  [@Lumpy](https://boards.straightdope.com/u/Lumpy)
#### Post date: [October 14, 2016, 3:20pm UTC](https://boards.straightdope.com/t/why-is-excel-doing-this-displays-zero-instead-of-blank/768510/3 "2016-10-14T15:20:56Z")
Here’s the IF function:
=IF(C5="",B5\*1,"")
i.e., if cell C5 is blank, go ahead and do the calculation on cell B5. If cell C5 is checked, display nothing. Cell C5 is used simply as a binary do/don’t do control.
That works fine. The problem is that adding anything using the ampersand, even something as simple as:
=IF(C5="",B5\*1,"")&“text”
will, if C5 is checked, display the result:
0text
---
### Author:  [@Dervorin](https://boards.straightdope.com/u/Dervorin)
#### Post date: [October 14, 2016, 3:35pm UTC](https://boards.straightdope.com/t/why-is-excel-doing-this-displays-zero-instead-of-blank/768510/4 "2016-10-14T15:35:31Z")
I can only replicate this behaviour if both C5 and B5 are blank. I think you’ll get the behaviour you want if you use a formula like
```auto
=IF(ISBLANK(C5),B5*1,"")&"text"
```
---
### Author:  [@Lumpy](https://boards.straightdope.com/u/Lumpy)
#### Post date: [October 14, 2016, 3:54pm UTC](https://boards.straightdope.com/t/why-is-excel-doing-this-displays-zero-instead-of-blank/768510/5 "2016-10-14T15:54:36Z")
That didn’t help but I think I know what’s going on now: I used the Advanced Options to make all zero results blank. However, that applies to the entire cell; if there’s anything else there, everything is displayed; in this case the result of B5\*1 when B5 is blank.
OK, back to the spreadsheet, I’ll see if there’s another way, but so far all formatting to hide or eliminate zeros seems to require that that be the only contents of the cell.
---
### Author:  [@Me\_Billy](https://boards.straightdope.com/u/Me_Billy)
#### Post date: [October 14, 2016, 3:57pm UTC](https://boards.straightdope.com/t/why-is-excel-doing-this-displays-zero-instead-of-blank/768510/6 "2016-10-14T15:57:20Z")
Note there is a numerical 0, a text 0, and a “null” which is nothing.
---
### Author:  [@Dervorin](https://boards.straightdope.com/u/Dervorin)
#### Post date: [October 14, 2016, 4:00pm UTC](https://boards.straightdope.com/t/why-is-excel-doing-this-displays-zero-instead-of-blank/768510/7 "2016-10-14T16:00:51Z")
> [@Dervorin](#):
>
> I can only replicate this behaviour if both C5 and B5 are blank. I think you’ll get the behaviour you want if you use a formula like
>
> ```auto
>
> =IF(ISBLANK(C5),B5*1,"")&"text"
>
> ```
Posted too soon, edited too late: I believe this might be closer.
```auto
=IF(ISBLANK(C5),TEXT(B5*1, "#"),"")&"text"
```
This will, however, show just “text” if B5 happens to be 0. If B5 is any other value, say 5, you’ll get “5text”. Is that correct behaviour?
C5 values down the first column, B5 values down the rows:
```auto
C5 B5:| Blank | 0 | Other number
Blank | text | text | text
0 | text | text | text
Other number | 2text | text | text
```
---
### Author:  [@Dervorin](https://boards.straightdope.com/u/Dervorin)
#### Post date: [October 14, 2016, 4:06pm UTC](https://boards.straightdope.com/t/why-is-excel-doing-this-displays-zero-instead-of-blank/768510/8 "2016-10-14T16:06:42Z")
Sorry, one last update… experimentation breeds multiple posts, it would seem! I also think I’ve made a mistake in the table in my last post, but it’s such a pain that I’m going to stop trying to make tables!
If you want to avoid the 0 behaviour for B5, you could use something like this:
```auto
=IF(ISBLANK(C5),IF(B5=0, "0", TEXT(B5*1, "#")),"")&"text"
```
---
### Author:  [@Lumpy](https://boards.straightdope.com/u/Lumpy)
#### Post date: [October 14, 2016, 4:21pm UTC](https://boards.straightdope.com/t/why-is-excel-doing-this-displays-zero-instead-of-blank/768510/9 "2016-10-14T16:21:15Z")
> [@Dervorin](#):
>
> ```auto
>
> =IF(ISBLANK(C5),TEXT(B5*1, "#"),"")&"text"
>
> ```
>
> This will, however, show just “text” if B5 happens to be 0. If B5 is any other value, say 5, you’ll get “5text”. Is that correct behaviour?
Yes, I want to not display zeros, so this rather than your following post would be it. I’ll confirm there isn’t anything I’m overlooking but By Jingo, I think that’s it!