Automatically insert a decimal point

[TheMissingPoint]

Hi,
New member. Office 2016.
I have a problem with existing and new spreadsheets (95% usage is for currency). I have the "Automatically insert decimal point" turned on. My custom settings are in place - [Red]#,##0.00;[Blue]"-("#,##0.00). I have checked all my Regional settings to make sure nothing has changed. Since about the 13th of September, every time I enter figures, the entries ignore the settings - what should be 16.00 comes up as 0.16 or -(16.00) as -(0.16).

I suspect this has something to with Windows 10 itself. But I am getting desperate, as my searches on the internet have not found any reports of this error.
Any suggestions greatly appreciated.

 

[Joe4]

Welcome to the Board!

what should be 16.00 comes up as 0.16 or -(16.00) as -(0.16).

What do you mean by this?
Do you mean when you type in 16, it gets automatically converted to 0.16?
Or are you talking about existing numbers in existing cells?

If you are talking about existing numbers in existing cells, if you take a look at the Custom Formatting applied to that cell, does it match what you show in your original post?
If you change the format from Custom to Number, does it then change to 16?

If this only happens as you enter new values in to your cells, do you have any VBA on the workbook that might be changing the values?

Also, does this only happen in this one workbook?
Does it also happen if you set this up in a brand new workbook?

Note. One option that you may want to try to get the same desired effect is to choose the existing Number or Currency format, setting it to show two decimal places.
Then just use Conditional Formatting to make all positive values red, and all negative values blue.

 

[TheMissingPoint]

Welcome to the Board!


What do you mean by this?
Do you mean when you type in 16, it gets automatically converted to 0.16?
Or are you talking about existing numbers in existing cells?

If you are talking about existing numbers in existing cells, if you take a look at the Custom Formatting applied to that cell, does it match what you show in your original post?
If you change the format from Custom to Number, does it then change to 16?

If this only happens as you enter new values in to your cells, do you have any VBA on the workbook that might be changing the values?

Also, does this only happen in this one workbook?
Does it also happen if you set this up in a brand new workbook?

Note. One option that you may want to try to get the same desired effect is to choose the existing Number or Currency format, setting it to show two decimal places.
Then just use Conditional Formatting to make all positive values red, and all negative values blue.

Hi John,
Yes, any new entry of 16 comes up as 0.6. No, existing entries are unchanged. It happens with a brand new workbook with no VBA or formatting. In the new workbook setting up the custom format only changes the color not the position of the decimal. All parts of the conditional formatting are working. I have Excel set to automatically insert the decimal point, but in all cases it is being over-ridden. I have even checked my country settings to make sure they are correct. The only VBA I have is to cover the size and positioning of the Comments box.
I will keep experimenting as I need to find the answer. I am much better beginning to think it may be Excel itself that may be corrupted. Thanks for trying.

 

[RoryA]

I have the "Automatically insert decimal point" turned on

That's exactly what that setting is for.

 

[Joe4]

That's exactly what that setting is for.

Its just like Rory says, the setting your chose is doing it by design.
I think it is a setting to make data entry easier, that people can just enter the numbers and not to have to enter the decimals.
So if they wanted to enter the value "16.97", all they would have to type is "1697".

It sounds like you don't want that setting and should shut it off.
If you want to display two decimal places for all numbers, just control that through formatting.

 

[TheMissingPoint]

Thanks for the advice - I don't know why, (mental block), but I must have changed the automatic decimal point settting to "2", whereas it should have been on "0", or as you suggest turned off completely. I usually use the right-click, format cells, when I want to increase or decrease the number of decimal places returned by a formula. It is much quicker than showing the Home Tab and changing it there.
This is one of the best forums I have found for getting not only a response, but a quick one. Thanks again.

 

[Joe4]

You are welcome.
Glad we were able to help!