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.
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.
- 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.
- 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)
- [Fixed!] Why Is Excel Changing My Numbers? (4 Reasons)
- How to Stop Excel from Changing Last Number to 0 (4 Easy Ways)
- Why Is Excel Changing My Numbers to Zero? (4 Solutions)
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.
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.
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.