See all How-To Articles

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.

 

excel limit decimal places

 

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.

 

excel decimal number initial data

 

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.

 

number decrease decimal digits

 

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.

 

limit decimal places 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.

 

limit decimal places number format 2

 

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

 

excel decimal number final data

 

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.

 

google sheets format as number

 

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

 

google sheets format as number 2

 

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.

 

google sheets set decimal limit

 

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.

 

google sheets set decimal limit 2

 

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

 

google sheets final data