CODEWITHSUNDEEP
excelformatting
james
james

Reputation: 1

Conditional Decimal Format

I want to format a number with two decimals unless it's 0. Can I do this?

123.93
 23.00
144.34
     0
  2.38

Upvotes: 0

Views: 744

Answers (4)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

From Excel Help:

A number format can have up to four sections of code, separated by semicolons. These code sections define the format for positive numbers, negative numbers, zero values, and text, in that order.

;;;

So All you really need to do is something like 0.00,-0.00,0

Upvotes: 0

pnuts
pnuts

Reputation: 59450

An alternative that includes a separator for thousands, shows negative values in red font and aligns zeros with whole numbers: 

#,##0.00;[Red]-#,##0.00;0

(for the alignment, there are three blanks at the end).

Upvotes: 0

Leon
Leon

Reputation: 1141

This works great:

Go to format cells and select custom. Then paste this 0.00;[=0]0 into the Type field

from http://www.ozgrid.com/Excel/CustomFormats.htm

Upvotes: 5

anon
anon

Reputation: 4608

Try this:

=IF(A1=0,"0",TEXT(A1,"0.00"))

Upvotes: 0

Related Questions