I have a spreadsheet set up with simple sum formulas but would like to know if there is any way that the cells containing the formula could display nothing, i.e. remain totally blank when there is no data to be calculated. Displaying zeros when there is nothing to calculate makes the printed version look very untidy and it would be great if this could be done. Any help would be much appreciated.
Thanks for all the replies and PaulB2005, yes the site is crashing which is why I didn't respond sooner. I actually thought of another way as well just after posting, by conditional formatting the cell to white if value equals 0. Thanks again to you all and I'll take the opportunity to wish everyone a very happy Christmas. Keep up the good work!
will work ok. The only thing to consider about the conditional formatting method is that it might go awry if the file is opened in another application...
If you are only using it yourself, then no problem, but if you need to share it with someone who has, for example, Works, or Ability, or 602, it might be that the conditional formatting doesn't work, whereas the formula method would. (I do know that the conditional formatting works ok when opened in OpenOffice.)
An advantage of using the conditional formatting method is that, in absolute terms, it's probably quicker to calculate. Of course, in the real world, unless your spreadsheet is MASSIVE, this isn't going to be noticeable, (and even if it is MASSIVE it will probably be barely noticable!) But, depending on what the actual formulae are, it is conceivable that some cells might return different results depending on whether the value of other cells are 0, (zero), or "", (blank). I should add that this is also unlikely... but might merit consideration if you get unexpected results!
One of the fascinations of Excel, for me, is that for most issues there are more methods available than ways to skin a cat. This may be one of the reasons why some people find it a bit tricky - there are just so many options.
My preference would always be to use a formula-based approach for this type of problem. Conditional Formatting and hiding zero values have their place but - say in a year's time - would you remember what you had done and be able to figure out why a cell sometimes displayed a number and was sometimes blank? At least with a formula, in this case you could work out what was going on.
This thread is now locked and can not be replied to.