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 + 9 is the shortcut key to Decrease Decimal in Excel.

Select the range of cells B2:B16 and Press ALT + H + 9 two times to round to the nearest dollar.

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 + 1 or Click the 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.

1. Select the range of cells D2:D16.
2. Press Ctrl + 1 to open the Format Cells dialog box.
3. 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 Enter. Drag the fill handle to the range F2:F16 to copy the formula.

Go to cell H2, type the formula =MROUND( D2, 1 ), and Press Enter. Drag the fill handle to the range 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 Enter. Drag the fill handle to the range 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 Enter. Drag the fill handle to the range 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.

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

3. Rename the table name PPData in the Power Pivot editor, select the Add column, type the formula =ROUND( PPData[Amount], 0 ), rename the column header Round and press Enter.

4. Select the Flattened PivotTable option in the PivotTable command.

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

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

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

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

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

7. 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 + F8, the keyboard shortcut to open the Macro dialog box, Choose the 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.

1. Go to Automate menu.
2. Select the Round Nearest Dollar Script – to open the Code editor on the right side of your Excel application.
3. 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!