How to stop Excel from rounding numbers
Microsoft Excel is a powerful spreadsheet maker tool that you can utilise to organize and analyze information. Apart from that, Excel also features computation capabilities, pivot tables, graphing tools, and more than. Some of you might take 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 problem for the users in cases where they do not want Excel to circular off the numbers. For example, when yous are entering values similar credit card numbers, scientific data, etc. In this article, we will run across how to end Excel from rounding numbers.
How to stop Excel from rounding numbers
Hither, we will see how to stop Excel from rounding off:
- Large numbers
- Decimal numbers
Permit'south first.
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, information technology 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 higher up 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 information technology is. Only the trouble starts when I entered a number with more than eleven 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 alter its format to Number. To do so, select the jail cell(south) and click on the Home tab. Now, click on the drib-down card under the Number grouping and select Number.
When you change the cell format, yous volition see that Excel automatically adds 2 decimal values after the number. If you do non want Excel to add decimal values, you can remove them past clicking on the Decrease Decimal push under the Number group.
Another problem starts when you enter a value containing more than xv 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 tin meet in the above screenshot, Excel displayed the bodily value for the numbers upwards to xv digits. But when I entered the numbers with more than 15 digits, Excel replaced all the digits later on the 15th digit with 0. This problem can be fixed simply by adding an apostrophe before the number. This will strength Excel to consider the entered value every bit text due to which it does not round that number.
Every bit you can come across in the above screenshot, I take added an apostrophe before the 16, 17, and eighteen digit numbers. This apostrophe is not displayed in the cell but you can see it in the formula bar. Later 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 lot are dealing with scientific data, rounding off the decimal values can create a problem. By default, Excel rounds off the decimal value later on certain digits. One style to solve the trouble is to increase the width of the column.
As you can see in the to a higher place 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 piece of work for large decimal values. In the higher up screenshot, I have increased the cavalcade width, merely Excel displayed only 4 decimal places and rounded off the final decimal place. If yous want to brandish the entire decimal value in Excel, you take 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 lot 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 y'all can finish Excel from rounding numbers.
Read: How to display Numbers as Fractions in Excel.
How practice I get Excel to cease rounding long numbers?
You tin stop Excel from rounding big numbers past changing the prison cell format from Full general to Number. This trick works for numbers containing 15 or less than fifteen digits. For the numbers that contain more than than 15 digits, you lot have to convert the number into the text to stop Excel from rounding them.
Why is Excel rounding xvi digits?
This thing happens considering the cells are set to Number format by default. To fix this problem, y'all have to convert the cell format into text. Later on 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.
Source: https://www.thewindowsclub.com/how-to-stop-excel-from-rounding-numbers
Posted by: mantzhaternithere1988.blogspot.com
0 Response to "How to stop Excel from rounding numbers"
Post a Comment