How to Stop Excel from Rounding 16-Digit Numbers (2 Methods)
When working with large numbers in Excel, you may encounter issues with rounding. By default, Excel limits the display of numbers to 15 digits, which can cause problems when working with numbers with more than 15 digits. Here are two potential solutions on how to stop Excel from rounding 16-digit numbers.
Introduction to the Dataset
In this dataset, I have some employee details. Notice the account numbers mentioned in column D have 16 digits. For this reason, Excel automatically expressed them as scientific numbers.
Now, in this tutorial, I will show you how I stop Excel from rounding these 16-digit numbers.
Easiest Way to Stop Excel from Rounding 16-Digit Numbers
If your dataset is not too large, adding a single quotation mark (‘) right before the number will be the easiest way for you to stop rounding up 16-digit or larger numbers. When a number is entered in a cell with a single quotation mark (‘) at the beginning, Excel treats it as text even if it looks like a number.
Usage Guide
Step_1: Add a single quotation mark (‘) before each number.
Step_2: Follow this process and add a quotation mark (‘) in the cell range D2:D10.
Final Result >
All the numbers in column D are visible now.
Handle Green Errors
You can remove the green alert signs located on the left top side of the cells by following these steps:
Step_1: Now, select the cell range D2:D10.
Step_2: Then right-click on the yellow alert button located in the top-left corner of the top cell.
Step_3: Finally, select Ignore Error from the drop-down menu.
You can see Excel has removed the Green Error signs, and it looks great now.
Note:
- Do not put a single quotation mark at the end of the number. Only insert it at the beginning of the number.
- Do not use any space between the single quotation mark and the number.
- 2 Ways to Round to Nearest 50 Cents in Excel
- 4 Formulas to Round Off Prices To Nearest 10 Cents in Excel
Alternative Way: Using Text to Columns Feature to Stop Excel from Rounding 16-Digit Numbers
Another way to stop Excel from rounding 16-digit numbers is to use the Text to Columns feature to turn your values into text.
Usage Guide
Step_1: At first, select the cell range (Here, cell D2 to D10).
Step_2: Then go to the Data tab.
Step_3: Now click on the Text to Columns command from the Data Tools group.
You will see the Convert Text to Columns Wizard popping up on the Excel sheet.
Step_4: Here, choose the Delimited option for the file type.
Step_5: Hit the Next button.
Step_6: Now, skip the Delimiters list.
Step_7: Just, click on the Next button.
Step_8: Now, select Text from the Column Data Format.
Step_9: Lastly, press the Finish button.
Final Result >
Now you can see that Excel has restored all the 16-digit numbers in column D.
Handle Green Errors
You can remove the green alert signs located on the left top side of the cells by following these steps:
Step_1: Now, select the cell range D2:D10.
Step_2: Then right-click on the yellow alert button located in the top-left corner of the top cell.
Step_3: Finally, select Ignore Error from the drop-down menu.
All the errors are removed now and it looks fresh.
If you have a large datasheet, this will be the best way to stop rounding up 16-digit numbers.
Challenges You May Face While Transfering Your Data to Another Software
This works great in Excel, but often if you need to shift this data in any other software, the software may consider the single quotation mark as an error and thus not support the number entry.
In these possibilities, you can follow a few more steps to solve this problem.
Step_1: Select a blank cell. I choose cell E2.
Step_2: Insert the formula in cell E2. Doing so the value of cell D2 will appear on cell E2.
=D2Step_3: Then press ENTER.
Step_4: Now, place the cursor on the right bottom corner of cell E2 and double-click on the Fill Handle icon.
Step_5: After that, select the cell range E2:E10.
Step_6: Now right-click on the cell range to display the Context Menu.
Step_7: Select Copy from the menu.
Step_8: Now, right-click on cell D2.
Step_9: Select the Paste Values option from the context menu.
Step_10: Finally, remove the green error signs from the cell range.
Final Result >
You can see the number format is General now in the Number Format Box and all the values are perfectly 16-digit numbers.
Conclusion
I truly hope you’ll find these answers helpful. By using these solutions, you can keep the data precise and dependable without worrying about unwanted rounding. Moreover, feel free to ask any questions you may have in the comment box below. To read more articles related to rounding up in Excel, click here. Thank you for reading, and have a great day!
Related Articles
- How to Round Off Numbers in Excel (9 Methods)
- 3 Formulas for Rounding Time to the Nearest Hour in Excel
- [3 Formulas] Round Off Time to Nearest Multiple of 15 Minutes in Excel
- 5 Ways to Round a Formula with SUM Function in Excel
- 2 Ways to Stop Excel from Rounding up Percentages
- [3 Ways] Round Off Time to Nearest Multiple of 5 Minutes in Excel
![A1 & R1C1 Reference Style in Excel [A Complete Discussion]](https://excelgraduate.com/wp-content/uploads/2022/05/How-to-change-R1C1-to-A1-in-Excel-2.png)
![[3 Ways] Round Off Time to Nearest Multiple of 5 Minutes in Excel](https://excelgraduate.com/wp-content/uploads/2022/07/excel-round-time-to-nearest-5-minutes-2.png)
