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

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

Download Practice Workbook

Why Does Excel Show a Fixed Number of Digits?

2 Methods to Enter a 20 Digit Number in Excel

1. Converting to Text Format

2. Adding Apostrophe (‘) in Front of Numbers

Conclusion

Download Practice Workbook

You can download the practice workbook from here:

Entering 20 digit Number in a Cell.xlsx

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.

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.

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.

Thus, you have converted the cells of range E5:E10 in Text
Alternatively, you can change the cell format by right–clicking on the cell. For this, select the cell range and right–click.
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.

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.

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

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.

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

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.

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.