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.

CreditCardExp

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.

CreditCardNumFrmt

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.

CreditCard16

More Information

____________

You may also like...

5 Responses

  1. 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.

  2. teylyn says:

    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

  3. Lincoln says:

    @ Teylyn

    I’m guessing it’s deliberate…

  4. What exactly is that hand gesture at 1:16?

  5. @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.

Leave a Reply to Dick Kusleika Cancel reply

Your email address will not be published. Required fields are marked *