How to Stop Excel from Rounding Large Numbers (3 Methods)

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

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. Stopping 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 Changing 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.

Stop Excel from Rounding Large Numbers

  • Secondly, go to the Home tab and select the Number Format box. A drop-down menu will occur.
  • Select Number from there.

Stop Excel from Rounding Large Numbers

  • After that, you will see results like the picture.

Stop Excel from Rounding Large Numbers

  • Now, autofit the columns to see results like below.

Stop Excel from Rounding Large Numbers

  • 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.

Stop Excel from Rounding Large Numbers

  • Finally, click OK to see the large numbers.

Stop Excel from Rounding Large Numbers


1.2 Converting 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.

Stop Excel from Rounding Large Numbers

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.

Stop Excel from Rounding Large Numbers

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.

Stop Excel from Rounding 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.

Stop Excel from Rounding Large Numbers

  • After clicking OK, you will see the full large number and a smart tag.

Stop Excel from Rounding Large Numbers

  • Finally, click on the smart tag and select Ignore Error.

Stop Excel from Rounding Large Numbers

  • You can also overcome this problem by starting the number with an Apostrophe like the image below.

Stop Excel from Rounding Large Numbers

Read More: How to Stop Rounding in Excel

 


2. Preventing 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.

Prevent Excel from Rounding Large Whole Numbers

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.

Prevent Excel from Rounding Large Whole Numbers

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.

Prevent Excel from Rounding Large Whole Numbers

  • After that, double-click on the mouse.

Prevent Excel from Rounding Large Whole Numbers

  • Finally, do the same for Columns C & D to see results like the picture below.

Prevent Excel from Rounding Large Whole Numbers


3. Blocking 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: Rounding to Nearest Dollar in Excel


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.

Download Practice Book

Download the practice book here.


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.


Related Articles


<< Go Back to Stop Rounding | Rounding in Excel | Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mursalin Ibne Salehin
Mursalin Ibne Salehin

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo