In this Excel tutorial, you will learn how to remove decimals in the Excel formula bar.

Removing decimals in the Excel formula bar can done in several methods like enabling the **Set Precision as Displayed** option in the **File** => **Options** => **Advanced** section; by using Excel formulas to truncate the decimal values, then copying the formula results and pasting the results as **Values **and using the Excel VBA code snippet.

Sometimes you may need to remove some digits or all the digits after the decimal point. Whatever you do with your decimal number, only the display values in the cells change. The stored value (displayed in the formula bar) remains unchanged. By the end of this article, you will have complete knowledge of how to make the stored value and displayed value the same.

*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. Suppose you want to remove 1 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.

To remove decimals in the Excel formula bar, you can apply the following methods.

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

Enabling the **Set Precision as Displayed** option is the easiest method to make the display value and the stored value the same in Excel. But this method comes with a caution. Read more to know this.

We have a dataset like the following image. You see that display value and the stored value is different.

**Follow these steps to remove decimals in the formula bar:**

**Step 1**: Go to the **File** tab.

**Step 2**: Click on the **File** tab. You will find the **Options** command.

**Step 3**: Click the **Options** command, you will get the **Excel Options** dialog box. From the left-side pane, choose **Advanced** and if you scroll down, under the section **When calculating this workbook, **you will find an option **Set precision as displayed**.

**Step 4**: Choose the option **Set precision as displayed**. You will get a **caution notice** like this. This notice gives you a warning that the stored values in cells will permanently change to whatever format is displayed in the cells.

*Note**: As enabling the*

**Set precision as displayed**option permanently changes the stored value, your data may get increasingly inaccurate over time. Therefore, enable this option only if the displayed precision maintains the accuracy of your data.**Step 5**: Click the **OK** button in the notice. Then 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

## 2. Removing All Decimals from Formula Bar in Excel

Enabling the **Set precision as displayed** option permanently changes the stored value in cells of all worksheets. It creates problems when we require removing decimals from the cells of the active sheet or a specific range. We can apply Excel functions or VBA to remove decimals in such situations.

In this section, we will remove all decimals from the formula bar by rounding the numbers to integers. Learn the following 4 methods to remove all decimals in Excel formula bar.

### 2.1 Apply INT Function and Use Paste As Values Option

**The INT function** in Excel rounds down a number to its nearest integer. You can apply it by following the steps below:

**Step 1**: In cell **C5**, insert the following formula => press the **Enter** key => use the **Fill Handle** tool to copy the formula in the remaining cells.

`=INT(B5)`

*Note**: The*

**INT**function makes a negative number more negative by rounding it down.**Step 2**: Then, 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 process to copy the range.

**Step 3**: After that, select cell **B5** => click the **Paste** dropdown => you will get the **Values (V)** command under the **Paste Values** group.

**Step 4**: 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.### 2.2 Remove All Decimals Using CEILING Function and Paste As Values Option

**The CEILING function** rounds up a number to the nearest integers. You can apply this function if you require rounding up a number to zero decimals. Use the following steps:

**Step 1**: Insert the following formula into cell **C5** => press the **Enter** key => use the **Fill Handle** tool to copy the formula in the remaining cells.

`=CEILING(B5,1)`

*Note**: You can use the*

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

### 2.3 Use FLOOR Function to Remove All Decimals

**The FLOOR function** rounds down a number to the nearest integer. You can use the following steps to apply the **FLOOR** function to remove decimals.

**Step 1**: Type the following formula into cell **C5** => press the **Enter** key => use the **Fill Handle** tool to copy the formula in the remaining cells.

`=FLOOR(B5,1)`

*Note**: You can use the*

**FLOOR.MATH**function instead of the**FLOOR**function.**Step 2**: Copy the range **C5:C12** and paste as values in cell **B5**. As you can see, the decimals are now removed from the formula bar in Excel.

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

You can use the **VBA Int function** to remove all decimals from a number. Follow the steps below to remove all decimals using VBA.

**Step 1**: Go to the **Developer** tab, you will find the **Visual Basic** option.

**Step 2**: After clicking the **Visual Basic** option, the **Visual Basic Editor** window will open. If you click the **Insert** menu, you will find 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.*

**Step 3**: After selecting the **Module** option, **Module1** will appear. Insert the following code in the module => click the **Save** button >> 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
```

**Step 4**: After running the above VBA, all decimal places will be removed.

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

## 3. Removing Decimals To a Specific Place from Formula Bar

In the previous section, we removed all decimals from the formula bar in Excel. However, we can also keep a specific number of decimal places and remove the remaining decimals. In this section, we will remove decimals to a specific place in Excel formula bar by using Excel formulas and VBA.

### 3.1 Use 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.

Here, we will keep only 1 decimal place. Use the following steps to apply the **ROUND** function and **Paste as Values** command to remove decimals.

**Step 1**: Apply the following formula in cell **C5** => press the **Enter** key => drag down the **Fill Handle** icon.

`=ROUND(B5,1)`

**Step 2**: Then, 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 process to copy the range.

**Step 3**: After that, select cell **B5** => click the **Paste** dropdown => you will get the **Values (V)** command under the **Paste Values** group.

**Step 4**: 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.

**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**.### 3.2 Apply 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.

**Step 1**: Insert the following formula into cell **C5** => press the **Enter** key => use the **Fill Handle** tool to copy the formula in the remaining cells.

`=ROUNDUP(B5,1)`

**Step 2**: Copy the range **C5:C12** and paste as values in cell **B5**. You will get equal values for the stored number and displayed number in the range **B5:B12**.

### 3.3 Use ROUNDDOWN Function

**The ROUNDDOWN function**, on the other hand, always rounds down a number to a specific number of digits. Use the steps below to apply this function to rounding numbers and removing decimals.

**Step 1**: In cell **C5**, type in the following formula => press the **Enter** key => drag down the **Fill Handle** icon to copy the formula in the remaining cells.

`=ROUNDDOWN(B5,1)`

**Step 2**: Select the range **C5:C12** => copy it => then, paste it into cell **B5**. As you can see, the cell value and the displayed value in the formula bar are now equal.

### 3.4 Apply TRUNC Function

**The TRUNC function** in Excel keeps a specific number of decimal places by removing the rest of the decimals. Use the steps below to apply this function and paste as values command to remove decimals.

**Step 1**: Insert the following formula in cell **C5** => press the **Enter** key => drag down the **Fill Handle** icon.

`=TRUNC(B5,1)`

**Step 2**: Select the range **C5:C12** => copy it => paste it into cell **B5**. As you can see, the cell value and the formula bar value are now equal.

*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.### 3.5 Use VBA to Keep a Specific Number of Decimal Places

You can take input from a user and keep a specific number of decimals by using VBA. Follow the steps below to learn more.

**Step 1**: Follow the **first three steps of method 2.4**Â to insert a **Module** in **Visual Basic Editor** >> enter the following code in the module >> 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
```

**Step 2**: After clicking the **Run** button, you will get an input box like the following. Input the number of decimal places you want to keep. Here, I will input 2.

**Step 3**: After clicking the **OK** button in the input box, return to the worksheet. As you can see, all the numbers have only 2 decimal places.

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

## What Are the Things to Remember?

- Enabling the Set precision as displayed option permanently changes the stored value. Enable this option only if the displayed precision maintains the accuracy of your data.
- If the digit right after the rounding position is 5 or higher, the
**ROUND**function increases the rounding position by 1. Otherwise, it remains unchanged. - The
**CEILING**and**FLOOR**functions will always return integers by rounding up and rounding down respectively.

**Download Practice Workbook**

This ends our tutorial on how to remove decimals in Excel formula bar. I have shown how you can make the display value and stored value the same by enabling Excelâ€™s **Set precision as displayed **option from the **File => Options **pane. Our VBA codes can quickly remove all decimals or truncate specific decimal places as well. This tutorial also presented various Excel built-in functions and the Paste as Values command to remove decimals from the formula bar.

I hope this tutorial was helpful for you. Let us know your queries and feedback in the comment section.

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

**<< Go Back to Decimals in Excel | Number Format | Learn Excel**