Home

Excel reformating automatically

%3CLINGO-SUB%20id%3D%22lingo-sub-696375%22%20slang%3D%22en-US%22%3EExcel%20reformating%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-696375%22%20slang%3D%22en-US%22%3E%3CP%3EUsing%20Excel%20build%201905%20(office%20professional%20plus%202016).%3C%2FP%3E%3CP%3EFor%20some%20reason%20as%20of%20latest%20update%2C%20any%20value%20with%20more%20than%202%20decimal%20places%20changes%20format%20(from%20General)%20and%20has%20incorrect%20value%20in%20cell.%20i.e.%201.111%20changes%20to%201111.%20This%20is%20in%20a%20new%20blank%20spreadsheet.%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-696375%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-696579%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20reformating%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-696579%22%20slang%3D%22en-US%22%3EHas%20your%20decimal%20separator%20changed%20perhaps%3F%3CBR%20%2F%3ETwo%20settings%20control%20this%3A%3CBR%20%2F%3EWindows%20regional%20settings%3CBR%20%2F%3EExcel%20settings%3A%20File%2C%20Options%2C%20Advanced%20tab%2C%20Editing%20options%2C%20%22Use%20system%20separators%22%20checkbox%20(should%20be%20checked)%20and%20the%20two%20subsequent%20textboxes%20for%20decimal%20and%20thousands%20separator.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-698442%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20reformating%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-698442%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F361565%22%20target%3D%22_blank%22%3E%40DaveT175%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20value%20is%20changing%20that%20could%20be%20occasionally%20checked%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20463px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F119193iD433E89675F42D10%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-699116%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20reformating%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699116%22%20slang%3D%22en-US%22%3EI%20tried%20to%20reproduce%20the%20issue%20with%20that%20setting%20but%20was%20unable%20to%20%3A-)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-699140%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20reformating%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699140%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYes%2C%20it%20returns%2011.11%20when%20typing%201111%2C%20but%20I'm%20not%20sure%20there%20is%20no%20misprint%20in%20the%20question.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-699496%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20reformating%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699496%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20Jan%2C%20This%20was%20the%20problem.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20noticed%20the%20changes%20directly%20after%20doing%20the%20routine%20office%20upgrade.%20I%20use%20excel%20every%20day%20so%20seemed%20to%20definitely%20relate%20to%20this.%20Other%20observation%20was%20that%20the%20currency%20formatting%20had%20changed%20but%20probably%20the%20same%20issue.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20again%20for%20your%20help%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-699513%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20reformating%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699513%22%20slang%3D%22en-US%22%3E%3CP%3EBasically%20on%20third%20digit%20after%20a%20decimal%2C%20the%20format%20always%20changed%20so%201.111%20went%20to%201111%20and%2011111%20went%20to%2011.111%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F361565%22%20target%3D%22_blank%22%3E%40DaveT175%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-699596%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20reformating%20automatically%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-699596%22%20slang%3D%22en-US%22%3EYou're%20welcome!%3C%2FLINGO-BODY%3E
DaveT175
New Contributor

Using Excel build 1905 (office professional plus 2016).

For some reason as of latest update, any value with more than 2 decimal places changes format (from General) and has incorrect value in cell. i.e. 1.111 changes to 1111. This is in a new blank spreadsheet.

Any ideas?

Thanks

7 Replies
Has your decimal separator changed perhaps?
Two settings control this:
Windows regional settings
Excel settings: File, Options, Advanced tab, Editing options, "Use system separators" checkbox (should be checked) and the two subsequent textboxes for decimal and thousands separator.

@DaveT175 

If value is changing that could be occasionally checked

image.png

 

I tried to reproduce the issue with that setting but was unable to :-)

@Jan Karel Pieterse 

 

Yes, it returns 11.11 when typing 1111, but I'm not sure there is no misprint in the question.

Thanks Jan, This was the problem.

 

I noticed the changes directly after doing the routine office upgrade. I use excel every day so seemed to definitely relate to this. Other observation was that the currency formatting had changed but probably the same issue.

 

Thanks again for your help

 

 

@Jan Karel Pieterse 

Basically on third digit after a decimal, the format always changed so 1.111 went to 1111 and 11111 went to 11.111

 

Thanks

 

@DaveT175 

You're welcome!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies