Rafiul Haq
Working with long numbers (more than 11 digits) can be a problem in Excel. There are some auto formatting options in the program to keep the spreadsheet clean. However, many times we need to show the data as accurate and not in approximate form. In this article, we will show you the reason and two solutions to the problem – “long numbers are displayed incorrectly in Excel”.
Download Practice Workbook
You can download the Excel file from the link below.
2 Suitable Solutions If Long Numbers Are Displayed Incorrectly in Excel
When the number is more than 11 digits, the number gets transformed into scientific notation. Again, when the number is more than 15 digits, the number gets rounded in Excel. So, numbers with more than 15 digits will not be displayed correctly; this is an Excel feature. The solution to the problem is to store the numbers in text format. Then the numbers will be displayed correctly.
1. Setting Text Format
In the first solution, we will select a cell range. Then, from the number format, we will set the selected cell range to the text from the number format dropdown list. Lastly, we will type the numbers and they will be displayed correctly in Excel.
Steps:
- To begin with, select the cell range C5:C10.
- Next, from the Home tab, select Text from the Number Format dropdown list.
- Afterward, type the desired long number in cell C5.
- So, the long numbers will be displayed correctly.
Read More: How to Custom Cell Format Number with Text in Excel (4 Ways)
Similar Readings
- How to Round Numbers in Excel Without Formula (3 Quick Ways)
- How to Convert Number to Percentage in Excel (3 Quick Ways)
- Add or Keep Leading Zeros in Excel (10 Suitable Ways)
- Excel Round to 2 Decimal Places (with Calculator)
- How to Round up Decimals in Excel (4 Simple Ways)
2. Using Single Quotation
We will insert the long numbers into the desired cell range using this method. Additionally, we will add a single quote before typing a long number. This single quote or apostrophe denotes text formatting.
Steps:
- Firstly, type the long number with a single quotation in cell C5.
- Secondly, press ENTER. This will display the number in the correct way.
- Similarly, type all the other long numbers into the cell ranges.
Read More: How to Use Number Format Code in Excel (13 Ways)
Conclusion
We have shown you two solutions to the issue – “long numbers are displayed incorrectly in Excel”. If you face any problems regarding these methods or have any feedback for me, feel free to comment below. However, remember that our website implements comment moderation. Therefore, your comment may not be instantly visible. So, have a little bit of patience, and we will solve your query as soon as possible. Moreover, you can visit our site ExcelDemy for more Excel-related articles. Thanks for reading, keep excelling!
Related Articles
- How to Format Number with VBA in Excel (3 Methods)
- How to Put Parentheses for Negative Numbers in Excel
- Keep 0 Before a Phone Number in Excel (6 Methods)
- How to Format Number to Millions in Excel (6 Ways)
- Change Number Format from Comma to Dot in Excel (5 Ways)
- How to Format a Number in Thousands K and Millions M in Excel (4 Ways)
