I was working through a spreadsheet in Excel 2013 and one of the tables is a basic calculation involving quantities, unit prices and subtotals. It's pretty straight forward math to calculate the subtotal (quantity x unit price = subtotal). However, when I entered the formula into Excel, I found there were a bunch of $0.00 displayed in the subtotal column.
I wanted to hide the zero ($0.00) dollar fields until it was calculated. Conditional formatting only seems to style the background/text colour but doesn't affect if the field is displayed or hidden.
After digging around, I found a way to hide zero values using the following condition
0;-0;;@ it's using some inline if/else statements to test if the value is 0, and then do something with it. I think the
@ in this case is display it as is, whereas the missing argument between the two
;; means to hide it (or not display anything). Someone can correct me if I'm wrong.
So the format string I used was
$#,##0.00;-0;;@ to make a zero dollar currency value in Excel disappear when it's $0.00. You enter this format in the
Format Cell... dialog (right-click on the cell in question).
After screenshot (yay no $0.00)
Subscribe to Daniel Tse, Engineer, Consultant
Get the latest posts delivered right to your inbox