# How to Round To the Nearest Dollar in Excel [9 Ways]

If you have received the data with decimals and round to the nearest numbers in Excel for further analysis. This article guides you to use Excel formats, functions, power pivot, power query, VBA, and Office Scripts to round to the nearest dollar value in Excel.

Sometimes you do not need to consider the decimals for your analysis. But the machine or computer program records the data with decimal points. You need to remove decimals from the data and present the whole number in your dashboard or reports. You will learn a few different methods to round to the nearest dollar in Excel.

This blog post covers how to round to the nearest dollar value using the Excel **Format Cells**, **Keyboard Shortcuts**, and Excel functions **ROUND**, **MROUND**,** FLOOR**,** CEILING**, and** TEXT**.

For multiple data files and large data sets, you can use the **Power Query** and **Power Pivot** methods. Also, you will learn the **VBA** and **Office Scripts **code to round to the nearest dollar in Excel for your automation or dashboard projects.

Suppose in the data file, the **Amount** are captured with two decimals. You want to remove the decimals in column **D** by rounding the nearest dollar for your analysis and reports.

Get your copy of the example workbook used in this blog post to follow along!

## Round Nearest Dollar in Excel with Number Format

You want to show whole numbers without decimal points in your report. You can just change it to display the number of decimal places. Use **Increase Decimal** or **Decrease Decimal** commands in the **Home** Tab, to adjust the number of decimal places.

Select the range of cells **D2:D16** and Click the **Decrease Decimal** command two times. Since you have two decimal points in the data.

Alternatively, you can use **Keyboard** **Shortcuts. **It allows you to navigate and perform actions within Excel without using a mouse. ` ALT` +

**+**

`H`**is the shortcut key to Decrease Decimal in Excel.**

`9`Select the range of cells **B2:B16** and Press ` ALT` +

**+**

`H`**two times to round to the nearest dollar.**

`9`## Round Nearest Dollar in Excel with Format Cells

**Format cells** can be used to customize the appearance of your data in Excel. To format the content in Excel you have to select the cell or range of cells. Press ` Ctrl` +

**or Click the**

`1`**Right mouse button**on the range of cells and choose the

**Format Cells…**option from the context menu.

Please follow the below steps to Round to the nearest dollar in Excel.

- Select the range of cells
**D2:D16**. - Press
+**Ctrl**to open the`1`**Format Cells**dialog box. - Select the
**Accounting**in Category, make it**Zero**to Decimal places and Press**OK**

## Round Nearest Dollar in Excel with ROUND and MROUND Functions

You can use the Excel functions **ROUND** or **MROUND** to round to the nearest dollar in Excel instead of using the format cells dialog box. The functions refer to the actual cell and round to the nearest dollar. It automatically updates when the actual data is changing.

`ROUND(number, num_digits)`

**ROUND** function rounds the cell value to a specified number of digits

`MROUND(number, multiple)`

**MROUND** function returns a number rounded to the preferred multiple.

Go to cell **F2**, type the formula ** =ROUND( D2, 0 )**, and Press

**. Drag the fill handle to the range**

`Enter`**F2:F16**to copy the formula.

Go to cell **H2**, type the formula ** =MROUND( D2, 1 )**, and Press

**. Drag the fill handle to the range**

`Enter`**H2:H16**to copy the formula.

**MROUND** function returns nearest 1 in the above formula, which rounds the nearest dollar.

## Round Nearest Dollar in Excel with Combination of functions

Excel allows you to use a combination of basic Excel functions to round to the nearest dollar. In this method, you explore the functions **FLOOR**,** CEILING**, **MOD**, and** IF.**

`FLOOR( number, significance )`

**FLOOR** returns a rounded-down number.

`CEILING( number, significance )`

**CEILING** returns a rounded-up number.

`MOD( number, divisor )`

**MOD** returns only the remainder value

Go to cell **E2**, type the formula ** =IF( MOD( D2, 1 ) < 0.5, FLOOR( D2, 1 ), CEILING( D2, 1 ) )**, and Press

**. Drag the fill handle to the range**

`Enter`**E2:E16**to copy the formula.

**MOD** function returns the remainder value. If the remainder value is less than **0.5**, the formula use the **FLOOR** function to round down the value else use the **CEILING** function to round up the value.

## Round Nearest Dollar in Excel with TEXT Function

In this method, you explore the **TEXT **and **VALUE** functions to round to the nearest dollar in Excel. It is used to format text, date, convert numbers to text, and change the case of text.

`TEXT( value, format_text )`

**TEXT** function format the given value and return a text

`VALUE( text )`

It converts a text that represents a number to a number

Go to cell **E2**, type the formula ** =VALUE( TEXT( D2, "0" ) )**, and Press

**. Drag the fill handle to the range**

`Enter`**E2:E16**to copy the formula.

**TEXT** function in the above formula rounds the nearest dollar and returns the number as a text. The **VALUE** function converts the text representing a number to a number.

## Round Nearest Dollar in Excel with Power Pivot

**Power Pivot** is an add-in that allows you to connect multiple data sources, create complex data models, create relationships between data tables, and perform complex calculations. Use the **ROUND** function in the power pivot to round to the nearest dollar in Excel.

Please follow the below steps to round to the nearest dollar.

- Select the range of cells
**A1:D16**in the Data Sheet - Go to
**Power Pivot**Menu, and press the command**Add to Data Model**to add the data to the power pivot.

- Rename the table name
**PPData**in the Power Pivot editor, select the**Add column**, type the formula, rename the column header`=ROUND( PPData[Amount], 0 )`

**Round**and press`Enter`.

- Select the
**Flattened PivotTable**option in the**PivotTable**command.

- You have options to select the New Worksheet or
**Existing worksheet**and select the worksheet location. Once you press the**OK**button, Excel adds an empty pivot table in the worksheet.

- You drag the fields
**Invoice No**,**Counter No**and the newly added column**Round**to the**Rows**Area.

## Round Nearest Dollar in Excel with Power Query

**Power Query** is an ETL data transformation tool. You can quickly format, clean and shape data using the Power Query user interface. Also, connect multiple data files, including databases, tables, and text files.

Steps to round to the nearest dollar in Excel using **Power Query**

- Select the range of cells
**A1:D16**in the**Data**Sheet - Choose
**From Table/Range**option in**Data**Tab – to open the Power Query window. - Rename the Query name
**PowerQuery**

- Select the
**Amount**Column. - Go to
**Transform**Tab, Select the**Whole Number**option in the**Data Type**drop down.

- Select the
**Close & Load**command and Press**Close & Load To… –**to popup**Import Data**dialog box.

- In the
**Import Data**dialog box, select the**Table**option, select**New worksheet**to insert the transformed Power Query table into a new worksheet, and Press**OK**.

## Round Nearest Dollar in Excel with VBA

**VBA** allows you to record repetitive tasks, create custom functions & formulas, automate processes, and create user entry forms. You use **For Each** loop to iterate the range of cells and the **Round** function to round to the nearest dollar value.

```
Sub RoundNearestDollar()
' Declare variables
Dim rge As Range
' Assign range of cells
Set rge = Sheets("VBA ").Range("D2:D16")
' loop through each cell in the range
For Each rg In rge
' Reset round nearest dollar using round function to the cell
rg.Value = Round(rg.Value, 0)
Next
End Sub
```

Insert the above code into your module.

Macro declares the variables rge. Assign the range of cells **D2:D16** to the variable **rge**. Loop through the range of cells. In each iteration, the **ROUND **function round to the nearest dollar value and reset the current cell value.

To run the **RoundNearestDollar **macro. Press ** Alt** +

**, the keyboard shortcut to open the Macro dialog box, Choose the**

`F8`**RoundNearestDollar**Macro and then Press

**Run**.

## Round Nearest Dollar in Excel with Office Script

You can record the repetitive task, automate, edit and run using **Office Scripts** in both desktop and web excel versions. You explore the Office Scripts **For** loop and **Math.round() **function to round to the nearest dollar in this section.

```
function main(workbook: ExcelScript.Workbook) {
// Assign the range of cells to the variable
let ws = workbook.getWorksheet("Office Scripts");
let rng = ws.getRange("D2:D16");
let rows = rng.getRowCount();
// loop through the range of cells
for (let i = 0; i < rows; i++) {
// get current cell value and store it in the variable cellValue
let cellValue = rng.getCell(i, 0).getValue() as number
// Round nearest dollar and reset to the current cell value
rng.getCell(i, 0).setValue(Math.round(cellValue));
}
}
```

Script assigns the range of cells **D2:D16** to the variable **rng**. For loop iterate each cell in the given range **rng. **Script stores the current cell value to the variable **cellValue, **and uses the function ** Math.round **to round to the nearest dollar and reset to the current cell value.

Follow the below steps to execute the **Round Nearest Dollar **Office script in Excel.

- Go to
**Automate**menu. - Select the
**Round Nearest Dollar**Script – to open the Code editor on the right side of your Excel application. - Press
**Run**

## Conclusions

Most of the time, the data are received with decimal points, which you may not need for analysis and round to the nearest dollar to present in your report. Displaying the whole numbers in your report will help the user to easily read, compare and calculate.

You learned how quickly to round to the nearest dollar using Excel **Format cells**. Format cells method is not changing the actual value in the cell, it changes only the display format in Excel**.** If you refer to this cell in the other formula, the non-round original value will be in your formula.

You refer to the actual cell and use in the Excel functions **ROUND**, **MROUND**, **TEXT** and Combination of functions methods. The formula automatically rounds the nearest dollar value when the actual data is changing.

**Power Pivot** and **Power Query** works well when dealing with multiple data sources or large data sets. **VBA** or **Offices Scripts** code helps you to add this functionality to your dashboard or report automation projects.

Did you know any other method to **Round to the Nearest Dollar** in Excel? Let me know in the comments below!