Excel - How to leave cell blank instead of 0?

excel cell zero emptydatatext
windbrand222 picture windbrand222 · Apr 12, 2016 · Viewed 12.2k times · Source

I'm not using a numerical formula. All I have is a simple reference to another cell.

=(Sheet1!D8)

If that cell is blank, I want to have the cell on this page blank too, not a 0. How do I do that? I want it to appear empty to anyone, not just me, so I don't think simply editing excel settings would work? None of my google search results, including MS office site, have been helpful.

Answer

Stephen Lloyd picture Stephen Lloyd · Apr 12, 2016

My suggestion:

=IF(ISBLANK(Sheet1!D8),"",Sheet1!D8)

You can force a formula to evaluate as text instead of as a number by adding '& ""' to the end like below, but if you are dealing with numbers this isn't great.

=(Sheet1!D8)&""

See this answer for other ideas.

Related questions

How to delete Certain Characters in a excel 2010 cell

In column A I have a load of name that look like this [John Smith] I still want them in A but the [] removed...

Read More
Convert cells(1,1) into "A1" and vice versa

I am working on an worksheet generator in Excel 2007. I have a certain layout I have to follow and I often have to format cells based on input. Since the generator is dynamic I have to calculate all kinds of …

Read More
How to disable Excel's automatic cell reference change after copy/paste?

I've got a massive Excel 2003 spreadsheet I'm working on. There are a lot of very large formulas with a lot of cell references. Here's a simple example. ='Sheet'!AC69+'Sheet'!AC52+'Sheet'!AC53)*$D$3+'Sheet'!AC49 Most of them are …

Read More