# 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 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:

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 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:

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.