Excel Ate My Numbers
What danger lurks in the evil spreadsheet? Drama and tension in outer space!
No, Excel shouldn’t change your numbers, but it can happen occasionally, as this poor guy discovered.
Numbers in General Format
As the robot in the video mentioned, you might have problems if you try to enter a large number in Excel. For example, I’ve entered a 16-digit credit card number — 1234567890123456 — in cell A2.
Everything looks fine, until I press the Enter key.
The cell is formatted as General, which will only display 11 numeric characters. Since the credit card number is larger than that, it appears in exponential notation.
Significant Digits in Number Format
To make the credit card number display correctly, I could try Number format, with zero decimals.
That looks better, except that the last digit has changed from a 6 to a 0. Excel only retains 15 significant digits, so it changes our 16th digit to zero.
Large Numbers in Text Format
Since we need to see all 16 digits in the credit card number, we can format the credit card column as Text, and enter the numbers. All 16 digits will be stored, and will display correctly. Or, type an apostrophe before the credit card number, and it will be treated as text.
- On the Microsoft site, a brief article on the Number of significant digits MS Excel retains
- Chip Pearson’s article on Rounding Errors In Microsoft Excel97
- For smaller numbers that have been formatted as text, you can Convert Text to Numbers