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

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

## What is ExcelDemy?

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

Hi there! This is Mursalin. I am currently working as a Team Leader at ExcelDemy. I am always motivated to gather knowledge from different sources and find solutions to problems in easier ways. I manage and help the writers to develop quality content in Excel and VBA-related topics.

We will be happy to hear your thoughts Advanced Excel Exercises with Solutions PDF  