How to Stop Excel From Rounding? 4 Easy Ways
(Note: This guide on how to stop Excel from rounding is suitable for all Excel versions including Office 365)
In Excel, numbers play a very major role. Large numbers can be quite bothersome. But, it is even more annoying when Excel automatically rounds off a larger value into a smaller value.
Though in some cases you might need to round off the numbers, in other cases it is unnecessary to round off the values. For instance, a credit card number or transaction ID has a sequence of 15 characters or more. These digits are specific and cannot be rounded off. But, Excel automatically rounds off the values. So what do you do?
In this article, I will show you why Excel rounds the values and how to stop Excel from rounding the values using 4 easy methods.
You’ll Learn:
Watch our video on how to stop Excel from rounding numbers
Related:
How to Use the ROUND Function in Excel?
How to Convert Excel to Word? 2 Easy Methods
How to Sort Dates in Excel? 6 Easy Methods
First, let us see why and how Excel rounds the values.
Why Does Excel Round Larger Numeric Values?
Consider an example, enter a number consisting of more than 18 digits and press Enter.
In a number sequence consisting of many digits, Excel automatically rounds off the numeric values greater than 15 digits. Only the first six digits are appear and the other exceeding digits are shown as exponential(E) values.
However, in the formula bar, the whole number is displayed up to 15 digits and the digits after that are converted to zero.
You might think that, maybe Excel displays only 15 digits in the front end but stores the complete input value. However, even in the backend Excel only takes the first 15 digits into account and the rest are converted to zero.
Other reasons Excel rounds values might be due to change in decimal places, shorter column width, and improper formatting of the cell.
Now, let’s see how to stop Excel from rounding the values.
How to Stop Excel From Rounding Values?
By Changing the Format of the Cells
Excel rounds off larger numbers because of the different formatting of the cell. In Excel, the default cell formatting is General. When the cell format is general, Excel only shows a specific number of digits. However, if the cell formatting is Number, Excel can show more digits in the cells.
To change the cell formatting from General to Number, select the cell you want to change the format.
Go to Home and in the Number section from the dropdown select Number.
Now you can see the format of the cell changed, and the value exhibited as a complete number.
You can also use a keyboard shortcut to change the format of the cells.
Select the cells to change the formatting. Now press Ctrl +1.
This opens up a Format Cells dialog box.
Select Number. You can also specify the decimal places and significant digits and click OK.
This converts the format of the cells and prevents Excel from rounding the values.
By Changing Format to Currency
When numbers are to be represented as currencies for banking or accounting purposes, the numbers have to be accurate to achieve proper outcomes. In such cases, rounding off the values will cause inaccuracies in calculations and totals.
Also, currency values contain multiple commas to differentiate the number of places. So, to avoid this you can change the format of the cell to Currency or Accounting.
You can change the format of the cell by going to Home. Under the Number section, select Currency or Accounting from the dropdown. Or, you can use the keyboard shortcut Ctrl+1 to choose the formatting. You can select the significant places after the decimal point and choose the symbol of currency.
Now click OK. This changes the format of the cells into currency and applies the separators and decimal places.
Also Read:
How to Indent in Excel? 3 Easy Methods
How to Delete a Pivot Table in Excel? 4 Best Methods
How to Unmerge Cells in Excel? 3 Best Methods
By Changing the Decimal Places
You can also prevent rounding the numbers in Excel by changing the decimal places.
Consider a number having an 18-digit value. If you press enter, the number rounds up and shows up in an exponential format. Here, you can change the decimal place to display the original number.
To change the decimal place, select the cell with the rounded value. Navigate to Home. Under the Number section, click on the Increase Decimal option to extend the significant digits. As the decimal places increases, the significant digits also increases.
This option gives you the liberty to choose the number of digits you want to display.
By Adding Apostrophe
We know Excel does not display numbers with more than 15 digits. But, in case you want to display more numbers with more digits, what would you do?
In some cases, denoting numbers consisting of more than 15 digits is necessary. Values like bank balances or telephone numbers might not be displayed in Excel.
To overcome this, you can add an apostrophe (‘) in front of the number. Adding an apostrophe converts the number to a string.
Excel only has a display restriction for numbers, whereas a string does not have any restrictions.
However, you cannot perform any functions or operations on the numbers. In case you want to perform any function, you can convert the text to a number.
By Extending the Width of the Column
The reason Excel was not able to display the full range might be because of the width of the column.
Even after performing operations to prevent rounding off, sometimes Excel throws an error because column width is less than the number.
To overcome this error, you just have to extend the column tab.
Place the mouse pointer in the column header. You can see the mouse pointer change to a double-arrowed pointer. Click on the column and drag the column to your desired width.
Suggested Reads:
How to Make Excel Track Changes in a Workbook? 4 Easy Tips
How to Apply the Accounting Number Format in Excel? (3 Best methods)
How to Add Error Bars in Excel? 7 Best Methods
Closing Thoughts
Preventing Excel from rounding off the values can help you keep track of a larger range of values and provides maximum accuracy for performing operations.
In this article, we saw multiple methods to stop Excel from rounding off the values. Choose the method that suits you best.
If you need more high-quality Excel guides, please check out our free Excel resources center.
Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.
Simon Calder
Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!