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

Get FREE Advanced Excel Exercises with Solutions!

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.

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

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 fourth digits of large numbers by inserting zeros in their places.

• 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.

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

Preformatted cells and inserting a single apostrophe guide 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 tab.
• Highlight the desired cell or cells.
• Click on the Number Format display drop-down icon.
• Select Text as a cell format.

• 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.

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.

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.

Related Articles

<< Go Back to Excel Auto Formatting | Excel Cell Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF