INDEX / MATCH and empty cells

[theta]

Hi,

I want to be able to format a cell so that if it is empty I get a blank cell, instead of a 0. Which is what I was getting

I have formatting all of my lookup cells as Custom : ""

This leaves them blank when the lookup return a null

But there must be an easier way to format all of my cells (not just my lookups) to appear blank if there is no value?

 

[Sektor]

Custom format: 0;-0;;

 

[theta]

Won't that ruin my date cells?

 

[theta]

General;;

That seems to work? But is it 'legal'

Basically treat all positive and negative as General, everything else is empty

Only problem is this won't let text 'spill' onto adjacent cells. And I do not want to merge them

 

[RoryA]

That will hide negatives.

 

[Sektor]

Replace zeroes with Date format. You asked for not showing zeroes in cell. The third section of this format does this thing - it doesn't display zeroes.

 

[Sektor]

That will hide negatives.

Sure?

 

[ayazgreat]

You can use condtional format for it

Select cells to be formated and go to condtional format (if you using excel 2007 or 2010) and select format cells that contain and select cells equal to zero and then go to format and in custom format Type "" and ok and apply.

 

[Nduduzo]

If you are using Excel 2007 do as follows: Use the excel options dialog box, go to the Advanced category and scroll down to the section "Display options for this worksheet" and remove the tick in the option that reads "show zero values in cells that have zero values"

 

[theta]

But some cells do contain zeros (sum cells) so need to be specific which cells I 'ignore zeros' for?