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.

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:

ðŸ“’ **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:

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

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

## 0 Comments