If you’re using Excel for Mac (Office 365) and notice that whole numbers (like 1, 2, 100) are displaying as decimals (e.g., 0.001, 0.002, 0.1), you’re not alone. This issue can be frustrating, but it’s usually caused by a simple formatting or scaling setting. Here’s how to fix it.
Why Does This Happen?
Excel may display whole numbers as decimals due to:
- Incorrect Number Formatting – The cell may be set to a custom format that scales numbers.
- Regional Settings – Some regions use a comma (
,) as a decimal separator, which can cause confusion. - Scaling in PivotTables or Charts – If data is linked to a PivotTable or chart, scaling may be applied.
- Imported Data Issues – Data imported from CSV or other sources may have hidden formatting.
How to Fix It
1. Check and Reset Number Formatting
- Select the affected cells.
- Go to the Home tab.
- In the Number group, click the dropdown and select General or Number.
- If the issue persists, right-click the cells → Format Cells → Choose Number or General → Click OK.
- If still no joy check try Excel> Preferences> Edit “Automatically insert a decimal point.” check its un checked (unticked)
2. Remove Custom Scaling (Thousands/Millions)
- Right-click the cells → Format Cells.
- Under Category, select Number or General.
- If a Custom format is applied (e.g.,
0.000,), delete it and reset to General.
3. Check Regional Settings
- Go to System Preferences → Language & Region.
- Ensure the decimal separator is a period (.) (not a comma).
- Restart Excel after making changes.
4. Fix PivotTable or Chart Scaling
- If the issue appears in a PivotTable:
- Right-click the PivotTable → PivotTable Options → Display tab.
- Uncheck “Show values as” if any scaling is applied.
- For charts:
- Right-click the axis → Format Axis → Check if Display units is set to None.
5. Re-import Data Correctly
- If the issue comes from imported data:
- Use Data → Get Data → From Text/CSV.
- Ensure the correct delimiter and decimal separator are selected.
Final Thoughts
This issue is usually a formatting glitch rather than a data corruption problem. By resetting the number format, checking regional settings, and ensuring no scaling is applied, you should be able to display whole numbers correctly in Excel for Mac.
If the problem persists, try copying the data into a new workbook or restarting Excel to clear any temporary formatting issues.
Related Microsoft links