How to Display Long Numbers in Excel (4 Easy Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will learn to display long numbers in Excel. Excel displays whole numbers to 11 digits. After 11 digits, it displays the numbers in scientific format. Another important thing is that Excel can hold numbers up to 15 digits. If you enter a 16-digit number in Excel, then it will replace the 16th digit with a 0. That is why it is important to know the way to display long numbers in Excel. Today, we will demonstrate 4 ways. Using these methods, you can easily display long numbers.


How to Display Long Numbers in Excel: 4 Easy Ways

To explain the methods, we will use a dataset that contains 12, 14, and 17-digit numbers. You can use the first two methods (Method 1 and Method 2) to display any long numbers. To display numbers with 15 or fewer digits, you can also follow Method 3 and Method 4. Also, in the last section, we demonstrate the steps to display 16/20 digit numbers in Excel.

how to display long numbers in excel


1. Format Cell as Text to Display Long Numbers in Excel

In the first method, we will format cells as text to display long numbers in Excel. You need to format the cells before typing numbers in them. If you format cells after entering the numbers, then this method will not have any effect. So, you need to format the desired cells before entering the long numbers. Let’s follow the steps below to see how we can display the long numbers.

STEPS:

  • First of all, select the cells where you want to enter the long numbers. Here, we have selected the range B6:D8.

Format Cell as Text to Display Long Numbers in Excel

  • Secondly, press Ctrl + 1 to open the Format Cells box.
  • In the Format Cells box, go to the Number tab and select Text.
  • Click OK to proceed.

Format Cell as Text to Display Long Numbers in Excel

  • Now, select Cell B6 and type a 12-digit number.
  • Press Enter.
  • After pressing Enter, you will see the 12-digit number and a warning sign. Don’t worry, it appears because a number is stored as text.

Format Cell as Text to Display Long Numbers in Excel

  • To remove the error, click on the warning sign. A drop-down menu will appear.
  • Select Ignore Error from there.

Format Cell as Text to Display Long Numbers in Excel

  • As a result, you will be able to display long numbers in Excel.

Format Cell as Text to Display Long Numbers in Excel

  • Similarly, you can follow the same steps to display 14/17-digit long numbers.

  • So, the full dataset will look like the picture below after completing the steps.

Read More: How to Enter 16 Digit Number in Excel


2. Show Long Numbers Using Single Quotation in Excel

Another way to display long numbers in Excel is to use a single quotation in front of each number. You need to type a single quotation in front of the number while typing it in the cell. You will not get the correct values for numbers greater than 15 digits after typing it without a single quotation. For example, if you want to display the long number 25251232455213221, then you need to type ‘25251232455213221. Let’s observe the steps below to learn more about the method.

STEPS:

  • Firstly, if you want to display 434556545415, select Cell B6 and type:
'434556545415

Show Long Numbers Using Single Quotation in Excel

  • After that, press Enter to see results like the picture below.

Show Long Numbers Using Single Quotation in Excel

  • In the following step, click on the warning symbol and select Ignore Error from the drop-down menu.

Show Long Numbers Using Single Quotation in Excel

  • You can repeat the same steps to enter a 17-digit number like Cell D6.

  • Finally, you can display all the long numbers like the picture below.

Read More: How to Enter 20 Digit Number in Excel


3. Use Excel TEXT Function to View Long Numbers

We can also use the TEXT function in Excel to view long numbers. The TEXT function changes any serial, date, and number to text. It is necessary to remember that you can use this method only for numbers with 15 or fewer digits. To demonstrate the method, we will use a dataset that contains two lists of numbers. We will multiply them and show the product in another cell. We will display the long product numbers with the TEXT function.

Use Excel TEXT Function to View Long Numbers

To explain the issue, we use the mathematical formula below to multiply the numbers:

=B6*C6

Use Excel TEXT Function to View Long Numbers

You can see the products are stored as scientific numbers. You can see 12, 14, and 15-digit numbers there.

Let’s follow the steps below to learn the way to overcome this problem.

STEPS:

  • In the first place, select Cell D6 and type the formula below:
=TEXT(B6*C6,"0")

Use Excel TEXT Function to View Long Numbers

Here, inside the TEXT function, the first argument denotes the value which is the product of Cell B6 & C6. The second argument denotes the number format. It means we are storing numbers as texts.

  • Finally, press Enter and drag the Fill Handle down to copy the formula.

Use Excel TEXT Function to View Long Numbers

Read More: How to Change Decimals to Percentages in Excel


4. Use Number Format to Display Long Numbers with 15 Digits

If you need to display numbers with 15 or fewer digits, then, you can use the Number Format to display them. Here, we will show you the use of number format with 12, 14, and 15-digit numbers. You can use this method for any numbers with 15 or fewer digits.

So, without further delay, let’s pay attention to the steps below.

STEPS:

  • In the beginning, select the range B6:D8.

Use Number Format to Display Long Numbers with 15 Digits

  • After that, press Ctrl + 1 to open the Format Cells box.
  • Go to the Number tab and select Custom in the Format Cells box.
  • Then, type # in the Type field.
  • Click OK to proceed.

Use Number Format to Display Long Numbers with 15 Digits

  • As a result, Excel will display the long numbers.

Use Number Format to Display Long Numbers with 15 Digits


How to Display 16/20 Digit Numbers in Excel

In order to display 16/20-digit numbers in Excel, you can follow either Method 1 or Method 2. For your convenience, we will use the dataset below to display 16/20-digit numbers using Method 2. To insert 16/20 long numbers, you must type a single quotation before entering the number in a cell.

Suppose, we want to enter 2222333344445555 in Cell C5 first. Let’s follow the steps below to see how we can implement the method.

STEPS:

  • Firstly, select Cell C5 and type the 16-digit number below with a single quotation:
'2222333344445555

How to Display 16/20 Digit Numbers in Excel

  • Secondly, press Enter to see the result.

  • You repeat the same steps to type 20 and 22-digit long numbers.

How to Display 16/20 Digit Numbers in Excel

  • In the following step, select the range C5:C7 and click on the warning sign.
  • A drop-down menu will appear.
  • Select Ignore Error from there.

How to Display 16/20 Digit Numbers in Excel

  • Finally, you will be able to display 16/20-digit numbers in Excel.


Download Practice Workbook

You can download the practice workbook from here.


Conclusion

In this article, we have 4 easy methods to Display Long Numbers in Excel. I hope this article will help you to perform your tasks efficiently. Moreover, we have also discussed the method to Display 16/20 Digit Numbers in Excel. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Lastly, if you have any suggestions or queries, feel free to ask 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.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo