11

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?

1

2 Answers 2

18

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...

£#0.00;-£#0.00;

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.

2
  • 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
15

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.

2
  • 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 .