How to remove decimal places permanently

%3CLINGO-SUB%20id%3D%22lingo-sub-1884245%22%20slang%3D%22en-US%22%3EHow%20to%20remove%20decimal%20places%20permanently%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1884245%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20new%20to%20the%20community.%20A%20Google%20search%20for%20a%20solution%20to%20my%20problem%20landed%20me%20here!%20Thanks.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20worksheet%20where%20various%20values%20are%20displayed%20as%20follows%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E14369.1666666667%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehow%20can%20I%20delete%20permanently%20%26nbsp%3Bthe%20decimal%20places%20after%20the%203rd%20decimal%20place%20to%20make%20it%2014369.167%3F%3C%2FP%3E%3CP%3EI%20am%20not%20looking%20for%20a%20formula%20like%20round%2C%20or%20trim%2C%20but%20a%20%26nbsp%3Bsolution%20that%20I%20can%20apply%20to%20an%20entire%20sheet.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%26nbsp%3B%20a%20ton%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1884245%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1884269%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20remove%20decimal%20places%20permanently%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1884269%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F867999%22%20target%3D%22_blank%22%3E%40Sudheer_Mambra%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWe%20can%20apply%20a%20format%20to%20the%20number%20so%20only%20the%203%20decimals%20display%2C%20but%20we%20cannot%20delete%20the%20numbers%20after%20the%203rd%20decimal%20without%20using%20ROUND().%26nbsp%3B%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Temp.png%22%20style%3D%22width%3A%20540px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F233394iD95967A5E86FC6E8%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Temp.png%22%20alt%3D%22Temp.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1884361%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20remove%20decimal%20places%20permanently%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1884361%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%26nbsp%3Ba%26nbsp%3Blot%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F773415%22%20target%3D%22_blank%22%3E%40Craig_Hatmaker%3C%2FA%3E%26nbsp%3B.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20actually%20looking%20for%20a%20solution%20for%20permanently%20deleting%20the%20decimal%20places.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1884757%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20remove%20decimal%20places%20permanently%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1884757%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F867999%22%20target%3D%22_blank%22%3E%40Sudheer_Mambra%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Foffice%2Fset-rounding-precision-e5d707e3-07a8-4df2-810c-218c531eb06a%23%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ESet%20rounding%20precision%3C%2FA%3E%26nbsp%3B%2C%20with%20that%20every%20number%20will%20be%20automatically%20rounded%20to%20number%20of%20digits%20as%20in%20applied%20format.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1888186%22%20slang%3D%22en-US%22%3ERe%3A%20How%20to%20remove%20decimal%20places%20permanently%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1888186%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20lot.%20As%20the%20system%20gives%20some%20warning%20that%20the%20accuracy%20of%20the%20data%20would%20be%20lost%2C%20I%20am%20now%20scared%20to%20use.%20How%20serious%20is%20that%20issue%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBR%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hi all,

 

I am new to the community. A Google search for a solution to my problem landed me here! Thanks.

 

I have a worksheet where various values are displayed as follows:

 

14369.1666666667

 

how can I delete permanently  the decimal places after the 3rd decimal place to make it 14369.167?

I am not looking for a formula like round, or trim, but a  solution that I can apply to an entire sheet.

 

Thanks  a ton in advance.

7 Replies
Highlighted

@Sudheer_Mambra 

We can apply a format to the entire worksheet so only the 3 decimals display in numbers, 
Temp.png

Highlighted

Thanks a lot@Craig_Hatmaker .

 

I am actually looking for a solution for permanently deleting the decimal places.

 

 

Highlighted

@Sudheer_Mambra 

You may Set rounding precision , with that every number will be automatically rounded to number of digits as in applied format. 

Highlighted

Hello @Sergei Baklan,

 

Thanks a lot. As the system gives some warning that the accuracy of the data would be lost, I am now scared to use. How serious is that issue?

 

BR

Highlighted

@Sudheer_Mambra 

How serious it depends on concrete data.

For example, for $100/3 result will be $33.33 (instead of $33.33333333333...) if you set precision on 2 digits. Multiplying back $33.33 x 3 = $99.99. Other words you lost one cent from $100 on such simple operations.

Highlighted

@Sudheer_Mambra  wrote:  ``the system gives some warning that the accuracy of the data would be lost [....] How serious is that issue?``

 

Very serious, IMHO.  But note that the warning of permanent loss of precisions applies only to constants, not formulas.

 

So if you have a formula like =100/3 that has unintended consequences after setting Precision As Displayed (PAD), you can reverse the misbehavior simply by deselecting PAD again.

 

However, suppose you have an interest rate like 2.345678%, which you display as 2.35% for aesthetic reasons.  When you set PAD, the interest rate actually becomes 2.35%.  If (and when) you discover that many dependent calculations are affected adversely, so you deselect PAD again, the interest rate remains 2.35%.

 

For that reason, it is imporant to heed the following warning:  Before setting PAD in an existing workbook, make a back-up copy of the Excel file.  If (and when) you discover untended consequences of setting PAD, you can revert to the back-up Excel file.

 

That said, setting PAD is the only method for achieving your requirement, to wit:  ``not looking for a formula like round, [...] but a solution that I can apply to an entire sheet [sic]``.  (Setting PAD applies to the entire workbook, not selected worksheets.)

 

IMHO, you should abandon that requirement.  The decision of what and when to round and to what precision should always be done on a case-by-case basis, IMHO.

 

 

Highlighted

@Joe User 

Everything depends on goals. Workbook to multiply Qty*Price and sum result will work fine with precision as displayed, decision to use it is made for this concrete workbook. Why not.