6 Ways To Round to the Nearest Whole Number in Microsoft Excel

Learn how to round to the nearest whole number in Excel to present your datasets, tables, etc., with simplicity and consistency.

When you import datasets from different sources they come in different levels of accuracy. Some databases might store values up to 10 decimal places while others store values up to 3 decimal places. When you combine such data in one place, you’ll see a disparity in the length of the values which decreases data readability and comprehension.

In this case, you can round in Excel to transform values of carrying decimal places to a uniform standard that your workplace or school allows.

Today, I’ll show you with steps and real-world datasets how to round up and round down in Excel to the nearest whole number using various methods of rounding in Excel.

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.

A sample dataset of invoices
A sample dataset of invoices

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

Using the Decrease Decimal button
Using the Decrease Decimal button

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.

Rounded to nearest whole number using Decrease Decimal
Rounded to nearest whole number using Decrease Decimal

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:

Use Format Cells Currency formatting
Use Format Cells Currency formatting

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.

Rounded to the nearest whole number using Number formatting
Rounded to the nearest whole number using Number formatting

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.

Sample dataset

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.

ROUND Function

Go to D2 and enter the following formula in it:

=ROUND(C2,0)
Applied ROUND function

Hit Enter to calculate the cell.

Use fill handle to copy and paste formulas

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:

The INT formula

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)
Applying INT

Hit Enter to calculate the formula.

Using fill handle to apply 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.

Format Cells Custom category
Format Cells Custom category

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.

Applying a custom code
Applying a custom code

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.

Converted to integers using a custom code
Converted to integers using a custom code

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.

From Oracle database
From Oracle database

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.

From Table or Range
From Table or Range

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.

Select values on Power Query
Select values on Power Query

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

Open Round from Transform tab
Open Round from Transform tab

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.

No decimal places

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

Rounded to the nearest whole number on Power Query
Rounded to the nearest whole number on Power Query

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

Close and load to
Close and load to

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

Import Data dialog
Import Data dialog

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.

Delete old dataset
Delete old dataset

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

Transformed data on Power Query
Transformed data on Power Query

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:

📒 Read More: How To Use The VBA Code You Find Online

Did you go through the above Excel tutorial? You’re all set to create awesome macros with these scripts. I’ve written the scripts in a way that Excel VBA guides you through the whole process from the step when you execute the macro.

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:

VBA script sample 1
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
Run a macro

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

Select the macro RoundToNearestWholeNumber and hit Run.

Choose range

Enter the dataset for transformation using the mouse.

Choose currency or general

Choose between Currency or General number formatting.

Choose USD symbol

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

Rounded using a VBA script

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:

VBA script 2
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.
Yes no choose
  • Choose whether you want to round down or up.
Rounding down in Excel

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:

VBA script 3
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
Choose odd yes

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

Rounded to odd whole numbers

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

Conclusions

I’m confident you’ve learned how to round to the nearest whole number in Excel by following along with the methods explained above.

I’ve presented a mix of methods for rounding in Excel that includes the simplest solution, function-based approach, and Excel user interface techniques. Here, you’ll also find a Power Query-based solution that helps you manage a gigantic database that won’t fit in an Excel worksheet.

Finally, you’ve learned highly automated and convenient ways to round up and down in Excel to the nearest whole number using simple Excel VBA macros.

About the Author

Bipasha Nath

Bipasha Nath

Bipasha is a technical content writer with 10+ years of experience in the technology industry. She previously worked in a SaaS software development company focused on SharePoint, OneDrive, Word, Excel, Word solutions.

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃