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.

I will show you how I stop Excel from rounding these 16-digit numbers.

Dataset: Stop Excel from Rounding 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.

Use Single Quate Mark to Stop Excel from Rounding 16-Digit Numbers

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.

Selecting Ignore Error Command in Excel

You can see Excel has removed the Green Error signs, and it looks great now.

Excel showing all 16-digit numbers without rounding

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.

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.

Using Text to Columns Feature to Stop Excel from Rounding 16-Digit Numbers

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.

Convert Text to Columns Wizard in Microsoft Excel

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

Excel Showing 16-digit Numbers with Green Alert signs

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.

Using Ignore Error command to Stop Excel from Rounding 16-digit Numbers

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.

=D2

Step_3: Then press ENTER.

Usinf formula to Stop Excel from Rounding 16 Digit Numbers

Step_4: Now, place the cursor on the right bottom corner of cell E2 and double-click on the Fill Handle button.

Using Fill Handle in Excel

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.

Paste Values option from the context menu in Excel

Step_10: Finally, remove the green error signs from the cell range.

Final Result

 Stopping Excel from Rounding 16 Digit Numbers

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!

(Visited 4 times, 1 visits today)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *