How to set number format to show all decimal digits

Question

I have one excel sheet. In that one cell value $1,544.13 when i click that cell it is showing 1544.12857142857 in formula bar.

I tried to set as General, but it does not show all the digits. It displays something like 1544.128571.

If I set the custom format to 0.00, I get 1544.13, but the formula shows 1544.12857142857.

Find the Excel sheet - Amount column has digits problem

How can I change the number format to show all decimal digits?

Solution 1

Format it as number with 11 digits:

Alternatively, you can also provide a custom format, such as #,##0.00000000000

Solution 2

An Excel number value can have, at most, 15 digits.

Number formatting via the Customs formatting type let's you use # for a digit place that will display a digit if an appropriate one exists, but will not if one does not. Contrast with 0 which will display a digit regardless using a 0 if no digit exists.

So, a number format that has 15 # characters after the decimal point will not show decimal places that do not exist, but will if they do. It will not affect the number itself, so, for instance, "123456789012345" will not end up with all those "left of the decimal point" digits lost, or any unhappy thing like that.

So, the following would do the trick asked for:

#,###.###############

However, your question has a telling line in it: I tried to set as General, but it does not show all the digits. It displays something like 1544.128571.

This line makes it seem very likely you could actually just solve your problem by widening the column. It is displaying only that many characters due to the column width (and it rounds to get what it is displaying).

Not only would widening the column work, if you can do so without ruining your layout, but the solution I gave will not work any better if you cannot make the column wide enough to display up to 15 digits (left OR right of the decimal point) with the attendant commas and decimal point.

And alternative actually exists: formatting the text to "Shrink to fit" which would, as it says, shrink anything to fit the available width. But... that might be pretty disturbing. You can end up with something so small it looks like an underline (and that can be something you do on purpose too).

So consider whether you have the space to display all the digits available. If you do not, and cannot, have the room to display all the digits, you are out of luck, unless using "Shrink to fit". If you CAN display all the digits, width being no problem, or one you can live with, the just using General formatting will get you a basically formatted display of all the available digits, once you widen the column.

General will not have commas, if that matters. (Or other spacing character, an actual space for example, where one might usually see a comma.) The format I gave above would have those niceties. But would take up some more width of course.

So it really depends upon your true issue here, how readable you want to make the displayed value, comma-wise, and what font size you can live with.

The format I gave above is no harder to use than the General format. The decision needs to be done based upon the three things I just listed.