Samsung Galaxy S8 review
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.
I always thought zeros looked neater than blank cells.
For cells range C1 to C4 use
If other words
If the sum of C1 to C4 =0 then show "" (blank) otherwise show the total of C1 to C4.
Does this site keep crashing?
You can hide zero values. Go to Tools/Options and untick "Zero values" on the View tab.
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!
Even with the site crashing you still can't beat VoG to the answer on an Excel question.....
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!
TRY USIng IF(ISBLANK) use help for guidance
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.