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

Dataset Overview

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

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.

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.

• Confirm the change by clicking OK.
  

• Finally, click OK in the Excel Options window.
  

• This will permanently reduce decimals in your workbook.
• Go to the Home tab > Number group > Decrease Decimal.
  

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

Read More: How to Change Decimal Places in Excel

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.
  
• Right-click the previous data range with higher decimal points.
• Choose Paste Options > Values.

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

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

This will reduce the decimal places permanently.

Case 3 – Using 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.

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

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.
    
• Copy the new data range with decreased decimal points.
• Right-click the previous data range with higher decimal points and choose Paste Options > Values.

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.
    

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

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.

Download Practice Workbook

You can download the practice workbook from here:

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.

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
• How to Limit Decimal Places in Excel

<< Go Back to Number Format | Learn Excel