How to Stop MS Excel from Rounding off Numbers

When you type in a large decimal number, MS Excel will likely round off the number. This can be due to several reasons: 

  • The column width is too small
  • The format is set to General instead of Number
  • The number of decimal places to be displayed is set for a lower number.

As a result, it can be annoying when Excel automatically rounds off your numbers. However, you can easily rectify this by using any of the methods given below.

5 Methods to stop Microsoft Excel from rounding numbers:

  • Increase the column width
  • Increase the decimals
  • Format the cells
  • Add an apostrophe
  • Format the number as text

Let us begin by looking at all the methods in detail.


Method #1: Increase the Column Width

In this method, you need to increase the column width so it can accommodate all the numbers. However, this method may not work all the time. Move on to the next method if this doesn’t work.

Step #1: Open your Excel workbook

Open an existing Excel workbook with the rounded-off numbers. 

Step #2: Click on the cell with the rounded-off numbers

Click on the cell with the rounded-off decimal number. 

When you click on the cell, the entire number will be shown in the formula bar. In the cell itself, you will only see a few of the decimal places.

Step #3: Change the border

Hover your cursor over the right border of the column, between the Alphabetic Headers.

As you do that, the cursor changes to a double-headed arrow.

Once this displays, click with your mouse, and drag the cursor to the right. This will increase the width of the column.

You can now see the entire number in cell A1.


Method #2: Increase the Decimals

Another way to show all the numbers is by manually increasing the decimal places. You can set it to how many decimals you want to see.

Step #1: Open your Excel workbook

Open an existing Excel workbook with the rounded-off numbers. 

Step #2: Click on the cell with the rounded-off numbers

Click on the cell with the rounded-off decimal number. 

When you click on the cell, the entire number will be shown in the formula bar. In the cell itself, you will only see a few of the decimal places.

Step #3: Click on ‘Increase Decimal’

In the Home tab ribbon, in the Number section, you can see an Increase Decimal icon. 

Click on it to increase the number of decimals by 1. If you want to increase by 5 decimal points, click the Increase Decimal option 5 times.

You will now see the entire number in cell A1, IF your column is wide enough. 

If your column is not wide enough, you will only see #######.

In that case, follow the steps for Method #1 as well.


Method #3: Format the Cells

You can also change the number of decimal places by entering the number instead of increasing it one by one.

Step #1: Open your Excel workbook

Open an existing Excel workbook with the rounded-off numbers. 

Step #2: Right-click on a cell and select the Format Cells option

Right-click on the cell with the rounded-off number. 

Select the Format Cells… option from the menu that appears.

Step #3: Change the number of Decimal places

The Format Cells dialog box will open. 

Under the Number tab, select the Number category. 

Here, you can see a Decimal places field. 

Increase or decrease the number of decimal places you want to see in the field by clicking on the up or down arrows next to the field. Or you could type in the number.

Step #4: Review and click OK

Click on the OK button to confirm your choice.

You can now see that there are 9 decimal places in cell A1, IF your column is wide enough. 

If your column is not wide enough, you will only see #######.

In that case, follow the steps for Method #1 as well.


Method #4: Add an apostrophe

Since the numbers are getting rounded off because it’s a number, you can change the field to a text field. You can do that by adding an apostrophe at the start of the number. This would change it to a text format. 

But you can’t do calculations if it’s formatted as text. So, only use this method if there are no calculations involved with this number.

Step #1: Open your Excel workbook

Open an existing Excel workbook with the rounded-off numbers. 

Step #2: Double-click on the cell

Double-click on the cell with the rounded-off number. 

Place the cursor completely to the left before the number begins.

Step #3: Add an apostrophe

Now, add an apostrophe in this position. On your keyboard, it should look like this:

The apostrophe won’t appear in your cell. It just converts the number to text.

Step #4: Press Enter

After adding the apostrophe, press Enter

You might see a warning message. 

You can click on the warning icon and select Ignore Error to remove the warning.

You can now see all the decimal places.


Method #5: Format the number as text

You can also format numbers as text using the Format Cells option. You cannot do calculations with this field, as this method changes the numbers to text. So, avoid this method if your Excel workbook has calculations involved.

Step #1: Open your Excel workbook

Open an existing Excel workbook with the rounded-off numbers. 

Step #2: Right-click on a cell and select the Format Cells option

When you click on the cell, the entire number will be shown in the formula bar. In the cell itself, you will only see a few of the decimal places.

Right-click on the cell with the rounded-off number. 

Select the Format Cells… option from the menu that appears.

Step #3: Select the Text category

Under the Number tab, select the Text category.

Step #4: Click OK

Confirm your choice by clicking on the OK button.

You should now see all the decimal places in your cell.


Conclusion

No need to scratch your head about those missing decimals, there may be a good reason for it. And it is easy to fix too.

Always check your column width first, and then you can increase the viewable decimals, simply by clicking on the right icon.

If you will not be using the field for calculations, you can simply convert it to text, using methods #5 or #6. 

But if the decimals are going to be used for calculations, stick to method #3 as it will ensure you have the right format, and that all decimals will be visible.

Leave a Comment