5

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?

Ƭᴇcʜιᴇ007
  • 113,841
Stu
  • 51

2 Answers2

1

One method is to use another column and round:

=ROUND(A1, 2)

You can also use a custom conditional format. First, set the format to 0.## like you have, then select all of the applicable cells or the entire column and set a custom conditional format by selecting Conditional Formatting -> Manage Rules from the Home ribbon:

Conditional Formatting

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:

Conditional Formatting Continued

Now click the Format button and set the Number format to General or just #.

Jason Clement
  • 2,098
0

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.



Here's the link: http://www.mrexcel.com/forum/excel-questions/510333-custom-format-if-number-integer-dont-show-decimals-but.html

I tested this and it does work. Just change decimal places from one to two.
BillDOe
  • 1,781