• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Why does Excel 2019 keep changing my cell formatting?

Trice

New Member
Longtime Excel user, and I upgraded to the 2019 version about three months ago. I've noticed recently that, at least for older spreadsheets, Excel automatically adds two decimal places to certain cells when I click into the formula bar. These cells are already formatted to show currency with no decimal places. But when I click in the formula bar then hit return, it adds two decimal places. The values in the cell (and from the cells the formula references) do include cents - they are not whole numbers - but I've specifically set the formatting to show whole dollar values only.

Even when I re-format the cell for currency with no decimal places, this auto-reformatting behavior continues. As far as I can tell from my testing, this behavior does not happen with new sheets I create in 2019.

(Under Options > Advanced > Editing Options > the "Automatically insert a decimal point" box is UNchecked. I even turned it on, set the "places" value to 0 then turned it back off again and the behavior persists.)

Edit: From my testing it appears this behavior only happens in cells with formulas, not cells that contain only data. I entered data with two decimals but still formatted not to show decimals, and the formatting held even when I clicked in and out of the formula numerous times.

Any ideas?
 
Last edited:

GraH - Guido

Well-Known Member
Hi, have you checked the Microsoft user forum if this is a known issue?
If I'm informed correctly the big difference in 2019 version is the upgrade of the calculation engine which is completely different from the previous one. That might be the cause it is only happening with formulae results and only in files from previous versions. Still I'd be guessing.

Suggestion as workaround/test. What if you first clear all formats, fully recalculate, save and close workbook (as binary, xslb). Open it again, save as .xlsx and re-apply formats. Does it change anything at all?
 

Trice

New Member
Hi, have you checked the Microsoft user forum if this is a known issue?
If I'm informed correctly the big difference in 2019 version is the upgrade of the calculation engine which is completely different from the previous one. That might be the cause it is only happening with formulae results and only in files from previous versions. Still I'd be guessing.

Suggestion as workaround/test. What if you first clear all formats, fully recalculate, save and close workbook (as binary, xslb). Open it again, save as .xlsx and re-apply formats. Does it change anything at all?
Thanks for the reply. Your suggestion/test does not work, unfortunately. And by "Microsoft user forum" do you mean the Microsoft Community at answers.microsoft.com? I have not looked there specifically but I have Googled this issue ad nauseum (to no avail) and some links have pointed to that site. A quick search just now doesn't produce any results.
 

GraH - Guido

Well-Known Member
There is a trick where you can access the xml code of your Excel file by changing the extension to .zip (do this on a copy). Then open via zip and look for xml parts. Open these via notepad (++). After having changed the xml, rename the zip to .xlsx. Open and repair file.

In the xml code of the sheets/table you might find a hint on the cause. By deleting some lines it might get solved.
I've only used this to delete all formats, all images of a file, by deleting even whole folders from the zip and it worked. Never changed stuff inside an xml chunk however.

I've read somewhere MS offers a file converter to newer Office versions. Don't know if it is useful in case some-one upgraded from rather recent versions like 2010, 2013, 2016.

@Peter Bartholomew , would this issue be something you can test as well? If you feel like it obviously.
 

Peter Bartholomew

Well-Known Member
If I format a currency item by selecting Currency or Accounting and reduce the decimal places (using Office 365 insider), I can confirm that the cents reappear when the formula is reentered. On the other hand, if I impose the format from the Styles menu using Currency [0], only round dollar amounts are shown after reentering.

I am not sure whether this is a 'feature' or an error :confused:
 
Top