Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am using the following formula in all my first hundred rows for column F:


This is used to calculate the time in minutes between the cells in column E and column D. I have this formula applied to every row. The formula works however I have rows where the E and D columns are empty. For these Excel displays a 0 in column f.

Is there a way I can make a number format show nothing if the value of the cell is zero? Note that I still want to keep this as a number formatted cell as later on I sum up all the values in column f and use that in another part of the spreadsheet.

share|improve this question

migrated from Jan 18 '13 at 13:17

This question came from our site for professional and enthusiast programmers.

Yes, with a custom format like this:


The pattern for a zero is the 3rd argument (which in your case is blank), to show for example an x in place of zero, use this:

share|improve this answer

If there are circumstances where zero is a legitimate result (for example when E5 and D5 are not blank but E5 = D5) then rather than format to show zero as blank you could alter the formula to return a blank, e.g.


That will return a blank unless both D5 and E5 are numeric (e.g. time values)

You can still use SUM function on the results, blanks will be ignored

share|improve this answer


share|improve this answer

Try =if(((E5-D5)*1440)=0," ",((E5-D5)*1440))

share|improve this answer

You must log in to answer this question.