How to Fix Excel Numbers Displaying as Decimals (1 Shows as 0.01)
Question details
When entering a whole number into an Excel cell, it is automatically converted into a decimal fraction, such as typing 1 and having it display as 0.01.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Entering standard numeric data or whole numbers into spreadsheet cells.
- Observed behavior
- Excel automatically divides the entered whole number by 100, shifting the decimal point two places to the left immediately after the user presses Enter.
Check if this behavior happens in a completely new, blank workbook. If it does, the issue is tied to a global Excel application setting rather than localized cell formatting.
Disable the Automatic Decimal Point Setting in Excel Options
This is the primary solution to stop Excel from globally applying decimal places to newly typed whole numbers.
Excel includes a feature specifically designed to speed up data entry for accounting tasks by automatically inserting decimal points. When this feature is accidentally enabled, it divides all entered numbers by a set factor (usually 100), making 1 appear as 0.01. Disabling this global setting will immediately restore normal typing behavior.
Launch Microsoft Excel, click on the 'File' tab in the top left corner, and select 'Options' at the bottom of the left-hand menu.
In the Excel Options dialog box, click on 'Advanced' in the left-hand navigation pane.
Look under the 'Editing options' section at the top. Find the checkbox labeled 'Automatically insert a decimal point' and uncheck it.
Click the 'OK' button at the bottom of the window to apply your changes. Try typing a number into a cell to verify the issue is resolved.
Reformat Specific Cells to General or Number Format
Use this solution if the decimal issue only occurs in specific cells, rows, or columns, which indicates a cell-level formatting problem.
Easily Control Number Formatting with WPS Spreadsheet
WPS Office provides a highly intuitive spreadsheet tool that gives you complete control over advanced editing options. If you frequently struggle with unexpected formatting changes, WPS Spreadsheet makes it simple to toggle automatic decimals and manage cell data precisely.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook in the Spreadsheet module.
- 2. Access the Options Menu: Click the 'Menu' button located in the top left corner, then select 'Options' from the drop-down list.
- 3. Navigate to the Edit Tab: In the Options dialog window, click on the 'Edit' tab located in the left sidebar.
- 4. Turn Off Auto Decimals: Uncheck the box next to 'Auto decimal point' under the editing preferences, then click 'OK' to save.
Frequently Asked Questions
Why does Excel automatically add decimal points to whole numbers?
Excel includes an 'Automatically insert a decimal point' feature designed for financial and data entry professionals. When enabled, it saves time by allowing users to quickly type long lists of currency values without repeatedly hitting the period key.
Will fixing the automatic decimal setting correct the numbers I already typed?
No. Turning off the 'Automatically insert a decimal point' setting only prevents the behavior for future data entry. For the numbers already entered as 0.01 instead of 1, you will need to manually re-enter them or multiply the affected cells by 100 using Paste Special.
How do I fix this issue if I am using Excel for Mac?
On a Mac, click on 'Excel' in the top menu bar, then select 'Preferences'. Under the Authoring section, click on 'Edit'. Look for the 'Display this number of decimal places' option and uncheck it.
