I have a column in an Excel spreadsheet that contains monetary values, most of which are zero. I want to hide the zero ones, but keep the non-zero ones formatted as currency.

This Microsoft page shows how to hide zeroes, but it leaves the non-zero values as integers. It doesn't explain how the tip works, so I can' see how to modify it to format what is shown.

Anyone able to help?


2 Answers 2


Found the answer here. It seems that the semi-colons separate four sections of formatting, for positive numbers, negative numbers, zeroes and text.

In my case, I want currency shown for positive or negatives, and blank for zeroes, so the following custom format does the trick...


Because the third format is blank (ie there's nothing after the second semi-colon), it shows blank for zeroes, but uses the formatting in the first two otherwise.

Hope that helps someone.

  • 1
    The # here has no effect. It is interesting with a space like £# 0 which tells Excel to separate thousands. (The proposed format are typically like # ##0, but just # 0 does separate thousand too. Jan 11, 2022 at 9:11
  • @FrédéricLoyer Thanks for that. I was wondering what that was there for, but couldn't find a clear explanation Jan 12, 2022 at 18:11

A custom format like « 0;-0; » tells Excel to print a normal number if positive (0 format), normal number with a minus sign if negative (-0 format), and nothing if null (the third part of the custom format).

You could have also fancy format (0;-0;"NULL")… the order of format (positive;negative;null) is hardcoded in Excel.

  • I can't see how this part "keep the non-zero ones formatted as currency" is addressed in your answer Jan 11, 2022 at 8:11
  • 1
    I was focusing on « how the tip works ». But, as you found, adding a £ does the job. Jan 11, 2022 at 9:08

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .