/ WhatILearned

How to hide Zero Dollars (currency) values in Excel

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.

Before Screenshot

View post on imgur.com

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

View post on imgur.com

After screenshot (yay no $0.00)

View post on imgur.com