What to Know

  • Easiest way: On the Home tab, click Increase Decimal or Decrease Decimal to show more or fewer digits after the decimal point.
  • Create a rule: Go to Home > Number group, select down arrow > More Number Formats. Choose a category and enter decimal places.
  • Set a default: Go to Options > Advanced > Editing Options > Automatically insert a decimal point. Fill in the Places box.

This article explains how to change the number of decimal places displayed in a Microsoft Excel spreadsheet. Instructions apply to Excel 2019, 2016, 2013, 2010, and 2007; Excel for Mac 2016 and 2011; Excel for the web; Excel for Microsoft 365 and Excel for Microsoft 365 for Mac; and Excel Mobile.

Use the Increase Decimal and Decrease Decimal Buttons

For numbers you’ve already entered in a worksheet, increase or decrease the number of decimal places displayed by using the toolbar buttons.

  1. Open Excel to your current worksheet

  2. Select the cells you want to format.

  3. On the Home tab, select Increase Decimal or Decrease Decimal to show more or fewer digits after the decimal point.

    Increase and Decrease Decimal buttons in Excel

    Each selection or click adds or removes a decimal place.

  4. Your new decimal places setting is now in effect.

Apply a Built-In Number Format

In desktop versions of Excel, create custom decimal rules for the various built-in number types using the Number Format dialog box. 

  1. On the Home tab, in the Number group, select the arrow next to the list of number formats, and then select More Number Formats.

  2. In the Category list, depending on the type of data you have, select CurrencyAccountingPercentage, or Scientific.

    Specify the Decimal Places for Built-in Number Formats

  3. In the Decimal places box, enter the number of decimal places you want to display.

  4. Your new decimal places setting is now in effect.

Set a Default Decimal Places Number

If you have a preference and want to set the number of decimal places to display automatically:

This feature isn't available in Excel for web.

  1. Select Options. (In older versions of Excel, select the Microsoft Office Button  > Excel Options.)

  2. In the Advanced category, under Editing options, select the Automatically insert a decimal point checkbox.

    Specify a Fixed Decimal Point for All Numbers in a Worksheet

  3. In the Places box, enter a positive number for digits to the right of the decimal point or a negative number for digits to the left of the decimal point.

  4. Select OK.

    The Fixed decimal indicator appears in the status bar.

  5. On the worksheet, click a cell, and then type the number that you want.

    The change doesn't affect any data entered before you select a fixed decimal.

FAQ

  • How do I filter whole numbers and decimals in Excel?

    Use the formula =INT(A1)=A1, where "A1" indicates the cell to pull data from, then once you've set up the formula on the desired column select Data > Filter and choose how to filter the data. After that, choose either TRUE of FALSE to set the indicator for the given data (whole number or decimal), then select OK to finalize. You should then see TRUE and FALSE displayed next to each of the cells with numbers in them, to indicate those with and without decimals (dependent on which value you chose to indicate which).

  • How do I convert a decimal into a fraction in Excel?

    Select the cell with the decimal number you want to convert to a fraction, then select the Home Menu tab. After that, select the drop-down menu under Number Formatting and select Fraction.

  • How do I convert time to decimals in Excel?

    To convert time to decimals, down to the minute, in Excel use the formula =A1*24*60 or =HOUR(A1) * 60 + MINUTE(A1) + SECOND(A1) / 60, where "A1" indicates the cell containing the time.

  • How do I convert numbers and decimals into time in Excel?

    Use the formula =TEXT(A1/24,"[h] ""hours,"" m ""minutes"""), where "A1" indicates the cell with the data you want to pull from, to convert numbers into hours.

Source