127

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.

  • 15 should be formatted as 15, NOT 15.00
  • 14.3453453 should be formatted as 14.35
  • 12.1 should be formatted as 12.1
  • 0 should be formatted as 0

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

To further complicate the issue, the spreadsheet is a result of an export from SQL Server Reporting Services. So no macros are possible. Oh well, it looks like 0.## is my best bet, and they can just live with the extra period.

Improve this question

14 Answers 14

Reset to default
48

Alternatively, you can solve the "optional" decimal point problem using the following solution:

Number Format: General;[Red](General)

This will add the decimal place and fractional value accordingly, while formatting negative numbers in a more legible way.

As for the poster's original question, you still need to round/truncate the "extra" decimal points using a formula. However, this is a simple formula of =ROUND(<value>,<desired decimal places>) that is not extremely computationally expensive.

Examples:

2500 -> 2500
0.25 -> 0.25
-2500 -> (2500)
-0.25 -> (0.25)
Improve this answer
8
  • 4
    This answer deserves more upvotes. It handled the decimal point correctly without using conditional formatting. In my specific case, I want to show all decimal places, so I don't even need to modify the formula.
    – wilson
    Commented Mar 14, 2016 at 7:18
  • 2
    General has the problem of going into scientific notation if the number has too many decimals, e.g. 2.6532E-06 instead of 0.0000026532.
    – Dan Dascalescu
    Commented Jul 2, 2017 at 5:35
  • +1 This was helpful for me in solving a specific problem of needing to display values of thousands of dollars (e.g. show $40,000 as $40k, and $10,500 as $10.5k), in the labels of a Pivot Table (where conditional formatting is not an option). Format code $General\k did it perfectly!
    – Dan Henderson
    Commented Jul 27, 2017 at 16:49
  • Also applies to Gnumeric: just use Format: General instead of Format: Number
    – markling
    Commented Jul 2, 2020 at 15:09
  • 1
    Any idea how to add thousand separators to General;[Red](General)?
    – MsGISRocker
    Commented Feb 28, 2022 at 11:08
25

Apply Conditional Formatting for non-decimal numbers.

For example, A1 is the target cell.

  1. Format A1 as "###,###.00". This will be used for decimal number.

  2. Define Conditional Formatting for non-decimal numbers.

    • Condition: Cell Value equal to =TRUNC(A1).
    • Format: ###,###

Below is the result:

12       =>  12
14.231   => 14.23
15.00000 => 15
17.3     => 17.30
Improve this answer
2
  • 3
    Just had an issue with number 4.001, which rounded to 4.00 on display, yet would not satisfy the conditional formatting formula (4.001 <> 4) and would still show as "4.". I used this conditional formula to solve it: =ROUND($A1;2)=TRUNC($A1)
    – leokhorn
    Commented Nov 19, 2018 at 10:37
  • @leokhorn alternatively =ABS(A1-TRUNC(A1))<0.00499999999999989 I would have used <0.005 but my Excel was having internal rounding issues such that abs(4.005-trunc(4.005))<>0.005
    – Gregor y
    Commented Aug 25, 2020 at 19:31
25

Excel custom formats can provide a partial answer

Custom formats for numbers in Excel are entered in this format:

positive number format;negative number format;zero format;text format

One format that comes close to your requirement, but leaves in the decimal place for numbers with no decimals is:

#,###.??;(#,###.??);0

Example:

  • 15 is displayed as 15.
  • 14.3453453 is displayed as 14.35
  • 12.1 is displayed as 12.1
  • 0.5 is displayed as .5
  • 0 is displayed as 0
Improve this answer
4
  • This is by far the best answer.
    – KyloRen
    Commented Dec 7, 2016 at 2:08
  • 9
    Yet doesn't actually provide a valid solution, or even a possible work around. It does have useful information regarding custom number formats.
    – Lopsided
    Commented Apr 11, 2017 at 16:28
  • 15
    15 is displayed as ,15.
    – Roger Far
    Commented Dec 13, 2017 at 18:50
  • 7
    who needs to display 15.? that is for sure an Excel bug!
    – serge
    Commented May 1, 2021 at 14:20
17

My Answer removes the decimal point AND Removes 0 value

in conditional formatting write:

General;#;;@

This should do the trick.

Improve this answer
5
  • 6
    This displays zeroes as empty cells.
    – lolmaus - Andrey Mikhaylov
    Commented Aug 21, 2018 at 7:53
  • 1
    This is exactly what I was looking for. Thank you.
    – Owen Blacker
    Commented Jul 21, 2020 at 14:23
  • 1
    I used General°;#°;;@° to show degrees, like 5°, 3.5°, 2.25°, etc... without all the annoying trailing 0's which imply a level of precision that is not present in the original measurement. Thanks for the tip!
    – user207863
    Commented Dec 30, 2022 at 15:43
  • 1
    This is a very bad solution. The syntax, without condition blocks, is POSITIVE;NEGATIVE;ZERO;TEXT. @Mr.J your code is saying “Use the ‘General’ format for positive numbers; Round negative numbers to the nearest integer, then remove the negative symbol; Display nothing if zero; Display text, if text.” The General format automatically removes decimal points and zeros, so your code does nothing useful at all, other than breaking expectations. Not even the 2 decimal limit, either. @user207863 the code you're looking for is General°, which will add the degree symbol to any number.
    – JKVeganAbroad
    Commented Jun 12, 2023 at 18:15
  • Simple, concise, and understandable. This should be the accepted answer, thank you sir!
    – G.N.
    Commented Jun 1 at 0:46
9

I ran into this recently in Excel 2007 and just ended up using the 'TRUNC' function in the value cells:

  value  =TRUNC(B5,1)
      0      0
      5      5
    5.4    5.4
  65.43   65.4
765.432  765.4

Worked exactly the way I wanted it to...

Improve this answer
2
  • 4
    Yes, both TRUNC(x,2) and ROUND(x,2) will do the job (depending on what kind of rounding method you're after). You then just need to set the cell formatting to "General" and the decimal places will only be displayed if needed. (I vote to mark Droj's post as the answer.)
    – Simon E.
    Commented Jun 13, 2011 at 9:46
  • 1
    This solution drops precision in case the number is used in further calculations.
    – ymihere
    Commented Sep 8, 2023 at 9:07
9

Here's one method using conditional formatting.

  1. Right click your cell, choose "Format Cell"
  2. Select "Custom"
  3. Enter "0.####" (add more or fewer #s to control the maximum number of decimal places)
  4. Click OK
  5. With the cell still selected, use "Conditional Formatting" (may be a menu item in Format or button in Home depending on your Excel version)

  6. Add a new rule, based on the formula "=MOD(H32,1)=0"

    edit - better formula is "=MOD(ROUND(H32,2),1)=0" with the '2' being the desired number of decimal places. previous formula leave trailing decimal point if the number rounds to an integer.

    Replace H32 in the formula with the ID of your cell, but MAKE SURE THERE ARE NO $ SIGNS! (No $ signs ensures that you to copy the format to other cells)

  7. For the format of this rule, select the number tab, choose "Custom"

  8. This time, enter the formula "0"
  9. Click OK enough times to return to the sheet (varies among excel versions)

There you go, enjoy responsibly! If you want to copy the format to other cells, remember that you can copy the cell and use "Paste Special" with the "Format" option.

Improve this answer
3
  • Could you clarify if this only applies to certain (or more recent versions) of Excel? I am still using 2003 (and I suspect an embarrassing number of others are as well) and when I go to Conditional formatting, I have no option to modify the number formatting, only Font, Border, and Patterns. Thanks
    – SSilk
    Commented Jan 21, 2013 at 20:19
  • I just tried this approach on Excel 2010 and it worked fine. It will not work with earlier versions of Excel (at least that's what my "compatibility mode" checker in Excel 2010 tells me).
    – barfuin
    Commented Feb 12, 2013 at 19:11
  • BEST ANSWER! THANK YOU!
    – fduayer
    Commented Jul 11 at 14:04
2

Remember that in Reporting Services, you can write an expression for the number formatting, too. In my situation, I used

iif(floor(Fields!numericFieldName.Value)=Fields!numericFieldName.Value,"0","0.##")

as the number format expression. This produced an Excel file with two cell formats, selected by whether or not the value was an integer.

Improve this answer
1

Are you / your users inputting values directly in the cells, or are they being populated by a formula or a macro?

If the cells are not being populated directly by a human, you could store the calculated values in a hidden range and then display formatted text to the user with a formula like this:

=IF(ROUND(A1,2)=INT(A1),TEXT(A1,"0"),TEXT(A1,"0.0#"))

(where 'A1' is the cell being referenced)

The formula will display values like this:

 -------------------------
 |Original     |Formatted|
 |-------------+---------|
 |         15  |       15|
 | 14.3453453  |    14.35|
 |       12.1  |     12.1|
 |          0  |        0|
 |    -15.123  |   -15.12|
 |      1.004  |        1|
 -------------------------

NB: The formula output is a text string, not a numeric, so:

  • The output defaults to being left-aligned.
  • You cannot use the output in any further calculations (instead, you should use the original cell being referenced)
Improve this answer
1

Format cell as 'General' then under data validation restrict values to decimals

Improve this answer
2
  • If you need to avoid adding formulas (and probably a new column) to your spreadsheet then Fred's suggestion may be the best.
    – Simon E.
    Commented Jun 13, 2011 at 9:55
  • 1
    This will not allow people to enter numbers with more than the visual limit of decimal digits.
    – barfuin
    Commented Feb 12, 2013 at 19:14
1

This doesn't have to be quite so complicated - it can be done exclusively with Conditional Formatting. I have Excel 2010, so this may not work for earlier versions.

  1. Format the cells to be General. The number of decimal places for some numbers may be very high.
  2. Highlight a cell and click Conditional Formatting. Click New Rule, then "Use a Formula".
  3. Use the formula =(B1-INT(B1))>0 (where B1 is the cell you're formatting)
  4. Click the Format button and choose Custom. Enter [=0]0;.## into the Type field.
  5. Hit Enter several times.
  6. Click on Conditional Formatting again, click Manage Rules, and Edit the rule you just created. Change the range in the "Applies to" field to cover the range you want covered.

All done.

Improve this answer
0

Further to Luke's great answer above here is a variation based on his work. I didn't want to have a zero in the cell if there was no value. Leave your cell format as general and used this formula:

=IF((A1=""), "",IF(ROUND(A1,2)=INT(A1),TEXT(A1,"0"),TEXT(A1,"0.0#")))

Again where A1 is the cell being referenced.

Improve this answer
0

Using the Round function to provide significant figures. This does not change the number of decimal places displayed however. Just format the cell with general number format though.

ROUND(MyValue,Sig.figs - 1 - INT(LOG10(ABS(MyValue)))
Improve this answer
2
  • Why all the complicated INT(LOG... formula? Why not just ROUND(value,2) and format as General?
    – Simon E.
    Commented Jun 13, 2011 at 9:54
  • @SimonEast Round(value, 2) fixes at 0.00, where as 2 sig figs of 0.0125 would be 0.013 and not 0.01
    – Gregor y
    Commented Aug 25, 2020 at 19:20
0 
B1=IF(ROUND(A1,2)=INT(A1),TEXT(A1,"0"),TEXT(A1,"0.##"))
C1=IF(ROUND(B1,2)=INT(B1),TEXT(B1,"0"),TEXT(B1,"0.##"))

No more orphan decimal points.

Improve this answer
0

This seems to be the simplest way to have a Decimal shown when there are fractional numbers - and No Decimal shown for whole numbers:

=TEXT(ROUND(Base_Number,Max_Decimal_Places),"general")

or with cell references:

=TEXT(ROUND(A1,2),"general")

Gives

15 = 15
9.23432234 = 9.23

Improve this answer
1
  • Welcome to SuperUser @lookingforsame. Just a note to watch out for the hidden details of questions though. In this case, the op is saying the output is from SQL, if macro's are not possible, formulae may not be either. It's sometimes worth putting in a question as a comment before answering. Good to see a new person though, happy answering!
    – Julian Knight
    Commented Jan 17, 2014 at 17:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .