Excel has its limitations, so it’s a good thing we aren’t discussing disposition at this time. We’ll discuss one restriction in detail and how to overcome it. The way Excel displays big numbers and decimals is unique. As it stands, without making some special efforts, you won’t be able to force Excel to show all the digits of the large number.
A regular integer with more than 11 digits and a decimal with more than 7 digits are considered to be large numbers. What might these numerals with so many digits be used for? The number could be a phone number, debit/credit card number, or numeric identifier. The presence of all the numbers is therefore important, right?
I’ll show you how to take control and stop Excel from rounding numbers in this tutorial.
This Tutorial Covers:
- Why Do Numbers In Excel Round Off
- Stop rounding numbers by widening the columns
- Stop Rounding Large Numbers in Excel
- Changing the Cell Format to Number from General
- Increasing the number text’s digit count to 15 or more
- Turn off rounding of decimal numbers and currencies in Excel
- Things to Remember
- Why Do Numbers In Excel Round Off?
The following are some potential explanations for why Excel may be reducing off numbers:
- Excel ends up rounding numbers because the column width is insufficient to fit all of them, allowing it to show the entire value in the cell.
- The figure is excessively large and presented in exponential form.
- Any number longer than this will display 0 instead of the number after the 15th character because Excel can only display numbers up to 15 digits.
- The cell is designed to round off numbers and only display certain digits in a number (or certain digits after decimal).
2. Stop rounding numbers by widening the columns:
Excel attempts to adjust the appearance of a number entered into a cell to fit the cell width. If the column width is not wide enough, Excel will round the number to display it in the cell while still retaining the original number in the cell. For example, a large number such as 7687689.65457587 may be fully visible in the formula bar but may appear rounded off in the cell due to the limited column width. It is possible to display the full number by either widening the column or changing the cell format to allow for the complete number to be shown. Alternatively, one can display the number in scientific notation to accommodate very large or small numbers.
To disable rounding numbers in Excel and accommodate larger values in a cell, you can increase the column width using the following steps:
The procedure of stopping rounding numbers in excel by increasing the column width is shown below:
Step 1: Position the cursor on the column header border of the column that contains the number you wish to modify.
A double-pointed resizing arrow will appear on the border of the column header.
To increase the column width, you can either double-click on the resizing arrow or click and drag the column header border to a width of your preference. This will adjust the width of the column and allow the full number to be displayed within the cell.
The result looks like below:
3. Stop Rounding Large Numbers in Excel:
There are a few explanations for why Excel might round your large numbers when you input them.
- General is the selected cell style, and only one number length is displayed.
- The number has more than 15 numbers, 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 figures in each one.
- Changing the Cell Format to Number from General:
When Excel’s cell style is set to General, it only displays a certain number of digits in a cell and displays any number in excess of that number in exponential format. (also called the scientific format).
An illustration of a number in exponential notation is shown in cell A3 below.
As seen in the screenshot above, Excel has no trouble displaying the full number when I have a number with 11 digits.
However, it converts a 12-digit integer into an exponential form when I have one.
This one can be fixed quickly. All you have to do is switch the cell’s General style to Number.
How to disable decimal numbers in excel by changing the cell format to number from general is shown below:
Step 1: Choose the cell range or individual cells for which the format needs to be changed.
Step 2: On the “Home” tab, click. Click the formatting dropdown in the “Number” category. Choose “Number” for the style.
Your numbers should appear as anticipated if you follow the above instructions.
When you switch a cell’s format to Number, Excel automatically adds two numbers to the end of the number.
Step 3: Click the “Decrease Decimal” option underneath the “Number” Format to convert the numbers from decimals to integers. To eliminate the two decimal places, you must click twice.
The result look like below:
Additionally, if the cell’s number is replaced by a string of hash symbols, just extend the column. You should see the figure once the column is big enough to fit the whole thing.
- Increasing the number text’s digit count to 15 or more:
Another problem you might encounter when dealing with big 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 any additional digit is automatically converted to a 0.
When dealing with big numbers, this can be a problem. (such as Credit Card numbers or Order Numbers).
In the example below, the first 15 occurrences are displayed, and the 16th, 17th, and 18th numbers are changed to a 0.
Caution: It’s not like Excel just shows me 0 after the 15th digit and has the real amount in the back end like other methods do. Excel changes the numbers after the 15th to 0 even in the background.
How to solve this type of issue:
You must change your number’s structure into text in order to prevent Excel from performing this action. Excel is pre-programmed to handle numbers in a specific manner, but it doesn’t fiddle with the text format. (thankfully).
The most straightforward method for doing this is to place an apostrophe before the number.
When you press enter, the apostrophe would disappear from the cell and Excel would be forced to treat anything that comes after the apostrophe as a text string. (so that without the apostrophe, you could see the full number.)
In the example below, the number in cells A2, A3, and A4 all have apostrophes before them and display the full number, but the number in cell A4 lacks an apostrophe and has the last three digits changed to 0.
The apostrophe will be used to denote the numbers when entering them, but the numbers will not be visible in the column itself. When the cell with the number is selected, there will also be an error notice icon:
It’s not difficult to fix such errors.
The steps to remove this error are shown below:
Step 1: Click the error notification icon after selecting the area containing the errors.
From the options, choose “Ignore Error”.
The numbers will now be presented with their full value and a leading apostrophe after the error has been fixed:
Note: Note that since the numbers are in Text format and cannot be used for calculations or number functions, you should only use this technique if you’re working with numbers that have 16 digits or more.
4. Turn off rounding of decimal numbers and currencies in Excel:
Excel provides users with the option to customize the number of decimal places displayed in a cell. If a number has more decimal places than specified, Excel will round it off to display only the desired number of decimal places. This is particularly useful when working with currency or accounting formats, which typically display two decimal places. Therefore, if a number has more than two decimal places, it will be rounded off to show only two digits.
The following example demonstrates how Excel rounds off numbers with more than two decimal places and displays only two digits:
In cell C3, there is more than two digits after the decimal point. However, Excel rounds off the number and displays only two digits after the decimal point.
The following are the methods to modify the cell format so that more numbers display:
Step 1: Choose the spaces that contain numbers.
Step 2: Press the 1 key while holding down the Control key to display the Format cells dialog window.
Make sure the “Number” option is selected. Choose “Number” (or Currency/Accounting if you’re using those forms) in the left pane. I’m using the Currency style in this illustration. Decimal numbers should be changed from 2 to 3 or 4. (or whatever number of digits you want to display).
To dismiss the dialog box, click Ok.
By following the above instructions, Excel will always display your numbers with a specific amount of digits after the decimal. After the stated value, any additional 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 worth. It only modifies how it is presented.
The original number is thus retained even if you input a number with five digits after the decimal point but only two appear in the cell.
5. Things to Remember:
- When working with large numbers in Excel, it’s important to keep in mind that Excel rounds off any digits beyond the 15th place to zero, both in the displayed cell and in the background. This applies only to numeric data types, not to numbers stored as text.
- If you need to work with numbers that have 16 digits or more, you can use the text format to display the full number. However, it’s important to note that text-formatted numbers cannot be used for calculations or number functions.
- Another important consideration is that Excel displays numbers containing more than 11 digits in exponential format. You can adjust the number format to toggle the exponential display on or off.
These are a few techniques for preventing Excel from rounding numbers.
For ready-to-use Dashboard Templates:
