To reduce decimal places permanently in Excel:

- Go to the
**File**tab and click**Options**to open the**Excel Options**window. - Select
**Advanced**and enable**Set precision as displayed**option.

A warning message will appear. - Click
**OK**in the warning message. - Click
**OK**in the**Excel Options**window.

This will permanently reduce the decimals in your workbook.

When you remove decimals in Excel using the **Decrease Decimal** or** Format Cells** feature, only the displayed value changes and the actual value remains the same. But sometimes you may need to reduce the decimals permanently.

In this Excel tutorial, you will learn how to reduce decimal places permanently. We will use Excel Advanced Options, various functions, and VBA to accomplish this.

Consider the following dataset. There were five 5 decimal places, but I have reduced the decimal places permanently to two decimal places.

Here are 3 ways to reduce decimals permanently:

## Enabling Set precision as displayed Option

If you use** Decrease Decimals** from the **Number** group, the displayed cell value will change but the stored value will remain the same.

To remove decimals permanently here, you have to reduce the decimal points both stored and displayed values.

To reduce decimal places permanently in Excel, follow the steps below:

- Go to
**File**.

- Click
**Options**.Then, the**Excel Options**dialog box will appear. - In the
**Excel Options**window:- Go to
**Advanced**. - Enable the
**Set precision as displayed**option under**When calculating this workbook**.

As a result, a message box will appear to ensure your last change.

- Go to
- Click
**OK**in the warning message box.

- Finally, click
**OK**in the**Excel Options**window.

This will bring you to the active worksheet. - Now, go to the
**Home**tab >**Number**group >**Decrease Decimal**.

Now, you will find that the actual value in the **Formula Bar** and the displayed value in the cell are equal.

**Read More: **How to Change Decimal Places in Exce

## Applying Excel Functions and Using Paste As Values

The Excel function rounds up the decimal places, reducing decimal points permanently. In this section,Â we have used five different Excel functions – **ROUND**, **FLOOR**, **CEILING**, **TRUNC**, and **INT** to reduce decimals. After using any of these functions, we can apply the Paste as Values command to change the values permanently.

Here are the five cases to reduce decimals permanently:

### Case 1: Applying ROUND Function

**The ROUND function** is used for rounding a number to a specified number of digits. If the digit right after the rounding position is 5 or higher, the digit at the rounding position is increased by 1. Otherwise, the digit at the rounding position remains unchanged.

To reduce decimal points applying the **ROUND** function, you have to follow the steps below:

- Select a cell.
- Insert the following formula:

`=ROUND(D4,2)`

Here,**D4**represents the number you want to reduce the decimal points. - Press
**Enter**. - Drag down the
**Fill Handle**to copy the formula in the remaining cells.

- Select the new data range with decreased decimal points.
- Press
**Ctrl+C**to copy the data range.

- Select the previous data range having higher decimal points.
- Right-click to open the context menu.
- Select
**Paste Options**>**Values**.

Finally, you will see that the store value and display are equal meaning the decimal places are permanently removed.

**Note**: You can clear the values calculated using formulas if required. It wonâ€™t change the data.

### Case 2: Combining IF and FLOOR Functions

**The FLOOR function** rounds a number down to the nearest multiple of a specified significance. We can pair it with an **IF** statement to determine the direction of rounding based on the sign of the number.

To reduce decimal points permanently using this combination, follow the steps below:

- Select an empty cell.
- Insert the following formula:

Here,`=FLOOR(D4,IF(D4>0,0.01,-0.01))`

**D4**is the cell where you want to reduce the decimal points permanently. - Copy the formula using the
**Fill Handle**icon.

- Select the new data range with decreased decimal points and press
**Ctrl+C**to copy the data. - Select the previous data range having higher decimal points and right-click to open the context menu.
- Select
**Paste Options**>**Values**.

This will reduce the decimal places permanently.

### Case 3: Using CEILING Function

**The CEILING function** is often used in mathematical calculations to round up numbers. It rounds numbers up, away from zero, to the nearest specified multiple.

Hereâ€™s how you can permanently reduce numbers using the **CEILING** function:

- Insert the following formula in a cell:

Here`=CEILING(D4, 0.01)`

**D4**is the value that you want to reduce the decimal points permanently. - Drag down the
**Fill Handle**. - Select the new data range with decreased decimal points and press
**Ctrl+C**to copy the data. - Select the previous data range having higher decimal points and right-click to open the context menu.
- Select
**Paste Options**>**Values**.

This will reduce the decimal places permanently.

### Case 4: Using TRUNC Function

The **TRUNC** function in Excel truncates a number to a specified number of decimal places or removes the decimal part, leaving only the integer portion.

To reduce decimals permanently using **the TRUNC function**, use the following steps:

- Select the cell where you want to calculate the reduced decimal value.
- Insert the following formula:
Here,`=TRUNC(D4,2)`

**D4**represents the number whose decimal points you want to decrease. - Drag down the
**Fill Handle**.

- Select the new data range with decreased decimal points and press
**Ctrl+C**to copy the data. - Select the previous data range having higher decimal points and right-click to open the context menu.
- Select
**Paste Options**>**Values**.

This will reduce the decimal places permanently.

### Case 5: Using INT Function

**The INT function** rounds a number down to the nearest integer by removing its decimal part. Usually, this function is used for removing all decimal places.

However, if you want to return n decimal places after reducing decimals, then, you have to multiply the argument in the **INT** function with 10^{n} and divide the result from the **INT** function with 10^{n}. For example, if you want to round a cell and return only 2 decimal places, your multiplier is 10^{2} i.e. 100.

To reduce the decimal permanently using the **INT** function, follow the steps below:

- Select the cell where you want to calculate the reduced decimal value.
- Insert the following formula:

Here,`=INT(D4*100)/100`

**D4**represents the decimal value and 100 represents the multiplier for rounding to 2 decimal places (as 10^{2}= 100). - Drag down the
**Fill Handle**.

- Select the new data range with decreased decimal points and press
**Ctrl+C**to copy the data. - Select
**Paste Options**>**Values**.

This will reduce the decimal places permanently.

## Using VBA to Remove Decimals Permanently

VBA automates tasks in Microsoft Excel, allowing users to create macros and customize functions. When you are working with a large data range, VBA helps complete the task quickly.

To reduce decimal places permanently using VBA in Excel, you can follow the steps below:

- Select the worksheet and right-click on it.
- Choose
**View Code**.

- Insert the code below:
`Sub RemoveDecimalsPermanently() Dim source_range As Range Dim target_range As Range Dim cell As Range ' Set the source and target ranges Set source_range = ActiveSheet.Range("D4:D12") Set target_range = ActiveSheet.Range("E4:E12") ' Loop through each cell in the source range For Each cell In source_range If IsNumeric(cell.Value) Then ' Keep the first two decimals and display in the corresponding cell in the target range target_range.Cells(cell.Row - source_range.Rows(1).Row + 1, 1).Value = Round(cell.Value, 2) End If Next cell End Sub`

- Click the
**Run**button.

Now if you return to the worksheet, you will see that decimal points are permanently removed.

**Download Practice Workbook**

## Conclusion

## Frequently Asked Question

### Are there any potential issues with permanently reducing decimal places?

When permanently reducing decimal places, be cautious not to sacrifice essential precision for your specific application. Ensure that the level of rounding is appropriate for the type of data you are working with to avoid misleading interpretations.

### Can I apply different decimal places to different columns or rows in Excel?

Yes, you can apply different decimal places to various columns or rows. Use the Format Cells option (Right-click > Format Cells) and adjust the decimal places in the Number tab. Remember that this doesn’t make the change permanent for future entries.

### How do I lock decimal places in Excel?

To lock decimal places in Excel, select the cell or range of cells, right-click, and choose **Format Cells**. In the** Number** tab of the **Format Cells** dialog box, select the** Number** or **Currency** category and set the desired number of decimal places. Click **OK** to apply the formatting. This ensures that the specified number of decimal places will be displayed for the selected cells, and any new values entered into these cells will be automatically rounded accordingly based on the chosen format.

**Related Articles**

- How to Add Decimals in Excel

- How to Change Decimal Separator in Excel
- How to Change 1000 Separator to 100 Separator in Excel
- How to Remove Decimals in Excel Formula Bar
- How to Reduce Decimals in Excel

- How to Convert Decimal to Whole Numbers in Excel
- How to Convert Decimal to Fraction in Excel
- Excel Decimal Places Problem
- How to Limit Decimal Places in Excel

**<< Go Back to Decimals in Excel | Number Format | Learn Excel**