How to correct when Excel automatically rounds numbers

Instructions on how to adjust Excel to automatically round numbers quickly and easily. Find out details in TipsMake's article if this is a trick you are interested in!

If you are experiencing a situation where Excel automatically rounds numbers and do not want to use it, turn it off. The instructions in the article will help you adjust it quickly. In addition, TipsMake will also guide you on other rounding methods to make your work more convenient. 

Why does Excel automatically round numbers?

The decimal rounding feature is pleasing to many users, but it is not all-inclusive. The main purpose of Excel's automatic rounding is to display numbers with reasonable accuracy and ease of reading. This helps users work with numbers more easily and avoid confusion due to odd numbers or too many decimal places.

Excel has several default rounding rules, including:

1. Round up: If the 5th decimal place is 5 or greater, Excel will round the number up. For example, 3.4567 will be rounded to 3.457.
2. Round down: If the 5th decimal place is 4 or less, Excel rounds the number down. For example, 3.4564 will be rounded to 3.456.
3. Round up to even: If the 5th decimal place is 5 and the 6th decimal place is even, Excel will round the number up. For example, 3.4550 will be rounded to 3.456.

However, if that doesn't suit your purposes, it's possible to change the way Excel rounds numbers. By changing the number format settings in Excel, editing the number format options, you can control how Excel rounds numbers and the precision of numbers in the spreadsheet. Details in the next section. 

The fastest and most accurate way to turn off rounding in Excel

Method 1: Turn off the automatic rounding feature in Excel according to the example

To turn off automatic rounding in Excel, you can follow these steps:

1. Open Excel and select the ' File ' tab in the top left corner of the window and then select ' Options ' in the left menu.
2. In the ' Excel Options ' window , select the ' Advanced ' tab.
3. Scroll down to the ' Editing options ' section and find ' Automatically insert a decimal point ' and click Uncheck (no tick mark is fine). 
4. Click the ' OK ' button to save the changes and close the Excel options window. 

After performing the above steps, your Excel will not round numbers automatically and will maintain the accuracy of numbers in calculation cells.

How to keep odd numbers in Excel manually

For example, in my article, the number: 99.999983 is rounded to 100. 

1. Method with Increase Decimal key:

Select the number area you want not to round > Home > in the Number section > Select Increase Decimal (Increase the decimal number after the comma.)

Press until all decimal numbers appear. Admin's example is 6 decimal numbers, then I press 6 times and get the result. 

1. Format Cells… method (format)

Select the cell (area - column) you want to edit, then right-click > select Format Cells. 

After the Format Cells. dialog box opens, quickly do the following: 

Select the Number tab > Number section > In the Decimal places section , enter the number of digits after the comma you want to display (in the example, 6) > Click OK to complete. 

You can apply the above 2 methods to round numbers after commas in Excel like: 

1. Rounding to thousands in Excel (4 numbers)
2. Rounding to tens in Excel (2 numbers)

Hopefully the knowledge about Excel rounding numbers automatically will be useful for your work.

Samuel Daniel

Update 09 December 2023