How to Permanently Reduce Decimal Places in Excel (3 Methods)

Dataset Overview

We’ll use the below dataset to demonstrate the methods.

Overview of Reduce decimals permanently in Excel

Method 1 – 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.

2.Problem with reduced decimals

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

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

Go to the File tab.

Click Options to open the Excel Options window.

Confirm the change by clicking OK.
Select Advanced and enable the Set precision as displayed option.

advanced

Confirm the change by clicking OK.

Click OK in the Excel Options window.

This will permanently reduce decimals in your workbook.
Go to the Home tab, select the Number group and click on Decrease Decimal.

Decrease decimals pemanently

You will find that the actual value in the Formula Bar and the displayed value in the cells are equal.

Method 2 – Using Excel Functions and Paste As Values

Excel functions like ROUND, FLOOR, CEILING, TRUNC, and INT can reduce decimals.
After applying any of these functions, use Paste as Values to make the change permanent.

Case 1 – Applying ROUND Function

Select a cell.
Insert the formula: (where D4 represents the number to reduce).

=ROUND(D4,2)

Press Enter.
Drag down the Fill Handle to copy the formula.

Copy the new data range with decreased decimal points.

Reducing decimals using ROUND function in Excel

Right-click the previous data range with higher decimal points.
Choose Paste Options and select Values.

Copying values after using ROUND function to reduce decimals permanently

The stored and displayed values will be equal, permanently removing decimal places.

Result after ROUND function

Remember that clearing calculated values won’t affect the data.

Case 2 – Combining IF and FLOOR Functions

The FLOOR function rounds a number down to the nearest multiple of a specified significance.
To reduce decimal points permanently using this combination:
- Select an empty cell.
- Insert the following formula:
  =FLOOR(D4,IF(D4>0,0.01,-0.01))
  Here, D4 is the cell with the original value.
- 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.
- Right-click the previous data range with higher decimal points and choose Paste Options and click on Values.

Result of using IF and Floor function

This will reduce the decimal places permanently.

Case 3 – Using the CEILING Function

The CEILING function rounds numbers up to the nearest specified multiple.
To permanently reduce decimal places using CEILING:
- Insert the following formula in a cell:
  =CEILING(D4, 0.01)
  Here D4 is the original value.
- Drag down the Fill Handle.

Reducing decimal using CEIL function

- Copy the new data range with decreased decimal points.
- Right-click the previous data range with higher decimal points, choose Paste Options and click on Values.

Result of CEIL function to reduce decimals permanently This will reduce the decimal places permanently.

Case 4 – Using TRUNC Function

The TRUNC function removes decimal parts, leaving only the integer portion.
To reduce decimals permanently using TRUNC:
- Select the cell where you want to calculate the reduced decimal value.
- Insert the following formula: =TRUNC(D4,2)(where D4 is the original value).
- Drag down the Fill Handle.

Using TRUNC function to reduce decimals

Copy the new data range with decreased decimal points.
Right-click the previous data range with higher decimal points, choose Paste Options and click on Values.

Result of TRUNC function to reduce decimals
This will reduce the decimal places permanently.

Case 5 – Using INT Function

The INT function rounds down to the nearest integer by removing decimal parts.
To reduce decimals permanently using INT:
- Select the cell where you want to calculate the reduced decimal value.
- Insert the following formula:
  =INT(D4*100)/100(where D4 is the original decimal value).
- Drag down the Fill Handle.

Using INTO function to remove decimals permanently in Excel

- Copy the new data range with decreased decimal points.
- Right-click the previous data range with higher decimal points, choose Paste Options and select Values.

Result of applying Int function

This will reduce the decimal places permanently.

Method 3 – Using VBA to Remove Decimals Permanently

VBA automates tasks in Microsoft Excel, allowing users to create macros and customize functions.
When working with a large data range, VBA can expedite tasks.
To reduce decimal places permanently using VBA:
- Select the worksheet and right-click on it.
- Choose View Code.

Insert the following code:

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.

- If you return to the worksheet, you will see that decimal points are permanently removed.

Reducing decimals permanently using VBA

Download Practice Workbook

You can download the practice workbook from here:

Reduce Decimals Permanently.xlsm

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