Microsoft Excel: How to switch off automatic formatting

Excel responds to certain letter and number entries with automatic formatting. This makes sense in most cases, but sometimes it can be very annoying, as the user then has to switch back to the desired formatting via Start > Format > Format cells. Thankfully, Excel now includes a number of options that you can use to deactivate the unwanted automatic functions.

Further reading: How to master Excel: From beginner to pro

How to deactivate unwanted automatic functions

You can find these settings via File > Options > Data in the Automatic data conversion section. This involves the following functions:

Remove leading zeros and convert to number: For example, if you write the value 0009 in a cell, Excel automatically abbreviates it to 9.

Preserve the first 15 digits of long numbers and convert it to scientific notation: If you enter the number 3520345723544235874452337844560238967 in a cell, for example, Excel truncates it to 15 digits and displays it in scientific notation in the form 3.52034572354423E+36.




In many cases, Excel automatically performs a conversion during input. You can prevent this using a series of fairly new options.




In many cases, Excel automatically performs a conversion during input. You can prevent this using a series of fairly new options.
IDG

In many cases, Excel automatically performs a conversion during input. You can prevent this using a series of fairly new options.
IDG


IDG

Convert digits around the letter E to a number in scientific notation: Excel converts all character strings containing the letter E to a number in scientific notation. For example, if you type 44E88, it converts the cell content into 4.4E+89.

Convert consecutive letters and numbers to a date: The input 01-03- 24 thus becomes 01.03.2024 on the spreadsheet.

To prevent these automatic conversions when entering and inserting text or numbers, delete the tick in front of the corresponding options.

You can also use the option When loading a.csv file or similar file, notify me of automatic data conversions to specify that you are informed of any conversions on the worksheets when opening a CSV file.