Excel Rounding: Why Does Excel Round Numbers and How to Turn off Rounding in Excel

In Excel, numbers play a very important role and long numbers can be quite frustrating for users. However, it can be even more annoying when Excel automatically rounds values from a smaller value to a larger or vice versa.

While in some cases, you may need to round numbers, in other cases, it is not necessary to round values. For example, credit card numbers or transaction IDs with a string of 15 characters or more. These digits are specific and cannot be rounded. However, Excel will automatically round the values. So how do you turn off rounding in Excel?

In this article, FPT Shop will show you the reasons Excel rounds values and how to turn off rounding in Excel with different methods.

Why does Excel round numbers?

Here are some reasons why Excel may round numbers:

• The width of the column is not enough to contain all the numbers, so Excel rounds the numbers to fit the last value in the cell that it can fully display.
• The number is too large and is displayed in scientific notation format.
• Excel can only display numbers with a maximum of 15 digits, and any number longer than this will display 0 instead of the number after the 15th digit
• The cell is formatted in a rounded number format and only displays specific digits (or specific digits after the decimal point) in a number.

Now we will examine each case and find out how to turn off rounding in Excel for each specific case.

How to turn off rounding in Excel

1. Turn off rounding in Excel by increasing column width

When you enter a number in a cell, Excel tries to fit that number to a certain width of the cell. And in case the width of the column for entering numbers is not enough, Excel will take the next best option, which is to round the number to display the value in the cell (while still retaining the original number in the cell).

As you can see in the screenshot below, I entered a large number (12354.546), which is fully displayed on the formula bar but rounded in the cell.

In this case, to turn off rounding in Excel, all you need to do is increase the column width and make it wide enough for the digits to be fully displayed. To do this, place the pointer next to the column header (the letter of the column). You will notice that the mouse pointer changes to a two-headed arrow. Then double-click, and Excel will automatically adjust the column width to fit the content with the widest width.

2. Turn off rounding in Excel by preventing Excel from rounding large numbers

When it comes to entering large numbers in Excel, there are a few reasons why Excel can round your large numbers.

• The cell format is set to General and only displays specific digits
• Numbers longer than 15 digits and any number after the 15th digit is displayed as 0

Let’s look at each case and see how we can turn off rounding in Excel for each specific case

3. Turn off rounding in Excel by changing cell format

When the cell format is set to General, Excel displays only a specific number of digits in a cell and any number that exceeds that number is displayed in scientific notation format. Here’s an example of a number formatted in scientific notation in cell A2.

As you can see in the image above, when entering a long number with 11 digits, Excel has no problem displaying the entire number. But when entering a long number with 12 digits, Excel changes the display but converts it to scientific notation format.

To turn off rounding in Excel in this case, all you need to do is change the format of the cell from General to Number with the following steps:

Step 1: Select the cells or range of cells that you want to change the format

Step 2: Go to the Home tab on the ribbon toolbar.

Step 3: By default, the Number group will display the setting as General. You click on the drop-down list of the General item. Then select the Number option as the format.

These steps will immediately make your numbers display as expected. When you change the cell format from General to the Number format, Excel will automatically display 2 decimal places.

If you don’t want to display like that, click the decrease decimal icon Decrease also in the Number group just below (the button with the right-pointing arrow icon) to change it.

4. Turn off rounding in Excel by making text numbers display more than 15 digits

Another issue you may encounter when working with large numbers in Excel is that after 15 digits in a number, Excel converts any digits after the 15th digit to 0. This is by design and Excel considers 15 digits to be meaningful and any digit after that is automatically converted to 0.

This can be an issue when you’re dealing with large quantities (such as credit card numbers or order numbers). Below is an example where I entered the number 1, 16 times in one cell, the first 15 cases are displayed, and the 16th case is converted to 0.

Note: Unlike the other methods, not only does Excel display 0 after the 15th digit and has real numbers following it. Even in the rear, Excel converts any digits after the 15th digit to 0.

To turn off rounding in Excel using this method, you need to convert your number to text format. Although Excel is pre-programmed to handle numbers in a certain way, it doesn’t interfere with text format. And the simplest way to do this is to add an apostrophe before the string of numbers.

This will force Excel to consider anything following the apostrophe as a text string, and as soon as you press Enter, the apostrophe will not be displayed in the cell (so you can see the entire number without the apostrophe).

Here is an example where the number in cell A1 has an apostrophe before it and is displayed in its entirety, but the number in cell A2 doesn’t have an apostrophe and converts the last digit to 0

5. Turn off rounding in Excel with decimal/currency numbers

Excel also has many options for users to decide how many digits after the decimal point will be displayed. This also means that in the case where you have more digits after the decimal point, these digits will only be rounded to display the specified number of digits.

This usually happens when using currency or accounting formats, where numbers always display two digits after the decimal point. And if you have a number with three or four or five digits after the decimal point, it will be rounded to display two digits.

Below is an example where a number has more than two digits after the decimal point but is rounded and displays two digits.

The reason Excel displays in this way is due to the number format of the cells that hold the numbers. And to turn off rounding in Excel in this case, change the format of the cell to be able to display more digits than what is currently displayed. Here are the steps to turn off rounding in Excel by changing the cell format and displaying more digits.

Step 1: Select the cells with the numbers you want to turn off rounding in Excel.

Step 2: Hold down the Control key and press the 1 key, this shortcut combination will immediately open the Format Cells dialog box.

Step 3: Make sure you are on the Number tab. Then click the Number option in the left pane. Or the Currency/Accounting option if you’re using those formats.

Step 4: Change the Decimal places value in the right pane from 2 to 3 or 4 or any number of digits you want to display.

Step 5: Click the OK button and close the Format Cells dialog box.

These steps will ensure that Excel always displays your numbers with the specified number of digits after the decimal point. Any digit after the specified value will be rounded (which may be acceptable in most cases).

Also, note that none of the above ways to turn off rounding in Excel actually changes the value in the cell. It only changes the way it is displayed. So if you enter a number with five digits after the decimal point and only two digits are displayed in the cell, the original number remains as you entered.

Conclusion

So with one of the solutions above, you can turn off rounding in Excel to ensure that the values in the table are displayed correctly in the way you need them for important data tables.