How to Remove Decimals in Excel Formula Bar (3 Methods)

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.

VBA to remove decimals in Excel formula bar based on user input

Click the image for a detailed view

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