How to Stop Excel from Rounding 16 Digit Numbers (2 Easy Ways)

Entering large numbers in Excel worksheets ends up rounding them in accordance with IEEE 754 compliance. Thus, users need to stop Excel from rounding 16-digit numbers. Excel follows a policy of displaying whole or floating numbers up to 11 digits without scientific formation and 15 digits without auto rounding in Number format.

Stopping Excel From Rounding 16 Digit Numbers

In this article, we discuss the reasons behind rounding and ways to stop Excel from rounding 16-digit numbers.


Download Excel Workbook


The Reasons Behind Excel Rounding Numbers

Excel maintains IEEE 754 in storing numbers. Therefore, Excel follows IEEE 754 bindings and other rules while displaying numbers in cells.

  • Numbers containing digits up to 15 will be displayed as a whole in the Number format. Excel rounds the 16th and so forth digits of large numbers by inserting zeros in their places.

Reason behind Rounding Numbers in Excel

  • Widening cells display whole numbers but no more than 11 Excel displays numbers in scientific formation if they cross the 11 digit threshold in the General format.


2 Easy Ways to Stop Excel from Rounding 16 Digit Numbers

Preformatted cells and inserting a single apostrophe lead Excel to stop rounding numbers equal to or more than 16 digits.

Go through the below sections to be able to restrict Excel from rounding large numbers.


Method 1: Preformatting Cells as Text to Stop Rounding Numbers in Excel

Users can preformat desired cells using the Home tab’s Number Format display (inside the Number section) or Format Cells window (click on the Number Format icon or press CTRL+1).

  • Go to the Home
  • Highlight the desired cell or cells.
  • Click on the Number Format display drop down icon.
  • Select Text as a cell format.

Preformatted as Text

  • Afterward type or paste any numbers with any digits. Excel will display all the digits of the numbers.

This method also works for stopping Excel from rounding numbers with 20, 30, or any number of digits.

Read More: How to Stop Excel from Auto Formatting Numbers (3 Easy Ways)


Similar Readings


Method 2: Inserting an Apostrophe to Stop Excel from Rounding 16 Digit Numbers in Excel

Another thing to stop Excel from rounding numbers is to make the number to display as Text. Inserting an Apostrophe preceding the digits converts the cell value into Text. And there are no display constraints for displaying text. So, Excel shows the whole numbers irrespective of digits.

  • Insert an Apostrophe before digits greater than 15 digits. Excel displays them as text thus stopping rounding them.

Stopping Excel From Rounding 16 Digit Numbers

Read More: How to Stop Excel from Changing Numbers (3 Easy Methods)


Things to keep in Mind

There are some things you need to keep in mind when you are trying to stop Excel from rounding large numbers.

  • By default, Excel converts numbers into scientific format when they have more than 11 digits in General In Number format, the number is up to 15, unless Excel rounds them off by putting zeros instead.
  • Inserting an Apostrophe converts cell values into text.

Conclusion

This article discusses the causes behind rounding numbers in Excel to have digits equal to or more than 16. The above-mentioned methods stop Excel from rounding numbers. Use any of them to accomplish your goal.

Do check out our awesome website, Exceldemy, to find interesting articles on Excel.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo