Prevent Excel from Automatically Changing Number of Decimals Displayed in Currency

Question

I have researched this thoroughly, and while there are a lot of similar answers on this forum, they do not address my specific problem.

I need to know how to prevent Excel from automatically changing my display format to display more decimal places. BEFORE YOU ANSWER, please read my entire description, since there are many SIMILAR issues, but not the exact one I am experiencing.

Here is how I reproduce the problem.

1. Format a blank cell (A1 for this example) with currency, zero decimal places, use "$" as currency symbol
2. Enter a number in an adjacent cell (A2 for this example), for example, 133
3. In the original cell that you formatted as currency (A1), enter the formula =A2*0.333
4. Excel automatically changes the decimals displayed back to 2 (it displays $44.29 instead of just $44)

Note, this doesn't happen all the time and I haven't been able to isolate what triggers it. It seems to happen only with currency. If you know how to get Excel to stop automatically changing the number of decimals displayed on currency values, please let me know.

I have already tried the following:

1. Options -> Advanced -> UN-CHECK "Automatically insert a decimal point"
2. Options -> Advanced -> UN-CHECK "Extend data range formats and formulas"

IMPORTANT: The following posts do not address my problem:

https://superuser.com/questions/517919/why-does-excel-2010-automatically-reformat-numbers

https://support.office.com/en-ie/article/Set-rounding-precision-e5d707e3-07a8-4df2-810c-218c531eb06a

https://support.office.com/en-us/article/Stop-showing-rounded-numbers-cc86bae9-b48e-48da-906d-e334a440d3ea

https://in.answers.yahoo.com/question/index?qid=20101204001252AAgPQAr

This has been bothering me for two years. I would really appreciate it if someone could help.

Answer 1

I formatted A1as you state- see screenshot

Enters 133 in A2

Entered =A2*0.333 in A1; result was $44 with no decimals

Dragged A1 to K1 and entered values in B2:K2

Still no decimals

So I cannot reproduce your problem.

You will need to make a file with the problem and put it on OneDrive (or similar file share site)

best wishes

Answer 2

I have had the same issues as the original author in Excel 2013.  My column is set to Accounting, 0 decimal places.  I put a formula in to calculate and it now give me 2 decimal places automatically and I have to go back and change it again.

Answer 3

I have the a similar problem, except in the example given it only happens if I multiply by 33%, rather than 0.333.  

I can add that if I change the currency symbol from (my usual) British pound to US dollar, the problem goes away.

If I change the formula to subtract the fractional part (set it to zero) then it also doesn't change the formatting. (In other words if the formula is "=A2*33%-0.33" then the format doesn't change. Remove the the "-0.33" and it does.)

Edited to add: I tested OPs example in an unused part of an open sheet, and was able to reproduce once I substituted 33% for 0.333. I have now tested in a blank sheet and can't reproduce.

Edit 2: I found this which appears to be the same problem:-

http://answers.microsoft.com/en-us/office/forum/office\_2010-excel/excel-2010-ignores-decimal-places-setting-when/ff1e0bc0-2a55-4f4e-9e1f-712cad2eefd4

Also, I have now managed to reproduce in a blank sheet, using Excel 2016. 

1. Type number "133" in A1 and formula "=A1*33%" in B1
2. Format both as currency with 0 places
3. Press F2 in B1 and enter to save, it converts to two places.

The problem only happens if both numbers are the same currency, and it appears to happen for any currency. If either number is changed to a different currency the problem doesn't happen. If no currency symbol is used, it doesn't happen. (The format changes to not be currency in that case.)