Mursalin Ibne Salehin
In this article, we will learn to stop Excel from rounding large numbers. Whenever we type large numbers in cells, Excel rounds it off. Sometimes, it becomes a problem because we need to display the full number. Today, we will use 3 easy methods. Using these methods, you can easily stop Excel from rounding large numbers. So, without further ado, let’s start the discussion.
Download Practice Book
Download the practice book here.
Why Does Excel Round off Large Numbers?
Excel rounds off large numbers due to some reasons. The reasons are discussed below.
- Small Column Width: If the column width of a cell is small, then, the cell will not be able to display all the numbers.
- 15-Digit Condition: Excel only displays numbers up to 15 It shows 0 instead of the desired number after 15th place. For example, if you type 1111222233334444 in a cell, it will store 1111222233334440.
- Length of the Number: If the number is too large, then, Excel displays it in exponential format.
- Number Format: Sometimes, we format a cell in a specific number format. In those cases, Excel rounds off large numbers.
3 Methods to Stop Excel from Rounding Large Numbers
To explain the methods, we will use a dataset that contains a set of numbers. You can see Excel has rounded off the numbers. Throughout the article, we will use the same dataset with slight changes.
1. Stop Excel from Rounding Large Numbers
We can divide the large numbers into two types. The numbers that have a maximum of 15 digits or less and the numbers that have greater than 15 digits. Here, we will discuss the methods in two different sections.
1.1 Change the Cell Format
Suppose, the numbers have a maximum of 15 digits or less and Excel rounds it off. In that case, you need to change the cell format to stop Excel from rounding large numbers. Let’s follow the steps below to know the process.
STEPS:
- In the first place, select the cells that contain the large numbers.
- Secondly, go to the Home tab and select the Number Format box. A drop-down menu will occur.
- Select Number from there.
- After that, you will see results like the picture.
- Now, autofit the columns to see results like below.
- To remove the decimal places, press Ctrl + 1 to open the Format Cells window and select the Number tab.
- Then, in the Category section, select Number and change the Decimal places to 0.
- Finally, click OK to see the large numbers.
1.2 Convert Number to Text
In this case, we will convert the number to text to stop Excel from rounding large numbers. Generally, an Excel cell stores up to 15 digit numbers. It will add 0 from the 16th place. For example, we have selected a cell and typed 1111222233334444.
Now, if we press Enter, it will display 1.11122E+15. But if you look at the formula bar, then you will see it stores 0 in place of the last digit of the large number we used.
To overcome this problem, pay attention to the steps below.
STEPS:
- First of all, select the cell or cells where you want to type large numbers.
- In the second step, press Ctrl + 1 on the keyboard to open the Format Cells window.
- In the Format Cells window, select the Number tab and then, select Text in the Category section.
- Click OK to proceed.
- After clicking OK, you will see the full large number and a smart tag.
- Finally, click on the smart tag and select Ignore Error.
- You can also overcome this problem by starting the number with an Apostrophe like the image below.
Read More: How to Stop Rounding in Excel (5 Handy Ways)
Similar Readings
- How to Round a Formula with SUM in Excel (4 Simple Ways)
- Rounding Time in Excel to Nearest Hour (6 Easy Methods)
- How to Round Excel Data to Make Summations Correct (7 Easy Methods)
- Roundup a Formula Result in Excel (4 Easy Methods)
- How to Set Rounding Precision in Excel (with Easy Steps)
2. Prevent Excel from Rounding Large Whole Numbers
In the second method, we will learn to prevent Excel from rounding large whole numbers. Here, we can apply the methods to the numbers that contain up to 11 digits. You can see 9, 10 & 11 digits numbers in the dataset below. The numbers are large. That’s why Excel is rounding them to exponential format.
Excel does not round off numbers up to 11 digits. It shows the exact value. But it starts rounding numbers from 12 digits. You can see it in the picture below. It stores them in exponential format. To overcome this, you need to autofit the column width.
Let’s observe the steps below to prevent excel from rounding numbers.
STEPS:
- In the beginning, put your cursor in the line between Columns B & C to adjust the column width of Column B.
- After that, double-click on the mouse.
- Finally, do the same for Columns C & D to see results like the picture below.
Read More: Excel VBA: Round to Nearest 5 (Macro and UDF)
3. Block Excel from Rounding Large Decimal Numbers/Currencies
In the last method, we will block Excel from rounding large decimal numbers or currencies. Here, we will use a dataset that contains two decimal numbers. The numbers are large but Excel is showing up to 3 decimal places.
Let’s follow the steps below to block Excel from rounding numbers.
STEPS:
- Firstly, press Ctrl + 1 to open the Format Cells window.
- In the second step, select the Number tab.
- Thirdly, select Number in the Category section.
- Then, change the decimal places up to the digits you want to display. We have changed the decimal places to 9.
- Now, click OK. You may see results like the picture below.
- Just adjust the column width to see the full numbers.
- In the following step, we will use currencies in place of decimal numbers.
- Again, press Ctrl + 1 to open the Format Cells window.
- In the Format Cells window, select Currency in the Category section and adjust the decimal places.
- In the end, you will be able to see the full value of the currencies.
Read More: How to Remove Decimals in Excel with Rounding (10 Easy Methods)
Things to Remember
There are some things you need to remember when you are trying to stop Excel from rounding large numbers.
- Follow the second sub-method of Method-1 to display large numbers that contain more than 15
- Another important thing is that Excel shows numbers that contain more than 11 digits in exponential format.
Conclusion
In this article, we have demonstrated 3 easy methods to Stop Excel from Rounding Large Numbers. Here, we have used practical datasets to explain the process. I hope these methods will help you to perform your tasks easily. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Last of all, if you have any suggestions or queries, feel free to ask in the comment section below.
