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.
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.
- First of all, select the cells where you want to enter the long numbers. Here, we have selected the range B6:D8.
- 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.
- 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.
- To remove the error, click on the warning sign. A drop-down menu will appear.
- Select Ignore Error from there.
- As a result, you will be able 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.
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.
- Firstly, if you want to display 434556545415, select Cell B6 and type:
- After that, press Enter to see results like the picture below.
- In the following step, click on the warning symbol and select Ignore Error from the drop-down menu.
- 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.
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.
To explain the issue, we use the mathematical formula below to multiply the 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.
- In the first place, select Cell D6 and type the formula below:
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.
Read More: [Fixed!] Excel Number Stored As Text
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.
- In the beginning, select the range B6:D8.
- 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.
- As a result, Excel will display the long numbers.
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.
- Firstly, select Cell C5 and type the 16-digit number below with a single quotation:
- Secondly, press Enter to see the result.
- You repeat the same steps to type 20 and 22-digit long numbers.
- 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.
- Finally, you will be able to display 16/20-digit numbers in Excel.
Read More: How to Enter 20 Digit Number in Excel
Download Practice Workbook
You can download the practice workbook from here.
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.
- How to Change Comma to Dot in Excel
- How to Change International Number Format in Excel
- How to Convert European Number Format to US in Excel
- How to Change Decimals to Percentages in Excel
- How to Convert Percentage to Decimal in Excel
- Excel Number Format Not Working
- How to Enter 16 Digit Number in Excel
- How to Convert Exponential Value to Exact Number in Excel
- How to Format Number with VBA in Excel