Microsoft Excel is a powerful spreadsheet maker tool that you can use to organize and analyze data. Apart from that, Excel also features computation capabilities, pivot tables, graphing tools, and more. Some of you might have seen that Excel rounds off numbers in some special cases, like when you enter large numbers and decimal values. This is very annoying as it creates trouble for the users in cases where they do not want Excel to round off the numbers. For example, when you are entering values like credit card numbers, scientific data, etc. In this article, we will see how to stop Excel from rounding numbers.
How to stop Excel from rounding numbers
Here, we will see how to stop Excel from rounding off:
- Large numbers
- Decimal numbers
Let’s start.
1] How to stop Excel from rounding large numbers
Excel displays the original number up to 11 digits. When you enter a number that has more than 11 digits, Excel does not display the original value. Instead, it displays the number in the exponential format. This exponential format is difficult to understand for normal users. The below screenshot shows such an example.
In the above screenshot, you can see I have entered three different values, an 11 digit number, a 12 digit number, and a 13 digit number. The 11 digit number is displayed as it is. But the problem starts when I entered a number with more than 11 digits. Excel has rounded off the 12 and 13 digit numbers. The formula bar shows the actual value but the cell shows the rounded value.
Fixing this problem is easy. You just have to change the format of the cell in which you have entered the values containing more than 11 digits. By default, the cell format is set to General. You have to change its format to Number. To do so, select the cell(s) and click on the Home tab. Now, click on the drop-down menu under the Number group and select Number.
When you change the cell format, you will see that Excel automatically adds two decimal values after the number. If you do not want Excel to add decimal values, you can remove them by clicking on the Decrease Decimal button under the Number group.
Another problem starts when you enter a value containing more than 15 digits. In this case, Excel replaces the number after the 15 digits with a 0. For example, in a 16 digit number, Excel replaces the 16th digit with 0. Similarly, in a 17 digit number, Excel replaces the 16th digit and 17th digit with 0.
As you can see in the above screenshot, Excel displayed the actual value for the numbers up to 15 digits. But when I entered the numbers with more than 15 digits, Excel replaced all the digits after the 15th digit with 0. This problem can be fixed simply by adding an apostrophe before the number. This will force Excel to consider the entered value as text due to which it does not round that number.
As you can see in the above screenshot, I have added an apostrophe before the 16, 17, and 18 digit numbers. This apostrophe is not displayed in the cell but you can see it in the formula bar. After adding apostrophes, Excel has displayed the original value without rounding.
Read: How to create a Thermometer Chart in Excel.
2] How to prevent Excel from rounding decimal numbers
When you are dealing with scientific data, rounding off the decimal values can create a problem. By default, Excel rounds off the decimal value after certain digits. One way to solve the problem is to increase the width of the column.
As you can see in the above screenshot, Excel has rounded off the decimal value. The formula bar is showing the original number but the cell is displaying only the rounded number. To fix this problem, you can increase the width of the column.
Increasing the column width does not work for large decimal values. In the above screenshot, I have increased the column width, but Excel displayed only 4 decimal places and rounded off the last decimal place. If you want to display the entire decimal value in Excel, you have to change the cell format.
To change the format of the cell, follow the instructions written below:
- Right-click on the cell and select Format Cells.
- Select the Number tab.
- Select the category according to the data you are dealing with. I have selected the Number category.
- Increase the Decimal places.
- Click OK.
After that, Excel will display the decimal number without rounding it up to the decimal places you have selected. If you see ##### in place of the actual decimal value, just increase the width of the column.
This is how you can stop Excel from rounding numbers.
Read: How to display Numbers as Fractions in Excel.
How do I get Excel to stop rounding long numbers?
You can stop Excel from rounding large numbers by changing the cell format from General to Number. This trick works for numbers containing 15 or less than 15 digits. For the numbers that contain more than 15 digits, you have to convert the number into the text to stop Excel from rounding them.
Why is Excel rounding 16 digits?
This thing happens because the cells are set to Number format by default. To fix this problem, you have to convert the cell format into text. After this, Excel considers the number as a text string and stops rounding it.
We hope this article solved your problem.
Read next: How to create a Custom List in Excel.
Leave a Reply