See all How-To Articles

How to Prevent Rounding in Excel & Google Sheets

This tutorial demonstrates how to prevent rounding in Excel and Google Sheets.

 

prevent rounding 6

 

Prevent Rounding Formatted Number

Some numbers with one or more decimal places may be rounded because the format is set to show fewer decimal places. To avoid that, you can format your values with General formatting (the default setting for all cells on a new sheet).

Say you have data set with values in each cell. The numbers in cells A2:A6 appear to be rounded. When you click on the cell you can see the entire number in the formula bar.

 

prevent rounding 7

 

To make Excel not round the values, follow these steps:

  1. Select the cells you want to stop showing as rounded numbers and in the Ribbon, (2) go to Home and (3) click on the Number Format icon in the lower right corner.

 

prevent rounding 2

 

  1. In the Format Cells window (Number tab), click on General, then OK.

 

prevent rounding 3

 

As a result, all the selected cells will have General formatting.

 

prevent rounding 5

 

In some instances, this change alone will still result in some rounding. Keep reading to see how to prevent rounding in those cases.

Prevent Rounding in General Format

If you have a long number in a cell with General formatting, then Excel will automatically display fewer decimal places so that the number can fit within the cell. To avoid this you can do one of the two methods pictured below.

Increase Decimal Places

Say you have a rounded number in the cell because it is too long to fit the cell. To stop showing the rounded number, follow these steps:

 

prevent rounding 11

 

In the Ribbon, (1) go to Home and (2) click on the Increase Decimal button until you reach the number of decimal places you want to display within the cell.

 

prevent rounding 16

 

As a result, the format will change from General to Number, and the width of the cell will change automatically.

 

prevent rounding 12

 

Adjust Column Width

The fastest way to avoid rounding numbers with General formatting is to manually change the width of the column. To do that just place the cursor on the boundary between Columns A and B in the column header and when it changes to a double-headed arrow, drag it to the right to widen column A so the whole number can fit in the cell.

 

prevent rounding 13

 

As a result, the cell fits the whole number and can display it without rounding.

 

prevent rounding 15

 

Prevent Rounding in Google Sheets

Say you have a data range with rounded values in Column A (A2:A6). To stop showing rounded values, follow these steps:

 

prevent rounding 8

 

  1. Select the cells you want to format.
  2. In the Menu, go to Format > Number > Automatic.

 

prevent rounding 9

 

As a result, all values are shown without rounding.

 

prevent rounding 10