I have a number which is the output of a calculation in one of the cells in Excel Spreadsheet A. When I copy this cell and paste as value in Spreadsheet B, which is open in a different instance of Excel, some of the decimal places mysteriously disappear. I was surprised by this, so I copied and pasted the value in Spreadsheet A as a value, then copied and pasted this value from A to B, and it still changed.
in Spreadsheet A, the value is:
2258.07102745522
When copied and pasted to Spreadsheet B (in another instance), the value is:
2258.071027
Never encountered this before, and I work extensively with Excel. What is causing this?
Best Answer
If you copy between different instances of Excel (and not just different worksheets or files), they don’t consider each other being the same program, and interact simplified through Windows’ clipboard.
For such transfers, the complete Excel cell information (format, number, formula, color, etc.) gets replaced by a dumb text, and then pasted as such. This text reflects what you see in a cell, not what the underlying Excel knows what is in the cell; therefore number of digits shown is relevant, same as potential formats with text pieces (for example, a number 24 that is formatted to show “24 h” becomes the text “24 h” when pasted).
You can avoid this by working in the same instance (which has some other consequences, like Excel-wide settings getting overwritten for all files by the last file opened...)