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.


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 enter 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 that 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


How to Enter 20 Digit Number in Excel: 2 Easy Methods

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 format.
  • 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 dialog box.
  • Then, in the Format Cells window, select the Text options and press OK. Thus, the selected range of cells will be converted into Text format.
  • 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 an 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 Display Long Numbers in Excel


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 option.
  • Thus, you have entered 20 digits numbers properly in Excel.

Download Practice Workbook

You can download the practice workbook from here:


Conclusion

In this article, you have found how to enter a 20-digit number in Excel. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Osman Goni Ridwan
Osman Goni Ridwan

OSMAN GONI RIDWAN is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, OSMAN has transitioned into the role of a content developer. Specializing in creating technical content exclusively centered around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo