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

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.


Download Practice Workbook

You can download the practice workbook from here.


4 Easy Ways to Display Long Numbers in Excel

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 Custom Cell Format Number with Text in Excel (4 Ways)


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: [Solved:] Long Numbers Are Displayed Incorrectly in Excel


Similar Readings


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: Excel Number Stored As Text [4 Fixes]


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

Read More: How to Custom Number Format in Excel with Multiple Conditions


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.

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


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. Also, you can visit the ExcelDemy website for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.


Related Articles

Mursalin Ibne Salehin

Mursalin Ibne Salehin

Hi there! This is Mursalin. I am an Excel and VBA content developer at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I am currently working and doing research on Microsoft Excel. Here I will be posting articles related to Microsoft Excel.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo