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:
- Select the cell or range of cells containing the formulas
- Click the Decrease Decimal or Increase Decimal button to change the number of digits shown after the decimal point
- 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:
- Select the cell or range with the formulas
- Right-click and choose Format Cells, or press Ctrl+1
- In the Format Cells dialog box, make sure the Number tab is selected
- In the Category list, click Number
- Set the number of Decimal places to display
- 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 roundnum_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:
Formula | Result | Description |
---|---|---|
=ROUND(1.456, 2) | 1.46 | Rounds 1.456 to 2 decimal places |
=ROUND(1.456, 0) | 1 | Rounds 1.456 to 0 decimal places (rounds to integer) |
=ROUND(1.456, -1) | 0 | Rounds 1.456 to nearest 10 |
=ROUND(1.456, -2) | 0 | Rounds 1.456 to nearest 100 |
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 formatdecimals
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:
Formula | Result | Description |
---|---|---|
=FIXED(1.2345, 2) | 1.23 | Displays 1.2345 rounded to 2 decimal places |
=FIXED(1234.5678, 1) | 1,234.6 | Displays 1234.5678 rounded to 1 decimal place with commas |
=FIXED(1234.5678, 3, TRUE) | 1234.568 | Displays 1234.5678 rounded to 3 decimals without commas |
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:
- Select the cell or range to format
- Open the Format Cells dialog box by right-clicking and choosing Format Cells, or pressing Ctrl+1
- In the Format Cells dialog box, make sure the Number tab is selected
- In the Category list, click Custom
- Delete any existing formatting codes in the Type box
- Enter a custom number format code to control the number of decimal places and other formatting
- Click OK to apply the custom number format
Here are some example custom format codes for numbers:
Custom Format Code | Description | Example |
---|---|---|
0 | Rounds to nearest integer, no decimal places | 1234 |
0.0 | Rounds to 1 decimal place | 1234.6 |
0.00 | Rounds to 2 decimal places | 1234.57 |
0.000 | Rounds to 3 decimal places | 1234.568 |
0,000 | Rounds to integer with comma separator | 1,235 |
$0.00 | Rounds to 2 decimals with $ currency symbol | $1,234.57 |
0.00% | Rounds to 2 decimals and displays as percentage | 12.35% |
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:
- Open a new, blank workbook
- Navigate to File > Options > Advanced
- Under Editing options, locate When calculating this workbook settings
- Change the Number of decimal places shown to your desired value
- 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
What is the default number of decimal places displayed in Excel?
Do the decimal place settings affect the underlying values in Excel?
What is the quickest way to change the number of decimal places in Excel?
How can I specify an exact number of decimal places in Excel?
Can I change the default decimal place settings for new workbooks in Excel?
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.