Limit Decimal Places (Significant Figures) in Excel & Google Sheets

In this tutorial, you will learn how to limit decimal places to set the number of significant figures in Excel and Google Sheets.

 

 

To round a decimal number in Excel, you can use the ROUND Function, as detailed here. Below we’ll show how to use cell formatting to limit the number of decimal places displayed while retaining the exact original value.

Limit Decimal Places

If you type a decimal number into a cell without any cell formatting (General format), Excel will display as many decimal places as the cell width allows for. For this example, consider a number with 8 decimal places, as shown below.

 

 

Column B has the standard width, so it displays 6 decimal places.

Although the cell is not explicitly formatted as a number, Excel recognizes is as a number and rounds it up (4.23871695 → 4.238717). Note that the stored value is exactly the same.

To decrease the number of decimal places, select the cell with a number (here, B2), and in the Ribbon, go to Home > Decrease Decimal. One click decreases the number of decimal places by one.

 

 

As shown in the picture above, the number of decimal places in cell B2 is decreases to 5. Also, the cell format automatically changes from General to Number.

You can also limit decimal places using number formatting.

1. Select the cell with a number (B2) and in the Ribbon, go to Home > Number Format.

 

 

2. In the Format Cells window, enter the number of decimal places (for example, 3) and click OK. You can immediately see how the number will look in the Sample box.

 

 

As a result, the number in cell B2 is rounded to 3 decimal places.

 

 

NOTE: If you format the cell as a number, Excel will display 2 decimal places by default.

Limit Decimal Places in Google Sheets

In Google Sheets, decimal numbers are also displayed depending on the column width. Unlike Excel, Google Sheets doesn’t recognize a number unless it’s formatted as one. Therefore, if you reduce the column width, it won’t round the number, just cut off the extra decimal places. When you format the cell as a number, it’s rounded to 2 decimal places. Follow these steps to reduce the number of displays decimal places to one:

1. First format the cell as a number. Select the cell with a number (B2) and in the Menu, go to Format > Number > Number.

 

 

Now cell B2 is formatted as a number with 2 decimal places by default.

 

 

2. Now you can limit the decimal places. Select the cell with a number (here, B2) and in the Menu, go to Format > Number > More Formats > Custom number format.

 

 

3. In the Custom number formats window, enter #,##0.0 and click Apply. Zeros after the decimal point represent the number of decimal places that will be displayed.

 

 

Finally, the number in cell B2 is limited to 1 decimal place.