Super User is a question and answer site for computer enthusiasts and power users. It only takes a minute to sign up.
Sign up to join this community
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?
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.
£# 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
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.
