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