This tutorial demonstrates how to prevent rounding in Excel and Google Sheets.
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.
To prevent Excel from rounding the values, follow these steps:
- Select the cells you want to stop showing as rounded numbers and in the Ribbon, go to Home and click on the Number Format icon in the lower right corner.
- In the Format Cells window (Number tab), click on General, then OK.
As a result, all the selected cells have General formatting.
In some instances, this change alone still results 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 automatically displays fewer decimal places so that the number can fit within the cell. To avoid this, you can use one of the two methods described 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:
In the Ribbon, go to Home and click on the Increase Decimal button until you reach the number of decimal places you want to display within the cell.
As a result, the format changes from General to Number, and the width of the cell changes automatically.
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 until the whole number can fit in the cell.
As a result, the cell fits the whole number and can display it without rounding.
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:
- Select the cells you want to format.
- In the Menu, go to Format > Number > Automatic.
As a result, all values are shown without rounding.