Skip to content How to Prevent Rounding in Excel for Numbers (Decimals/Fractions):
Excel is an incredibly sophisticated spreadsheet program that has been created to optimize the productivity of its users.
While it often functions as intended, there are rare situations where it may work against you.
When Excel decides to round off numbers is one such instance.
I’ll demonstrate how to stop Excel from rounding numbers in this tutorial.
This instruction explains: 1. Why Do Numbers In Excel Round Off? 2. Stop rounding numbers by widening the columns 3. Stop Rounding Big Numbers in Excel 3.1 Changing the Cell Format to Number from General 3.2 Increasing the number of text's digit count to 15 or more 4. Turn off rounding of decimal numbers and currencies in Excel
1. Why Do Numbers In Excel Round Off?
The following are some potential explanations for why Excel may be rounding off numbers:
- Excel ends up rounding integers since the column width is insufficient to fit all of them, allowing it to display the entire value in the cell.
- The number is too huge and shown in exponential form.
- Any number longer than this will display 0 instead of the number after the 15th digit since Excel can only display numbers up to 15 digits.
- The cell is structured to round off numbers and only display certain digits in a number (or certain digits after decimal).
The next table will examine each of these situations and explain how to prevent Excel from rounding values in each one.
2. Stop rounding numbers by widening the columns
Excel makes every effort to fit the number you input in a cell within the constraints of the cell’s width.
Additionally, Excel rounds the number to show the value in the cell if the width of the column in which the number is input is insufficient (while still keeping the original number in the cell).
I input a big value (12354.546), which is visible in the formula bar but is rounded down in the cell, as you can see in the image below.
How to Resolve This
The only thing you have to do is widen the column so that the numbers may be shown in their entirety.
Put your cursor near the margin of the column header to do this (the column alphabet). Your cursor will now have two points, as you can see in the image. When you double-click, the column width will adjust itself to fit the most wide-ranging content.
3. Stop Rounding Big Numbers in Excel
There are a few reasons why Excel can round your huge numbers when you enter them into the program.
- General is the selected cell format, and just one number length is displayed.
- The number has more than 15 digits, and any digits past the 15th are displayed as 0.
Let’s examine each of these situations and determine how to prevent Excel from rounding our numbers in each one.
3.1 Changing the Cell Format to Number from General
Excel only displays a certain amount of digits in a cell when the cell format is set to General, and any value over that is displayed in exponential format (also called the scientific format).
An illustration of a number in exponential format is shown in cell A2 below.
As seen in the picture above, Excel has no trouble displaying the whole number when I have a number with 11 digits.
However, it turns a 12-digit number into an exponential form when I have one.
How to Resolve This
This one can be fixed quickly. All you have to do is switch the cell’s General format to a Number.
Here are the steps to do it:
- Decide which cells or range of cells you want to format-change.
- On the Home tab, click
- Click the formatting dropdown in the Number group.
- Decide on “Number” for the format.
Your numbers should appear as predicted if you follow the preceding instructions.
When you switch a cell’s format to Number, Excel automatically adds two digits to the end of the number. Click on the Decrease Decimal icon on the ribbon’s Home tab if you don’t want these.
Additionally, if the cell’s number is replaced with a string of hash symbols, just expand the column. You should see the number once the column is big enough to fit the whole thing.
3.2 Increasing the number of text’s digit count to 15 or more
Another problem you might encounter when working with large numbers in Excel is that after 15 digits, Excel changes any remaining digit to a 0.
Excel only recognizes the first 15 digits as important by design, and each additional number is immediately changed to a 0.
When dealing with huge numbers, this can be an issue (such as Credit Card numbers or Order Numbers).
In the example below, when I input the number 1 16 times in a cell, the first 15 occurrences are displayed, and the 16th is changed to a 0.
Contrary to other methods, Excel is not simply showing me 0 after the 15th digit while storing the true number in the background. Excel changes the digits after the 15th to 0 even in the background.
How to deal with this
You must change your number’s format into the text to prevent Excel from performing this action. Excel is pre-programmed to handle numbers in a specific way, but it doesn’t fiddle with the text format (thankfully).
The most straightforward method for doing this is to use an apostrophe before the number.
When you click enter, the apostrophe would disappear from the column and Excel would be forced to treat everything that comes after the apostrophe as a text string (so you would be able to see the entire number without the apostrophe)
The number in cell A1 has an apostrophe before it and displays the complete number in the example below, while the number in cell A2 lacks an apostrophe and changes the last digit to a zero.
4. Turn off rounding of decimal numbers and currencies in Excel
Excel also allows users the freedom to choose how many digits should appear following the decimal point.
This might also indicate that any more digits following the decimal would be rounded off to display the required amount of digits.
When utilizing the currency format or the accounting format, where the numbers always appear with two digits after the decimal point, this is frequently the case. Additionally, if a number includes three, four, or five digits after the decimal point, those digits are rounded off to two to represent the true number.
In the example below, the value has more than two digits after the decimal point, but it is rounded and just two digits are displayed.
How to Resolve This
The format of the numbers in the cells where you have them is the source of this.
Change the cell format so that Excel can display more numbers than it is now able to prevent it from rounding these values.
The following are the procedures to modify the cell format so that more numbers display:
- Choose the cells that contain numbers.
- Press the 1 key while holding the Control key.
- Ensure that the “Number” tab is selected.
- If you’re utilizing those formats, choose Number in the left pane (or Currency/Accounting otherwise).
- Decimal places should be changed from 2 to 3 or 4. (or whatever number of digits you want to display).
- Dialog box should be closed.
By following the above instructions, Excel will always display your numbers with a certain amount of digits after the decimal. After the provided value, any more digits would be rounded (which is acceptable in most cases)
Additionally, keep in mind that none of this has any effect on the cell’s value. It merely modifies how it is presented.
The original number is thus retained even if you enter a number with five digits after the decimal point but only two appear in the cell.
These are a few techniques for preventing Excel from rounding figures.
I sincerely hope this tutorial was helpful.
