How to Stop Excel from Automatically Inserting a Decimal Point
Question details
The user types whole numbers into Excel, but the software automatically converts them into decimals by inserting a decimal point before the last two digits.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Entering numerical data into spreadsheet cells that are already formatted as General or Number.
- Observed behavior
- Excel automatically shifts the decimal point, causing values like 487 to display as 48.7, and 4875 to display as 48.75.
Ensure that your cells do not have a custom number format applied that forces a decimal point, and verify that no active macros are altering your input.
Disable the Automatic Decimal Setting in Excel Options
Turn off the advanced editing feature in Excel that is designed to automatically format typed numbers with fixed decimal places.
Excel includes a feature to automatically add decimal points, which is highly useful for accountants doing rapid currency entry. However, for general use, it can cause unexpected formatting where whole numbers are divided by 10 or 100.
Click the 'File' tab located in the top-left corner of the Excel ribbon to open the backstage view.
Select 'Options' at the very bottom of the left-hand menu. If you don't see it, click 'More...' first.
In the Excel Options dialog box, click on 'Advanced' in the left pane.
Look under the 'Editing options' section at the top. Uncheck the box labeled 'Automatically insert a decimal point' (or 'Insert decimal places' depending on your version).
Click 'OK' at the bottom of the window to save your changes and return to your spreadsheet.
Use WPS Spreadsheet to Handle Data Without Unwanted Formatting
WPS Spreadsheet offers an intuitive interface where you have full control over advanced editing settings like automatic decimals, ensuring your data always appears exactly as you type it.
- 1. Open WPS Spreadsheet: Launch WPS Spreadsheet and click the 'Menu' button in the top-left corner.
- 2. Access Options: Select 'Options' from the drop-down list to open the settings dialog.
- 3. Go to Edit Settings: Click on the 'Edit' tab located in the left-hand navigation pane of the Options window.
- 4. Disable automatic decimal point: Uncheck the box that says 'Provide automatic decimal point', then click 'OK' to save.
Frequently Asked Questions
Why does Excel change my numbers to decimals automatically?
This happens when the 'Automatically insert a decimal point' feature is enabled in Excel's Advanced Options. It is typically used by accountants to speed up data entry for financial data without needing to type the decimal point manually.
Does changing this setting affect existing numbers in my spreadsheet?
No, disabling the automatic decimal setting will only affect new numbers you type. Any existing data previously entered in your spreadsheet will remain completely unchanged.
What if only specific cells are showing unwanted decimals?
If the automatic decimal setting is turned off globally but decimals still appear, check the individual cell formatting. Select the affected cells, right-click and choose 'Format Cells', then ensure the 'Decimal places' under the Number or Currency category is set to 0.
