How to Control the Number of Decimal Places in Excel Formulas?

Are you working with numbers in Microsoft Excel and need to control how many digits display after the decimal point in your formulas and calculations? Excel provides several straightforward ways to specify the number of decimal places that appear in cells containing formulas. In this article, we’ll show you exactly how to use Excel’s built-in options to control decimal places and format numbers in your spreadsheets.

Understanding Decimal Places in Excel

In Excel, the default setting is to display numeric values with two decimal places. However, you often need to show more or fewer digits after the decimal point depending on the requirements of your data and analysis.

Controlling the number of decimal places shown serves several important purposes:

• It allows you to round numbers to a desired precision level
• It helps make your data easier to read and interpret
• It provides control over how numbers align and display in your spreadsheet

The number of decimal places displayed does not impact the underlying values that Excel stores and uses for calculations. Excel maintains full precision in the background; the decimal place settings only impact the visual display.

Methods to Control Decimal Places in Excel Formulas

Excel provides a few different ways to control how many decimal places are shown in formulas. Let’s walk through each of the options:

Method 1: Decrease Decimal & Increase Decimal Buttons

The quickest way to change the number of digits after the decimal point is to use the Decrease Decimal and Increase Decimal buttons on the Home tab of the Excel ribbon.

To use this method:

1. Select the cell or range of cells containing the formulas
2. Click the Decrease Decimal or Increase Decimal button to change the number of digits shown after the decimal point
3. Continue clicking the button until you reach the desired number of decimal places

This method is fast and easy but requires multiple clicks if you need to change the decimal places by more than one or two digits. It’s best for quick adjustments to a small number of cells.

Method 2: Format Cells Dialog Box

To specify an exact number of decimal places, you can use the Format Cells dialog box. This provides full control and allows you to set the number of digits after the decimal to any value from 0 up to 30.

To open the Format Cells dialog box:

1. Select the cell or range with the formulas
2. Right-click and choose Format Cells, or press Ctrl+1
3. In the Format Cells dialog box, make sure the Number tab is selected
4. In the Category list, click Number
5. Set the number of Decimal places to display
6. Click OK to apply the number format

Using the Format Cells dialog box takes a few more steps than the Decrease/Increase buttons, but it allows you specify the exact number of decimal places to use. It’s ideal if you need to show an exact number of digits after the decimal point across multiple cells.

Method 3: ROUND Function

Another way to control decimal places in Excel is by using the ROUND function inside your formulas. The ROUND function rounds a number to a specified number of digits and returns the rounded result.

The syntax for the ROUND function is:

=ROUND(number, num_digits)

• number is the value you want to round
• num_digits is the number of digits to round the number to

Positive values for num_digits round to the specified number of decimal places. Negative values round to the left of the decimal point.

Here are some examples:

Using the ROUND function inside your formulas gives you full flexibility to round the result to however many decimal places you need. The ROUND function changes the actual calculated value, not just the displayed value.

Method 4: FIXED Function

The FIXED function in Excel is similar to the ROUND function, but it allows you to specify the number of decimal places and optionally omit commas. FIXED is useful when you want to display a number with a fixed number of digits after the decimal point.

The syntax for the FIXED function is:

=FIXED(number, [decimals], [no_commas])

• number is the value you want to format
• decimals is the number of digits to display after the decimal point (optional, defaults to 2)
• no_commas is a logical value indicating whether to omit commas (optional, defaults to FALSE which includes commas)

Here are some examples of using the FIXED function:

The key difference between FIXED and ROUND is that FIXED returns a text string instead of a number. This means you can’t perform further calculations on the result of the FIXED function. Use FIXED when you want to control the display format, but use ROUND if you need the result for other formulas.

Method 5: Custom Number Formats

For ultimate control over how numbers display in Excel, you can create a custom number format. Custom formats allow you to specify exactly how many decimal places to show, along with other display settings like commas, currency symbols, percentages, and more.

To create a custom number format:

1. Select the cell or range to format
2. Open the Format Cells dialog box by right-clicking and choosing Format Cells, or pressing Ctrl+1
3. In the Format Cells dialog box, make sure the Number tab is selected
4. In the Category list, click Custom
5. Delete any existing formatting codes in the Type box
6. Enter a custom number format code to control the number of decimal places and other formatting
7. Click OK to apply the custom number format

Here are some example custom format codes for numbers:

These are just a few examples of custom number formats. You can create a wide variety of formats to control how numbers, dates, times, and other values display. The key advantages of custom formats are flexibility and control.

Changing Default Decimal Place Settings

If you frequently work with numbers that require the same number of decimal places, you can change Excel’s default setting so new workbooks will use your preferred number of decimal places automatically.

To change the default number of decimal places shown:

1. Open a new, blank workbook
2. Navigate to File > Options > Advanced
3. Under Editing options, locate When calculating this workbook settings
4. Change the Number of decimal places shown to your desired value
5. Click OK to save the setting

Now when you enter numbers in the blank workbook, they’ll automatically display with your preferred number of decimal places. You can save this workbook as a template to use your preferred decimal place settings in other new workbooks.

Note that changing the default decimal places setting only applies to new workbooks, not existing ones. For existing workbooks, you’ll need to use one of the other methods explained above to adjust the number of digits shown after the decimal point.

Final Thoughts

As you can see, Excel provides several ways to control how many decimal places display in your formulas and cells. Whether you just need a quick adjustment or very specific custom formatting, these techniques will let you display numbers exactly how you want.

Some key points to remember:

• Excel’s Decrease Decimal and Increase Decimal buttons allow quick changes
• The Format Cells dialog box gives you full control over the exact number of decimal places
• The ROUND function lets you round numbers inside formulas to a specified number of digits
• The FIXED function displays numbers with a fixed number of decimal places as text
• Custom number formats provide the ultimate flexibility in controlling how numbers display

Choose the method that works best for your specific situation and spreadsheet needs. With these tools in hand, you’ll be able to format and display your Excel numbers with ease.

FAQs

Vaishvi Desai

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.