'%3e%3cpath%20d='M8%200C12.4183%200%2016%203.58172%2016%208C16%2012.4183%2012.4183%2016%208%2016C3.58172%2016%200%2012.4183%200%208C0%203.58172%203.58172%200%208%200ZM11.6162%204.38379C11.2257%203.99337%2010.5927%203.99338%2010.2021%204.38379L8%206.58594L5.79785%204.38379C5.40732%203.99334%204.77429%203.99329%204.38379%204.38379C3.99331%204.77429%203.99335%205.40733%204.38379%205.79785L6.58594%208L4.38379%2010.2021C3.99348%2010.5927%203.99341%2011.2257%204.38379%2011.6162C4.77426%2012.0066%205.40734%2012.0065%205.79785%2011.6162L8%209.41406L10.2021%2011.6162C10.5927%2012.0066%2011.2257%2012.0067%2011.6162%2011.6162C12.0067%2011.2257%2012.0066%2010.5927%2011.6162%2010.2021L9.41406%208L11.6162%205.79785C12.0066%205.40735%2012.0066%204.77429%2011.6162%204.38379Z'%20fill='%23080E17'%20fill-opacity='0.46'/%3e%3c/g%3e%3cdefs%3e%3cclipPath%20id='clip0_3761_713'%3e%3crect%20width='16'%20height='16'%20fill='white'/%3e%3c/clipPath%3e%3c/defs%3e%3c/svg%3e)
How to Fix Excel Displaying 1 When You Type 100 on Mac
Question details
Users need to stop Excel from converting whole numbers into smaller decimal values during data entry.
- Product
- Microsoft Excel
- Device & OS
- Mac
- Scenario
- Typing standard whole numbers, such as 100, into a spreadsheet cell.
- Observed behavior
- Excel automatically inserts decimal places, changing the inputted number 100 to display and register as 1.
Ensure you haven't applied custom cell formatting or division formulas to the affected cells, as these can also alter how numbers are displayed.
Disable the Automatic Decimal Point Setting
Turn off the feature that automatically inserts decimal places for typed numbers, which is the most common cause of this issue.
Excel includes an automated decimal feature designed for fast currency entry (e.g., typing 100 to get $1.00). If this was accidentally turned on, all whole numbers you type will be divided by 10 or 100 automatically.
Click on 'Excel' in the top Mac menu bar and select 'Preferences' from the dropdown menu.
In the Excel Preferences dialog box, locate and click on the 'Edit' icon.
Find the checkbox labeled 'Automatically insert a decimal point' and uncheck it. Close the preferences window and re-enter your numbers.
Verify and Adjust Cell Number Format
Check if a specific custom cell format is forcing the number to display incorrectly.
Experience Seamless Data Entry with WPS Spreadsheet
WPS Office Spreadsheet offers intuitive default settings, ensuring your data appears exactly as typed. It provides an excellent alternative for Mac users seeking a streamlined, hassle-free spreadsheet experience without unexpected automated formatting.
- 1. Download and Install: Download WPS Office for Mac from the official website and install it on your device.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing Excel (.xlsx) file.
- 3. Enter Data Naturally: Type 100 into any cell; WPS Spreadsheet's default settings ensure it displays exactly as entered.
- 4. Format Cells Easily: If needed, select your cells and press Command+1 to explicitly control decimal places and number styles.
Frequently Asked Questions
Why does Excel have an automatic decimal point setting?
This feature is primarily designed for financial data entry or accountants who frequently type currency values. By enabling it, users can type '100' and have it automatically register as '1.00', saving keystrokes when entering long lists of prices.
Will turning off the automatic decimal setting fix previously entered numbers?
No, disabling this setting only applies to numbers you type afterward. Any numbers that were previously converted to 1 instead of 100 will need to be manually corrected, or you can type 100 in an empty cell, copy it, and use 'Paste Special > Multiply' on the affected cells.
Is the Command+1 shortcut the same for formatting cells in WPS Office on Mac?
Yes, both Microsoft Excel and WPS Spreadsheet support the Command+1 keyboard shortcut on Mac devices to quickly open the Format Cells dialog box.
Can this automatic decimal issue occur on the Windows version of Excel?
Yes, it happens on Windows as well. On a Windows PC, this setting is located by navigating to File > Options > Advanced, where the 'Automatically insert a decimal point' checkbox can be found and unchecked.
