How to Stop Excel from Rounding Pasted Values to Two Decimal Places
Question details
Users experience an issue where pasting numeric data between workbooks causes a permanent loss of decimal precision, reducing numbers from eight decimal places down to two.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Copying and pasting data with high decimal precision between different Excel workbooks or from CSV files.
- Observed behavior
- Excel permanently truncates the pasted values to match the destination cell's two-decimal display formatting, rather than preserving the original eight decimal places.
Verify whether your source data is in a CSV file or a standard Excel workbook, as plain CSV files do not natively preserve Excel precision settings or cell formatting.
Disable the 'Set Precision as Displayed' Option
Turning off this workbook setting prevents Excel from permanently changing the underlying stored data to match the visible cell format.
When the 'Set precision as displayed' option is enabled, Excel alters the actual stored value of a number to match whatever is visible on the screen. If the destination cells are formatted to show only two decimal places, pasting eight-decimal data into them will permanently truncate the extra digits.
Click on the 'File' tab in the top-left corner of the destination workbook and select 'Options' at the bottom of the menu.
In the Excel Options dialog box, click on 'Advanced' in the left-hand navigation pane.
Scroll down to the 'When calculating this workbook' section. Uncheck the box labeled 'Set precision as displayed'.
Click 'OK' to apply the changes. Re-copy and paste your original data to verify the decimal places are now retained.
Save CSV Files as Excel Workbooks Before Editing
Converting CSV source files to standard Excel formats (.xlsx) ensures that decimal precision and workbook settings are fully supported before transferring data.
Prevent Unwanted Data Rounding with WPS Spreadsheet
WPS Spreadsheet provides robust data handling capabilities, ensuring your high-precision decimals are preserved accurately when copying and pasting between files. It allows precise control over workbook calculation settings to prevent unwanted truncation.
- 1. Open your files in WPS Spreadsheet: Launch WPS Office and open both your source data file and destination workbook.
- 2. Verify precision settings: Click on 'Menu' > 'Options' > 'Calculation' and ensure your precision settings align with your data requirements before pasting.
- 3. Copy and paste safely: Select your source data, press Ctrl+C, switch to the destination sheet, and press Ctrl+V to paste without losing decimal precision.
Frequently Asked Questions
Why does Excel change my 8-decimal number to 2 decimals?
This happens if the 'Set precision as displayed' option is enabled in the destination workbook. Excel forces the underlying stored number to permanently match the cell's visible formatting (e.g., 2 decimal places).
Does formatting a cell to show more decimals restore the lost precision?
No. If 'Set precision as displayed' was turned on when you pasted the data, the extra decimal places are permanently deleted. You will need to turn off the setting and re-paste the original data from your source file.
Why do CSV files cause decimal rounding issues?
CSV (Comma Separated Values) is a plain text format that does not store Excel formatting or workbook-specific settings. Opening CSV files directly in Excel can sometimes trigger default text-parsing rules that truncate decimals. Always save them as .xlsx before copying data.
Can copying between two different Excel instances cause data formatting loss?
Yes, if the source and destination workbooks are opened in entirely separate instances of the Excel application, Excel must rely on the basic Windows clipboard for transfer, which can drop advanced workbook-specific precision settings. Opening both files in the same Excel instance is recommended.
