2

I need to display a very big number within Excel, for example a following number: 174587584558159.

Excel displays it by using a scientific notation, like following: 1.74588E+14.

I tried to format a cell like a text, but it didn't help. There's an option to add a single quotation mark at the beginning, but I don't want to use this approach.

What I have found that when I use a custom format with cell by using a pound sign - # it shows a number as I need, but I can't find description of this behavior on the Internet, so I hesitate to use it. The only description I've found, is that it can define how many digits it shows within the fraction part.

So, my question is: can I use # sign to show very big numbers within Excel, and if yes, why? Right now I'm using Excel 2016 (Windows), is this behavior will be same on older versions of Excel?

Anatoly
  • 121
  • 4
    You can get the same by making sure the cell is large enough to display the whole number and format the cell as a number, which is what the # is doing. Most likely, currently it is formatted as scientific notation. But realized that Excel only stores up to 15 significant digits. So 1234567890123456 will show 1234567890123450 with number format. To show the whole number you will need to store it as text. – Scott Craner Jun 28 '18 at 16:54
  • 3
    This is what you're looking for: https://support.office.com/en-us/article/create-or-delete-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4 – music2myear Jun 28 '18 at 16:54
  • 1
    you can only store those as text, because Excel uses IEEE-754 double precision format, so it can't store precisely to that many digits – phuclv Jun 28 '18 at 16:55
  • 2
    Thank you all! @ScottCraner, you can create answer based on your comment, I'll accept it, it's exactly what I was looking for. – Anatoly Jun 28 '18 at 17:33

1 Answers1

0

Because @ScottCraner didn't moved him comment to answer, I do it on my own.

# formats cell as a number. The catch is that Excel can only store up to 15 significant digits, so 1234567890123456 will show 1234567890123450 with number format. To show the whole number it need to be stored as a text.

Anatoly
  • 121