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.

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

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 ROUNDFLOORCEILINGTRUNC, 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 and select 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 and click on Values.

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.

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

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, choose Paste Options and click on 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, choose Paste Options and select 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.

You can download the practice workbook from here:

• 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

<< Go Back to Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF