Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Enter 20 Digit Number in Excel (2 Easy Methods)

If you are searching for solutions or some special tricks to enter 20 digit number in Excel then you have landed in the right place. There are 2 ways to enter 20 digit number in Excel. This article will show you every step with proper illustrations so, you can easily apply them for your purpose. Let’s get into the central part of the article.


Download Practice Workbook

You can download the practice workbook from here:


Why Does Excel Show a Fixed Number of Digits?

In Excel cells, you can’t insert a number that is greater than 15 digits. If you want to insert 16 digits or 20 digits numbers then Excel will take only the first 15 digits and make the remaining digits 0. For Example, you want to insert a number value in a cell which is 22334455667788978789 which is of 20 digits. But after entering this value into a cell in Microsoft Excel, you will find that the cell value would become 22334455667788900000. So, Excel is allowing only 15 digits in a cell and makes the remaining digits 0. Excel does this to save memory

Why Excel Won’t Show More Than 15 Digits?

In Excel, you can calculate values in the range  1.79769313486231E+308 or 2.229E-308. This range is huge. If Excel would allow unlimited significant digits in calculations then, it would require gigabytes of memory in a computer. But, Excel is created for the mass people who own a computer of typical configuration. And, allowing unlimited digits for calculation would create crashing scenarios in those computers regularly. Regarding this fact, Excel has limited the significant digits of a number to 15.

💭 Note:

Excel only converts the number to the exponent form and it doesn’t remove any digits, it substitutes the after 15th digits by 0. As, the digits after the 15th carry small significance so converting them to 0 and making exponent form won’t create much error.

While entering any number which contains digits higher than 15, you have to use the methods to be shown in this article to avoid losing any digits of the number. You have to do this because Excel allows only 15 significant digits to convert the remaining digits to 0 and create the exponent form.

How to Enter 20 Digit Number in Excel


2 Methods to Enter a 20 Digit Number in Excel

There are cases when we deal with very large numbers or very small numbers for research or any other purposes. And, we may need to calculate the very precise result so we can’t substitute any digits by zero and we have to use all of the digits of the number for calculations. So, in this situation, you have to show all digits of the number in Excel.

In this section, I will show you 2 quick and easy methods to enter a 20-digit number in Excel on Windows operating system. You will find detailed explanations with clear illustrations of each thing in this article. I have used Microsoft 365 version here. But you can use any other versions as of your availability. If anything of this article doesn’t work in your version then leave us a comment.


1. Converting to Text Format

You can simply change the format of the cell to Text and then insert the 20-digit numbers to get the full number without losing the digits after the 15th. For this, follow the steps below:

📌 Steps:

  • Here, in the dataset, first I have entered 20 digit ID in column D. But, you can see that the digits after the 15th have been substituted by 0. So, I want to insert the full number in column E.
  • For this, first, add a new column to the dataset.

Create empty column to Enter 20 Digit Number in Excel

  • Then, select the cells of the new column and go to the Home tab in the top ribbon.
  • Here, click on the drop-down arrow beside the Number format box.
  • From the list, select the Text option to convert the cells to Text format.

Select TEXT format from top ribbon in Excel

  • Thus, you have converted the cells of range E5:E10 in Text
  • Alternatively, you can change the cell format by rightclicking on the cell. For this, select the cell range and rightclick.
  • Then, the Context Menu bar will open. From here, go to the Format Cells
  • Then, in the Format Cells window, select the Text options and press OK. Thus, the selected range of cells will be converted into Text
  • After that, you have to insert the 20-digit number in those cells.

Entering 20 Digit Number in Excel

  • After inserting numbers in Text formatted cells, you would see that Excel is showing error sign.
  • So, select the cells and click on the error icon and select the “Ignore Error” option.

Ignoring Error regarding numbers in text format in Excel

  • As a result, you will see that the 20 digits numbers are showing in the cells without any loss of digits and errors.

Convert to Text Format to enter 20 digit number in Excel cells

Read More: How to Custom Cell Format Number with Text in Excel (4 Ways)


Similar Readings


2. Adding Apostrophe (‘) in Front of Numbers

There is another tricky way to insert 20-digit numbers in Excel which is to add an apostrophe in front of the number. Though using this method, Excel also shows an error sign, it is an effective way for regular use. For this method, follow the steps below:

📌 Steps:

  • In this method, you have to do just one thing. That is to add an apostrophe (‘) before inserting each 20 digits number.

Adding Apostrophe (‘) in front of Numbers

  • Do the same thing for all of the cells where you will insert numbers that contain greater than 15 digits.

20 digit number with an apostrophe in front

  • Also for this case, there will show a green flag icon at the left-top corner of each cell that contains numbers with apostrophes in front.
  • To fix this, select the cells and click on the error icon showing beside the selected cells and click on the Ignore Error
  • Thus, you have entered 20 digits numbers properly in Excel.

Read More: How to Add Currency Symbol in Excel (6 Ways)


Conclusion

In this article, you have found how to enter a 20-digit number in Excel. I hope you found this article helpful. You can visit our website ExcelDemy to learn more Excel-related content. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Osman Goni Ridwan

Osman Goni Ridwan

I am Ridwan, graduated from Naval Architecture and Marine Engineering Dept, BUET, currently residing in Dhaka, Bangladesh. And my passion is to grow up my skillsets with industry demands. My prime goal is to be a data analyst as I do love to solve problems and play with data.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo