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.
Read More: How to Stop Excel from Auto Formatting Numbers
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.
Download Excel Workbook
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
- Why Excel Is Changing My Numbers to DecimalsÂ
- How to Stop Autocorrect in Excel for DatesÂ
- [Fixed!] Excel Changing Dates to Random Numbers
- How to Stop Excel from Changing Numbers to Scientific Notation
- How to Stop Excel from Auto Formatting Hyperlinks
- How to Stop Excel from Changing Numbers to Dates
- How to Prevent Cell Format Changes Automatically in Excel
- [Fixed!] Why Is Excel Changing My Numbers?
- How to Stop Excel from Changing Last Number to 0
- Why Is Excel Changing My Numbers to Zero?
<< Go Back to Excel Auto Formatting | Excel Cell Format | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
