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

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

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

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.

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

Remove Decimals in Formula Bar.xlsm

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.

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

Get FREE Advanced Excel Exercises with Solutions!