Microsoft Excel is a strong spreadsheet maker software that you need to use to set up and analyze information. Aside from that, Excel additionally options computation capabilities, pivot tables, graphing instruments, and extra. A few of you may need seen that Excel rounds off numbers in some particular instances, like whenever you enter massive numbers and decimal values. That is very annoying because it creates bother for the customers in instances the place they are not looking for Excel to spherical off the numbers. For instance, when you’re coming into values like bank card numbers, scientific information, and so on. On this article, we’ll see how to stop Excel from rounding numbers.
How to stop Excel from rounding numbers
Right here, we’ll see how to stop Excel from rounding off:
- Massive numbers
- Decimal numbers
Let’s begin.
1] How to stop Excel from rounding massive numbers
Excel shows the unique quantity up to 11 digits. Once you enter a quantity that has greater than 11 digits, Excel doesn’t show the unique worth. As an alternative, it shows the quantity within the exponential format. This exponential format is troublesome to perceive for regular customers. The under screenshot reveals such an instance.
Within the above screenshot, you’ll be able to see I’ve entered three totally different values, an 11 digit quantity, a 12 digit quantity, and a 13 digit quantity. The 11 digit quantity is displayed as it’s. However the issue begins once I entered a quantity with greater than 11 digits. Excel has rounded off the 12 and 13 digit numbers. The formulation bar reveals the precise worth however the cell reveals the rounded worth.
Fixing this drawback is straightforward. You simply have to change the format of the cell wherein you might have entered the values containing greater than 11 digits. By default, the cell format is ready to Normal. You’ve gotten to change its format to Quantity. To take action, choose the cell(s) and click on on the House tab. Now, click on on the drop-down menu underneath the Quantity group and choose Quantity.
Once you change the cell format, you will notice that Excel mechanically provides two decimal values after the quantity. If you do not need Excel to add decimal values, you’ll be able to take away them by clicking on the Lower Decimal button underneath the Quantity group.
One other drawback begins whenever you enter a worth containing greater than 15 digits. On this case, Excel replaces the quantity after the 15 digits with a 0. For instance, in a 16 digit quantity, Excel replaces the sixteenth digit with 0. Equally, in a 17 digit quantity, Excel replaces the sixteenth digit and seventeenth digit with 0.
As you’ll be able to see within the above screenshot, Excel displayed the precise worth for the numbers up to 15 digits. However once I entered the numbers with greater than 15 digits, Excel changed all of the digits after the fifteenth digit with 0. This drawback could be fastened just by including an apostrophe earlier than the quantity. It will drive Excel to take into account the entered worth as textual content due to which it doesn’t spherical that quantity.
As you’ll be able to see within the above screenshot, I’ve added an apostrophe earlier than the 16, 17, and 18 digit numbers. This apostrophe just isn’t displayed within the cell however you’ll be able to see it within the formulation bar. After including apostrophes, Excel has displayed the unique worth with out rounding.
Learn: How to create a Thermometer Chart in Excel.
2] How to stop Excel from rounding decimal numbers
When you’re coping with scientific information, rounding off the decimal values can create an issue. By default, Excel rounds off the decimal worth after sure digits. A method to resolve the issue is to enhance the width of the column.
As you’ll be able to see within the above screenshot, Excel has rounded off the decimal worth. The formulation bar is exhibiting the unique quantity however the cell is displaying solely the rounded quantity. To repair this drawback, you’ll be able to enhance the width of the column.
Growing the column width doesn’t work for big decimal values. Within the above screenshot, I’ve elevated the column width, however Excel displayed solely 4 decimal locations and rounded off the final decimal place. If you need to show all the decimal worth in Excel, you might have to change the cell format.
To alter the format of the cell, observe the directions written under:
- Proper-click on the cell and choose Format Cells.
- Choose the Quantity tab.
- Choose the class in accordance to the information you might be coping with. I’ve chosen the Quantity class.
- Improve the Decimal locations.
- Click on OK.
After that, Excel will show the decimal quantity with out rounding it up to the decimal locations you might have chosen. In the event you see ##### in place of the particular decimal worth, simply enhance the width of the column.
That is how one can stop Excel from rounding numbers.
Learn: How to show Numbers as Fractions in Excel.
How do I get Excel to stop rounding lengthy numbers?
You possibly can stop Excel from rounding massive numbers by altering the cell format from Normal to Quantity. This trick works for numbers containing 15 or lower than 15 digits. For the numbers that include greater than 15 digits, you might have to convert the quantity into the textual content to stop Excel from rounding them.
Why is Excel rounding 16 digits?
This factor occurs as a result of the cells are set to Quantity format by default. To repair this drawback, you might have to convert the cell format into textual content. After this, Excel considers the quantity as a textual content string and stops rounding it.
We hope this text solved your drawback.
Learn subsequent: How to create a Customized Record in Excel.
