Issue with automatic custom number formatting in Excel

Copper Contributor

 

Hello Excel Community,

I'm encountering an unusual behavior in Excel where it seems to automatically apply a custom number format under certain conditions, without my input. Specifically, when I enter numbers with a decimal point, the formatting is inconsistent. For example, entering '22.23' displays correctly, but when I input '95.53', Excel automatically changes it to '95.53.00'. This does not happen with all numbers, making it particularly perplexing.

 

I have not intentionally applied any custom formatting, and this issue seems to arise spontaneously. It's crucial for me to display the numbers exactly as entered, without additional zeros appended.

 

Could anyone guide me on how to reset Excel to its default number formatting or suggest a way to prevent this automatic custom formatting from occurring? Any insights into why this might be happening with specific numbers and not others would also be greatly appreciated.

 

Thank you in advance for your assistance!

8 Replies

@Casg 

If you stay on such cell and Ctrl+1 which format do you see? And is it the same in exactly new workbook?

Hi Sergei,

It has this format [t].mm.ss - https://app.screencast.com/7ETMpcbFbWCdZ
And yes every new workbook I open and I don't know why or how i ended up there.

@Casg 

Perhaps you have custom template Where are my custom templates? - Microsoft Support which applies elapsed time format to all cells. **bleep** immediate workaround you may select entire sheet->Ctrl+1->apply General format

@Sergei
Doesn't look like i have any custom templates.

I have tried to make a video where i post different numbers, see here: https://app.screencast.com/o88L2MUo034gM

I have also tried to do Ctrl+1 for the whole sheet, doesn't help.

@Casg 

Strange. In E14 in formula bar you have the same number as in the cell

image.png

in G14 you have elapsed time format

image.png

If that's new file it shall be the template which predefines formats.

Do you have something related to Excel in %appdata%\Microsoft\Templates\ ?

Agreed, it is very strange!
My templates looks like this: https://app.screencast.com/UTW9WNcY0SqCa
I finally found the issue and it wasn't even in Excel...

My windows settings was wrong as my region format was wrong. https://app.screencast.com/l8fmLUM60z9oH

Thanks anyway for trying to help me Sergei :)

@Casg , Great to know you sorted this out, thank you for the update