How To Round to the Nearest Whole Number in Excel

275

When working with datasets in Excel, it is important to ensure consistency in the presentation of values. Different sources may provide data with varying levels of precision, leading to discrepancies in the length of values. To address this issue, you can use Excel’s rounding functions to standardize the values to a desired level of accuracy.

In this tutorial, I will demonstrate how to round values to the nearest whole number in Excel using different rounding methods. By following these steps and working with real-world datasets, you can improve the readability and clarity of your data analysis.

Using the Decimal Button in Excel

The Number commands block has the Increase Decimal and Decrease Decimal buttons. You can use these to show more or fewer digits after the decimal place.

So, you can use the Decrease Decimal button to reduce the number of decimal places until the underlying number transforms to the nearest integer.

This method is suitable for you if you’re a beginner-level Excel user working on small datasets. You’re okay with the manual adjustment of decimal digits using the Decrease Decimal button.

In the above dataset, you’ll find invoice amounts with different decimal places in column C.

To round these invoice amounts to the nearest whole number, select all the values in the column.

Now, go to the Number commands block in the Home tab and click on the Decrease Decimal button.

Excel will start reducing the digits after the decimal place.

If one of the values in the column contains only one digit after the decimal point, you’ll only need to click the button once.

Suppose, the minimum digits after the decimal point are five in a column, you’ll need to click the Decrease Decimal button five times.

Using the Built-In Number Formatting

You can also use the Number formatting tool to round the selected values to the nearest integer. The tool shows you a field where you can enter the number of digits you want after the decimal point. If you enter zero in this field, Excel transforms the values into whole numbers.

Here’s how to round in Excel using this technique:

Select the target values in a column or rows. In the current tutorial, it’s column C.

Press Ctrl + 1 to bring up the Format Cells dialog.

You should already be on the Number tab.

Click on the desired number-formatting conventions below the Category column in the left-side navigation panel.

Put a 0 inside the Decimal places field.

Click OK to apply the number formatting rule.

Excel transforms the selected fractional values to the nearest whole numbers.

Using Various Excel Functions

Find below various functions you can use to create formulas that’ll change fractional values to the nearest integer:

ROUND Function

The Excel ROUND function rounds a number to a specified number of digits. When rounding to the nearest whole number, you set the number of digits to 0. This function follows standard rounding rules, where numbers 0.5 and above are rounded up, and numbers below 0.5 are rounded down.

Let’s say you’d like to round the values in the column C and get outputs in column D of the dataset shown above.

Go to D2 and enter the following formula in it:

=ROUND(C2,0)

Hit Enter to calculate the cell.

Now, use the fill handle and drag it down until the cell D11 to apply the formula to the rest of the values in column C automatically.

INT Function

The INT function in Excel rounds a number down to the nearest integer, effectively truncating the decimal part. This means it always rounds towards zero, regardless of whether the decimal part is closer to the next whole number. If you’re wondering how to round down in Excel, use this function as shown below:

For the same dataset as shown previous, let’s transform the input values in column C to the nearest whole number in column D using the INT function.

In D2, enter the following formula:

=INT(C2)

Hit Enter to calculate the formula.

Use the fill handle to transform the rest of the values in column C into column D by copying the same formula.

Using a Custom Number Formatting Code

Suppose, the input invoice values of your dataset lack a currency symbol, like the $ sign. You’d like to add that to the values as well as convert the input values to whole numbers from decimals.

There could be other formatting requirements as well. In such a scenario, you can use the Custom formatting option available in the Format Cells dialog.

So, select the input values along a column or row.

Press Ctrl + 1 to get to the Format Cells tool.

Click on the Custom formatting option under the Category column.

Look for the required custom code from various built-in ones below the Type section.

Choose a formatting style that doesn’t include 0.00 at the end of a single custom code or between two custom codes.

To get the $ sign with no decimal places, select $#,##0.

If you don’t have this custom code, type the $#,##0 code inside the Type field.

Click OK to apply the customer number formatting.

You’ll get your currency sign as well as no decimal places after the invoice amounts in your dataset.

Again, this method is best suited for small datasets where you can visually reformat decimal values to integers using user interface tools.

Using Power Query Editor

If your input dataset for decimal to whole number conversion is gigantic and can’t be accommodated in an Excel worksheet, you can import the dataset to Power Query. Power Query can handle more rows than Excel can and hence is the go-to tool for transforming, cleaning, and rationalizing the dataset so you can import that to an Excel worksheet.

You can use Power Query Editor for both external and internal datasets.

To import a dataset to Power Query, go to the Data tab and click on the Get Data button. You can now hover the cursor over the From Database and choose from the available options, like From SQL Server Database, From Oracle Database, etc.

Suppose, your dataset is already in an Excel worksheet. To export that to Power Query, select the whole or part of the dataset you want to manipulate. Go to the Data tab and click on the From Table/Range button.

On the Create Table dialog, click OK.

Excel will export the dataset to the Power Query Editor. Select the column that contains the fractional invoice values.

Go to the Transform tab and find the Rounding drop-down menu inside the Number Column commands block. Click on Rounding and choose Round from the context menu.

You’ll now see the Round dialog. There, enter 0 inside the Decimal Places field and hit the OK button.

Power Query will instantly transform the decimal values to the nearest integers.

To export the dataset back to the originating Excel worksheet, click on the File tab and choose Close and Load To.

You’ll now see the Import Data dialog box. Click on the Existing worksheet radio button and select a cell range on the worksheet where you want the dataset to be imported.

You can now select the previous dataset, right-click, and select Delete on the context menu to get rid of untransformed data.

There you go! You’ve successfully transformed the fractional dataset to whole numbers using Power Query.

Using Excel VBA

So far, you’ve learned all the manual and semi-automatic solutions to round to the nearest whole number in Excel. If you want to learn a few simple automated hacks, try Excel VBA macros.

In this section, I’ve mentioned some common scenarios to convert decimals to integers using automated VBA scripts.

Before you get started with creating VBA macros with these codes, read this article to learn how to create a macro using a VBA script:

These macros will show input boxes with text explanations so you can enter inputs accordingly.

Rounding in Excel to the Nearest Whole Number

Thsi script will help you round to the nearest integer with interactive instructions:

Sub RoundToNearestWholeNumber()
    Dim targetRange As Range
    Dim numFormat As String
    Dim currencySymbol As String
    Dim cell As Range
    
    ' Prompt for target dataset
    On Error Resume Next
    Set targetRange = Application.InputBox("Select the target dataset:", Type:=8)
    On Error GoTo 0
    
    If targetRange Is Nothing Then
        MsgBox "No range selected. Exiting."
        Exit Sub
    End If
    
    ' Prompt for number formatting (currency or general)
    numFormat = InputBox("Enter number formatting (currency or general):")
    
    ' Prompt for currency symbol (if applicable)
    If LCase(numFormat) = "currency" Then
        currencySymbol = InputBox("Enter currency symbol (e.g., USD, GBP, etc.):")
    End If
    
    ' Round each cell value to the nearest whole number
    For Each cell In targetRange
        cell.Value = Round(cell.Value)
        If LCase(numFormat) = "currency" Then
            cell.NumberFormat = "$#,##0" ' Set to currency format
        Else
            cell.NumberFormat = "General"
        End If
    Next cell
    
    ' Show dialog when done
    MsgBox "Rounding completed!"
End Sub

To execute the macro, press Alt + F8 to show the Macro dialog box.

Select the macro RoundToNearestWholeNumber and hit Run.

Enter the dataset for transformation using the mouse.

Choose between Currency or General number formatting.

You also need to pick the currency symbol from USD and GBP.

Now, you should see that you’ve successfully rounded to the nearest integer using a VBA script.

Round Up / Down in Excel to the Nearest Whole Number

Find below another cool VBA script that’ll let you round up or down when transforming decimal values to the nearest integer:

Sub RoundToNearestWholeNumber()
    Dim targetRange As Range
    Dim numFormat As String
    Dim currencySymbol As String
    Dim cell As Range
    Dim roundUp As Boolean
    
    ' Prompt for target dataset
    On Error Resume Next
    Set targetRange = Application.InputBox("Select the target dataset:", Type:=8)
    On Error GoTo 0
    
    If targetRange Is Nothing Then
        MsgBox "No range selected. Exiting."
        Exit Sub
    End If
    
    ' Prompt for number formatting (currency or general)
    numFormat = InputBox("Enter number formatting (currency or general):")
    
    ' Prompt for currency symbol (if applicable)
    If LCase(numFormat) = "currency" Then
        currencySymbol = InputBox("Enter currency symbol (e.g., USD, GBP, etc.):")
    End If
    
    ' Prompt for rounding direction
    roundUp = MsgBox("Do you want to round up? Click Yes for rounding up, No for rounding down.", vbQuestion + vbYesNo) = vbYes
    
    ' Round each cell value to the nearest whole number
    For Each cell In targetRange
        If roundUp Then
            cell.Value = WorksheetFunction.Ceiling(cell.Value, 1)
        Else
            cell.Value = WorksheetFunction.Floor(cell.Value, 1)
        End If
        
        If LCase(numFormat) = "currency" Then
            cell.NumberFormat = "$#,##0" ' Set to currency format
        Else
            cell.NumberFormat = "General"
        End If
    Next cell
    
    ' Show dialog when done
    MsgBox "Rounding completed!"
End Sub

Upon execution, the VBA macro will show the following input boxes:

• Choose dataset from worksheet using mouse.
• Select between Currency and General number formatting.
• Pick a symbol for currency formatting.

• Choose whether you want to round down or up.

After successful execution of the script, you get the rounded down or up values.

Round in Excel to the Nearest Even / Odd Number

Need to round to the nearest odd or even whole numbers? Use this script to create a VBA macro:

Sub RoundToNearestOddOrEven()
    Dim targetRange As Range
    Dim numFormat As String
    Dim currencySymbol As String
    Dim cell As Range
    Dim roundToOdd As Boolean
    
    ' Prompt for target dataset
    On Error Resume Next
    Set targetRange = Application.InputBox("Select the target dataset:", Type:=8)
    On Error GoTo 0
    
    If targetRange Is Nothing Then
        MsgBox "No range selected. Exiting."
        Exit Sub
    End If
    
    ' Prompt for number formatting (currency or general)
    numFormat = InputBox("Enter number formatting (currency or general):")
    
    ' Prompt for currency symbol (if applicable)
    If LCase(numFormat) = "currency" Then
        currencySymbol = InputBox("Enter currency symbol (e.g., USD, GBP, etc.):")
    End If
    
    ' Prompt for rounding to odd or even
    roundToOdd = MsgBox("Do you want to round to odd numbers? Click Yes for odd, No for even.", vbQuestion + vbYesNo) = vbYes
    
    ' Round each cell value to the nearest whole number
    For Each cell In targetRange
        If roundToOdd Then
            cell.Value = WorksheetFunction.Ceiling(cell.Value, 2) - 1
        Else
            cell.Value = WorksheetFunction.Floor(cell.Value, 2)
        End If
        
        If LCase(numFormat) = "currency" Then
            cell.NumberFormat = "$#,##0" ' Set to currency format
        Else
            cell.NumberFormat = "General"
        End If
    Next cell
    
    ' Show dialog when done
    MsgBox "Rounding completed!"
End Sub

You’ll get a dialog box to choose between the nearest odd or even whole number when execute this macro.

After you enter all the required data in the input boxes correctly, Excel transforms your input dataset instantly.

Conclusions

You probably have a good grasp on how to round numbers to the nearest whole number in Excel after going through the methods outlined above.

I have shared various methods for rounding in Excel, ranging from the most straightforward solution to function-based approaches and techniques within the Excel user interface. Additionally, you can discover a Power Query-based solution for handling large databases that exceed the capacity of an Excel worksheet.