How to Stop Excel from Rounding (Works Every Time)

Excel is smart but might act a little over-smart at times (and that is never desired).

One of the many instances of Excel acting over smart in places where it shouldn’t be rounding off numbers automatically.

Yes, rounding numbers makes them easier to understand and communicate. For example, it is easier to say 99 than 99.76543 🐣

But some situations demand more accuracy and precision than ease. That’s when you’d not want Excel to round numbers.

There are several ways how you can stop Excel from rounding numbers. Download your free practice workbook here to practice them while we read the guide below.

Table of Contents

Stop Excel from Rounding by Adjusting the Column Width

Not every time Excel is to be blamed for being counterproductive. Maybe it’s just trying to adapt to the situation 😁

When a number is populated in a cell, Excel tries to fit it in based on the width of that column.

Like here:

It appears to be 45,604.78.

But look at the formula bar, the numbers appear to have many more decimal places.

Excel has rounded the number to 2 decimal places so that it perfectly fits the existing column width.

Now as you increase the column width (by dragging the column border), the rounded decimal places will begin to expand.

For example, I drag it enough to contain 4 decimal places, so Excel rounds the number to 5 decimal places.

Similarly, if you want the complete number to appear in the cell, continue increasing the column’s width by dragging 🚴‍♀️

Excel will display the decimal places only up to 5 digits by expanding the column width.

If you want more than 5 decimal places and do not want to round them, you’d have to try other methods discussed in this guide.

Pro Tip!

If you’re only interested in the complete number (with no rounding whatsoever) to appear in an Excel cell irrespective of the column’s width:

1. Hover your cursor over the column’s header.
2. When you see the small black double-headed pointer, double-click it.

Excel with auto-adjust the width of the column to incorporate the complete contents of the cell.

Normally, if numbers are formatted as “Number”, Excel would rather show them as hashes if they exceed the cell’s width.

But if formatted as General, Excel rounds them to the adjustable number of decimals or changes them to a scientific format (in the case of whole numbers).

The easy solution to fix this is to increase the column width, and everything will be fixed in place.

Stop Excel from Rounding by changing the Format

The General Format of Excel is a versatile one. It works with numbers and text but has its limitations.

By default, all cells of Excel are formatted as General unless you reformat any cell yourself or populate data into it that’s recognized as another format by Excel 🧐

Under general format, if you populate a number that exceeds 11 digits into any cell, this is what happens:

If the number exceeds 11 digits, even if you adjust the column width, Excel will still turn into a scientific format (under the General format).

Help this situation by changing the format of this number by following the steps below:

Step 1) Go to the Home tab > Number Group > Number Formats.

Step 2) Choose the Number Format.

And the number would be expressed as a proper number now.

If after converting a cell from General to Number Format, you see the cell corrupted with hashes like the below:

You need to adjust the column width.

This is because Excel would change a number to scientific notation to fit it in a cell as long as it’s in General format.

But under the Number format, Excel won’t do so and rather show it as hashes #️⃣

Adjust the rounding in Excel manually

Under the accounting or currency format, Excel automatically limits the number of decimal places to 2.

For example, if I apply the accounting formatting to the number 45604.7792372828 in Excel:

The number of decimal places in it will be limited to 2.

However, this is again only formatting, and the complete number is stored by Excel in the back end.

You can restore the number of decimals, and increase or decrease them as you want by adjusting the decimal places yourself.

There are two ways how you can do this ✌

From the Number Formats

To adjust the number of decimal places from the Number Formats:

Step 1) Select the relevant cells (whose decimal places are to be adjusted).

Step 2) Go to the Home tab > Number group > Number Formats.

Step 3) Go to More Number Formats.

Step 4) From the Formats pane on the left, select the Currency or Accounting format.

Step 5) Set the number of decimal places by using the upward or downward arrow button.

You can also write the number of decimal places you want in the box ✍

Step 6) Press okay and the decimal places will be automatically adjusted.

This method is preferable if you want a large number of decimal places (more than 5).

From the Home tab

This method of adjusting the decimal places and stopping Excel from rounding numbers on its choice is simpler.

Step 1) Select the relevant cells.

Step 2) Go to the Home tab > Numbers Group > Increase Decimal button.

Pro Tip!

These are two buttons; Decimal Increase and Decimal Decrease.

Both are placed next to each other on the Home tab on the Ribbon 🎀

Pressing the Increase Decimal button once to increase one decimal place in the selected cell. Pressing the Decrease Decimal button once decreases on decimal place in the selected cell.

Use both these buttons as many times as many decimal places as you want increased or decreased.

The plus point of this method is that these buttons are right there in front of you, hence, easily and readily accessible 💪

However, if you want to increase or decrease the decimal places by a big number (say 5 or more), some users might find it cumbersome to press a button that many (or more) times, especially if to be done frequently.

Pressing this button increases on decimal place in the selected cell.

Press it as many times as many decimal places as you want increased.

Similarly, you can use the Decrease Decimal button to decrease the decimal places, if wanted.

This method is easier since the buttons are readily accessible.

Convert the numbers to text

Writing a number in Excel looks like this:

But as soon as this number exceeds the count of 15 digits, it looks like this 👀

Digits after the 15th digit are automatically converted to zeros. Excel considers the 16th and onwards digits insignificant and converts them to zeros.

This can be a problem when you are inputting landline numbers, bank account numbers, credit card numbers, and so on.

And the problem is that this is not only about formatting or how the numbers look on the sheet’s face. This is how the numbers are stored with Excel. See the formula bar.

However, there are two ways how you can prevent Excel from doing this:

Step 1) Select a range of cells and convert them to Text format.

Step 2) Then input the numbers in these cells so Excel will treat them as a text string and not make any unasked-for changes to them.

The other method is to add an apostrophe before each number 💡

Like here:

It will not appear on the cell’s face but, you can see it in the formula bar and the cell will be tagged with a small green triangle on the top left corner.

After an apostrophe is added, Excel will not distort the number no matter how long it gets. It is also an excellent way to retain the leading zeros in a cell 🚀

Conclusion

With this tutorial coming to an end, we have learned the top-notch methods to stop Microsoft Excel from rounding numbers.

Beginning from a method as basic as adjusting the column width to tweaking the number formats, we have seen them all.

Learning the science of how numbers act in Excel is something that will always help your Excel jobs. The following Spreadsheeto Excel guides are specially written to help you master the same. Give them a read here:

• Number Formatting in Excel: Step-by-Step Tutorial (2024)
• How to Set Decimal Places in Excel Formula (Easily)
• How to Leave Excel Cells Blank if They Return Zero