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.
Yes, this is silly, but it was a fun project, and the serious instructions are below the video.
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.
More Information
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
How’d you get my credit card number? And why are you posting it here? At least you did didn’t post the 09-11 expiration date.
Hi Debrah,
just a comment on the new banner of your blog. It looks like a lot of mis-aligned tables that I see in my work. Columns of right-aligned numbers are topped with headers of left-aligned text, so that some of the column headers seem to float between the numbers and it’s not immediately apparent what column header belongs to what column of numbers. A much better layout would be to right-align the headers to sit on top of the respective number column.
I’m a bit disappointed to see such (bad) table layout from an Excel guru like you.
What gives? (no offense meant)
teylyn
@ Teylyn
I’m guessing it’s deliberate…
What exactly is that hand gesture at 1:16?
@John, sorry about that! At least the full number only shows up in one of the screen shots!
@teylyn, you’re right, and I’ve fixed the picture.
@Lincoln, thanks for the support! Wish you were right. 😉
@Dick, it’s supposed to be a face fanning, as in “Whew, I almost overheated, due to that error.” It didn’t turn out exactly as I expected, but it amused me, so I left it in.