We have a simple dataset of numbers that have a decimal number in the formula bar, which we’ll replace or display as an integer.

*Note:** We used Microsoft 365 to prepare this article. However, you can find the features and functions used in this tutorial in Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel Starter 2010 versions as well.*

## Stored Value and Display Value Are Different in Excel

In the following simple dataset, we have a few numbers with decimal places. We want to remove one decimal place by using the **Decrease Decimal** feature.

After clicking the **Decrease Decimal** feature, the displayed cell value will change, but the stored value (displayed in the formula bar) will remain the same.

## Method 1 – Enable Set Precision as Displayed Option to Remove Decimals in Excel Formula Bar

We have a dataset like in the following image. The display value and the stored value is different.

**Steps:**

- Go to the
**File**tab.

- Click on the
**Options**command.

- Choose
**Advanced**and scroll down to**When calculating this workbook**.

- Choose the option
**Set precision as displayed**. You will get a**caution notice.**

*Note**: As enabling the*

**Set precision as displayed**option permanently changes the stored value, your data may get increasingly inaccurate over time. Enable this option only if the displayed precision maintains the accuracy of your data.- Click the
**OK**button in the notice. - Click the
**OK**button in the**Excel Options**dialog box. You will return to your worksheet and find that the displayed value and the stored value are now equal.

**Read More: **How to Remove Decimals in Excel

## Method 2 – Removing All Decimals from Formula Bar in Excel

### Case 2.1 – Apply the INT Function and Use the Paste as Values Option

**Steps**:

- In cell
**C5**, insert the following formula:

`=INT(B5)`

- Press the
**Enter**key and use the**Fill Handle**tool to copy the formula in the remaining cells.

*Note**: The*

**INT**function makes a negative number more negative by rounding it down.- Select the range
**C5:C12**and click the**Copy**button from the**Clipboard**menu. You can also use the keyboard shortcut**Ctrl + C**.

- Select cell
**B5**and click on the**Paste**dropdown.

- Click the
**Values (V)**command. You will see that the stored number in cell**B5**(or other cells in range**B5:B12**) will be equal to the displayed number in the formula bar.

**Tip**: You can use the keyboard shortcut**Ctrl + Shift + V**to quickly paste as values.### Case 2.2 – Remove All Decimals Using the CEILING Function and the Paste as Values Option

**Steps**:

- Insert the following formula into cell
**C5**

`=CEILING(B5,1)`

- Press the
**Enter**key and use the**Fill Handle**tool to copy the formula in the remaining cells.

*Note**: You can use the*

**CEILING.MATH**function as an alternative to the**CEILING**function.- Copy the range
**C5:C12**and paste it as values in cell**B5**. The same rounded-up values can be seen in cells and the formula bar.

### Case 2.3 – Use the FLOOR Function to Remove All Decimals

**The FLOOR function** rounds down a number to the nearest integer.

**Steps**:

- Insert the following formula into cell
**C5:**

`=FLOOR(B5,1)`

- Press the
**Enter**key and use the**Fill Handle**tool to copy the formula in the remaining cells.

*Note**: You can use the*

**FLOOR.MATH**function instead of the**FLOOR**function.- Copy the range
**C5:C12**and paste as values in cell**B5**.

### Case 2.4 – Use VBA to Remove All Decimals in Excel Formula Bar

**Steps**:

- Go to the
**Developer**tab and select the**Visual Basic**option.

- The
**Visual Basic Editor**window will open. Go to the**Insert**menu and select the**Module**option.

*Note**: If the Developer tab is not available in your Excel ribbon, then you can use the keyboard shortcut Alt + F11 to open the Visual Basic Editor window.*

**Module1**will appear.- Insert the following code in the module, click the
**Save**button, and click the**Run**button.

```
Sub RemoveDecimalPoints()
Dim last_row As Integer
Dim firstRow_index As Integer
Dim dataCol_index As Integer
Set ws = ThisWorkbook.ActiveSheet
' index of the first row in your dataset
firstRow_index = 5
' index of the column for which you want to remove decimals
dataCol_index = 2
last_row = ws.Cells(Rows.Count, dataCol_index).End(xlUp).Row
Set data_range = ws.Range(Cells(firstRow_index, dataCol_index), _
Cells(last_row, dataCol_index))
For Each cell In data_range.Cells
cell.Value = Int(cell.Value)
cell.NumberFormat = "0"
Next cell
End Sub
```

After running the above VBA, all decimal places will be removed.

**Read More: **How to Reduce Decimals in Excel

## Method 3 – Removing Decimals to a Specific Place from Formula Bar

### Case 3.1 – Use the ROUND Function and Paste as Values Option

**The ROUND function** in Excel rounds a number to a specified number of decimal places. 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.

We will keep only 1 decimal place.

**Steps**:

- Apply the following formula in cell
**C5:**

`=ROUND(B5,1)`

- Press the
**Enter**key and use the**Fill Handle**tool to copy the formula in the remaining cells.

- Select the range
**C5:C12**and click the**Copy**button from the**Clipboard**menu. You can also use the keyboard shortcut**Ctrl + C**as an alternative.

- Select cell
**B5**and click the**Paste**dropdown.

- Click the
**Values (V)**command.

**Note**: The**ROUND**function can remove all decimals as well. If you want to remove all decimal places using the**ROUND**function, then set the**num_digits**argument to**0**.### Case 3.2 – Apply the ROUNDUP Function

**The ROUNDUP function** always rounds up a value to a specified number of decimal places. Removing decimals with this function is very similar to the previous case. We will keep only 1 decimal place here as well.

**Steps**:

- Insert the following formula into cell
**C5:**

`=ROUNDUP(B5,1)`

- Press the
**Enter**key and use the**Fill Handle**tool to copy the formula in the remaining cells.

- Copy the range
**C5:C12**and paste as values in cell**B5**.

### Case 3.3 – Use the ROUNDDOWN Function

**The ROUNDDOWN function** always rounds down a number to a specific number of digits.

**Steps**:

- In cell
**C5**, use the following formula:

`=ROUNDDOWN(B5,1)`

- Press the
**Enter**key and use the**Fill Handle**tool to copy the formula in the remaining cells.

- Select the range
**C5:C12**and paste it into cell**B5**as values.

### Case 3.4 – Apply the TRUNC Function

**Steps**:

- Insert the following formula in cell
**C5:**

`=TRUNC(B5,1)`

- Press the
**Enter**key and use the**Fill Handle**tool to copy the formula in the remaining cells.

- Select the range
**C5:C12.** - Copy it and paste it as values into cell
**B5**.

*Note**: The*

**TRUNC**function with**0 [num_digit]**argument simply truncates the number. But the**INT**function rounds down the number. So, they return the same result for positive numbers. But for negative numbers, they work differently.### Case 3.5 – Use VBA to Keep a Specific Number of Decimal Places

**Steps**:

- Follow the
**Method 2.4**to insert a**Module**in**Visual Basic Editor.** - Enter the following code in the module, then click the
**Save**button.

```
Sub KeepSpecificDecimalPoints()
Dim decimal_places As Integer
On Error Resume Next
decimal_places = InputBox("Enter the number of decimal places:")
If Not IsNumeric(decimal_places) Or decimal_places < 0 Then
MsgBox "Invalid input! Please input a valid number of decimal points."
Exit Sub
End If
Set ws = ThisWorkbook.ActiveSheet
Dim last_row As Integer
Dim firstRow_index As Integer
Dim dataCol_index As Integer
' index of the first row in your dataset
firstRow_index = 5
' index of the column for which you want to remove decimals
dataCol_index = 2
last_row = ws.Cells(Rows.Count, dataCol_index).End(xlUp).Row
Set data_range = ws.Range(Cells(firstRow_index, dataCol_index), _
Cells(last_row, dataCol_index))
For Each cell In data_range
cell.NumberFormat = "0." & String(decimal_places, "0")
Next cell
End Sub
```

- After clicking the
**Run**button, you will get an input box. - Input the number of decimal places you want to keep. We put 2.

- Click the
**OK**button in the input box and return to the worksheet.

**Read More: **How to Reduce Decimal Places Permanently in Excel

**Download the Practice Workbook**

## Rela**ted Articles**

- How to Add Decimals in Excel
- How to Change Decimal Places in Excel
- How to Change Decimal Separator in Excel
- How to Change 1000 Separator to 100 Separator 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**