# Show decimal places if not a complete whole number [duplicate]

I have numbers in cells in Excel. I want the numbers formatted so that if they have decimal places they show to a maximum of two, and if they have no decimal places it doesn't show any.

For example:

``````•   1 should be formatted as 1 NOT 1.00
•   0.75 should be formatted as .75
•   0.2 should be formatted as .2
•   0 should be formatted as 0
``````

The closest custom format code I've come up with is 0.##. Unfortunately this formats 1.00 as 1. (note the extra decimal point).

How can I create a custom format code to remove the decimal point on cell values the report a full whole number?

## marked as duplicate by phuclv, Kamil Maciorowski, Dave M, Scott, Twisty ImpersonatorDec 24 '17 at 21:46

I found this on MrExcel.com:

This is not beautiful but i think it works

Assuming formatting A1

1. First format A1 as Number with 1 decimal place

2. Go to Conditional Formatting New Rule Use a formula to.. (the last option)

and insert this formula =SEARCH(".0",TEXT(A1,"0.0"))

Click the Format button and pick Number with 0 decimal places

Ok, Ok

done

Now you can use the Format Painter to apply the format to other cells.

HTH

M.

``````=ROUND(A1, 2)
Then click the New Rule button, and select Use a formula to determine which cells to format, and set the formula to `=ROUND(A1, 0) = ROUND(A1, 2)` where A1 is the first cell in the selected cells. It will look like so:
Now click the Format button and set the Number format to General or just `#`.