How to Reduce Decimal Places Permanently in Excel (5 Ways)

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

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:
=FLOOR(D4,IF(D4>0,0.01,-0.01))
Here, 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:
=CEILING(D4, 0.01)
Here 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: =TRUNC(D4,2)
Here, 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ⁿ and divide the result from the INT function with 10ⁿ. For example, if you want to round a cell and return only 2 decimal places, your multiplier is 10² 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:
=INT(D4*100)/100
Here, D4 represents the decimal value and 100 represents the multiplier for rounding to 2 decimal places (as 10² = 100).
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.

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

Reduce Decimals Permanently.xlsm

Conclusion

In conclusion, permanently reducing decimal places in Excel empowers users to manage data precision efficiently. Whether through Excel options, functions, or VBA scripting, understanding these methods enhances productivity and ensures accurate data representation. By following these techniques, users can confidently tailor decimal precision to easily suit their analytical needs. This concludes our tutorial. Please share your feedback and queries in the comment section.

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.

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

Get FREE Advanced Excel Exercises with Solutions!