How can I identify zero values, but format non-zero ones?

Solution 1:

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.

Solution 2:

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.