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 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.
 Rightclick 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.
 Rightclick 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.
 Insert the following formula in a cell:

 Copy the new data range with decreased decimal points.
 Rightclick 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.
 Rightclick 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.
 Rightclick 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 rightclick 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.
 Insert the following code:

 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 (Rightclick > 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.
 Rightclick 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.
 To lock decimal places in Excel:
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
Get FREE Advanced Excel Exercises with Solutions!