To round a number to the nearest dollar in Excel, use the =ROUND(cell, 0) formula. For example, =ROUND(A1, 0) turns 12.50 into 13 and 12.49 into 12. If you need to round up or down to the nearest dollar, use =ROUNDUP(cell, 0) or =ROUNDDOWN(cell, 0).
Why Round to Dollars in Excel?
Rounding to whole dollars is common in financial reports, invoices, and budgets where cents are irrelevant or where currency rules require whole-dollar amounts. Excel gives you several ways to do this, and the right choice depends on whether you want standard rounding, always-up, always-down, or a display-only change. Understanding the difference between formatting and actual rounding is key—formatting changes how a number looks, while rounding changes the underlying value.
The Core Formulas: ROUND, ROUNDUP, ROUNDDOWN
Excel’s three primary rounding functions all take two arguments: the number and the number of digits. To round to dollars, set the second argument to 0.
=ROUND(A1, 0)– Rounds to nearest dollar (standard rounding: .5 rounds up).=ROUNDUP(A1, 0)– Always rounds up to the next dollar.=ROUNDDOWN(A1, 0)– Always rounds down to the previous dollar.
Example: With 12.50 in cell A1, =ROUND(A1, 0) returns 13, =ROUNDUP(A1, 0) returns 13, and =ROUNDDOWN(A1, 0) returns 12. With 12.49, ROUND returns 12, ROUNDUP returns 13, and ROUNDDOWN returns 12.
These functions work in Excel for Microsoft 365, Excel 2021, Excel 2019, and older versions. They also work in Google Sheets, though the menu paths differ.
Display-Only Rounding: Number Formatting
If you want to show whole dollars without changing the actual value—for example, when you still need cents for calculations—use number formatting. Select the cells, press Ctrl+1 (Windows) or Cmd+1 (Mac) to open Format Cells, then choose Number and set Decimal places to 0. Alternatively, use the ribbon: Home > Number > Decrease Decimal.
This method only changes the display; the underlying value remains 12.50. If you later use that cell in a formula, Excel will use the full 12.50, not the displayed 13. This is a common source of confusion, so decide whether you need display-only or actual rounding.
Rounding with MROUND and CEILING/FLOOR
For more control, use MROUND to round to a multiple of any number. To round to the nearest dollar, =MROUND(A1, 1) is equivalent to =ROUND(A1, 0). But MROUND is useful if you need to round to, say, $5 or $10 increments.
CEILING and FLOOR round up or down to a specified multiple. =CEILING(A1, 1) rounds up to the nearest dollar, and =FLOOR(A1, 1) rounds down. These functions are helpful when you need to round to a currency unit like a dollar bill.
Example: With 12.34 in A1, =MROUND(A1, 1) returns 12, =CEILING(A1, 1) returns 13, and =FLOOR(A1, 1) returns 12.
How to Choose the Right Method
Ask yourself two questions:
- Do I need the actual value changed? If yes, use a formula like ROUND. If no, use number formatting.
- What rounding behavior do I need? Standard rounding (ROUND) is typical for financial reporting. ROUNDUP is used when you must never undercharge, such as calculating tax or shipping. ROUNDDOWN is used when you must never overcharge.
For example, an invoice that must collect at least the calculated amount might use ROUNDUP. A discount calculation might use ROUNDDOWN to avoid giving too much off.
Common Problems and Misconceptions
Problem: The formula seems to round incorrectly. This often happens because the cell is formatted to show fewer decimals, but the actual value still has cents. Check the formula bar to see the real value. If you see 12.50 but the formula returns 12, the cell might be formatted to display 12.50, but the underlying value is 12.49.
Problem: ROUNDUP rounds negative numbers in the wrong direction. ROUNDUP always rounds away from zero. So =ROUNDUP(-1.2, 0) returns -2, not -1. If you need to round toward zero (i.e., -1.2 to -1), use =ROUNDDOWN(-1.2, 0) or use MROUND with a negative multiple. For example, =MROUND(-1.2, 1) returns -1.
Misconception: The ROUND function always rounds .5 up. Actually, Excel uses standard rounding where .5 rounds away from zero. So 0.5 becomes 1, but -0.5 becomes -1. This is consistent with most spreadsheet applications.
Rounding in Financial Calculations
When working with money, rounding can affect totals. For example, if you have a list of prices in A2:A10 and you want to round each to the nearest dollar before summing, use =SUM(ROUND(A2:A10, 0)) as an array formula (press Ctrl+Shift+Enter in older Excel, or just Enter in Excel 365). This is different from rounding the sum itself: =ROUND(SUM(A2:A10), 0) rounds only the final total.
Example: With 1.20, 2.30, and 3.40 in A2:A4, =SUM(ROUND(A2:A4, 0)) returns 1+2+3 = 6, while =ROUND(SUM(A2:A4), 0) returns ROUND(6.90, 0) = 7. The difference matters for accurate reporting.
For currency, consider using the ROUND function on each line item before summing to avoid cumulative rounding errors. This is a common practice in accounting.
Alternatives: INT, TRUNC, and Text Functions
Other functions can round to whole numbers but with different behavior:
=INT(A1)rounds down to the nearest integer. For positive numbers, it’s the same as ROUNDDOWN. For negative numbers, INT rounds toward zero? No, INT rounds down, so=INT(-1.2)returns -2.=TRUNC(A1, 0)truncates the decimal part, effectively rounding toward zero.=TRUNC(-1.2, 0)returns -1.=TEXT(A1, "0")converts the number to text with no decimals, but the result is text, not a number. Avoid this unless you need text output.
For most dollar rounding, ROUND, ROUNDUP, and ROUNDDOWN are sufficient. TRUNC is useful when you need to remove cents without rounding, regardless of sign.
Frequently Asked Questions
How do I round to the nearest dollar in Excel without a formula? Use number formatting: select the cells, press Ctrl+1, choose Number, and set Decimal places to 0. This changes the display only; the underlying value stays the same.
Why does my ROUND formula sometimes show 12.00 instead of 12? The cell might be formatted to show two decimal places. After using ROUND, change the number format to General or Number with 0 decimals to display 12, not 12.00.
Can I round to the nearest dollar in Google Sheets the same way? Yes, the ROUND, ROUNDUP, and ROUNDDOWN functions work identically in Google Sheets. The formatting menu is different: Format > Number > Number, then set decimal places to 0.
What is the difference between ROUND and MROUND? ROUND rounds to a specified number of digits (0 for dollars). MROUND rounds to a specified multiple, so =MROUND(A1, 1) is the same as =ROUND(A1, 0) for positive numbers. MROUND is more flexible for non-dollar multiples.
How do I round a formula result to dollars without changing the original data? Wrap the formula with ROUND. For example, if you have =A1*B1 in C1, change it to =ROUND(A1*B1, 0). This rounds the result of the calculation, leaving A1 and B1 unchanged.
Does Excel round .5 up or down? Excel rounds .5 away from zero. So 2.5 becomes 3, and -2.5 becomes -3. This is standard rounding in most spreadsheet applications.
Related
- Excel Just Numbers From a Cell? How to Extract Text
- Excel Learning Center Jacksonville NC: Classes & Training
- Excel Inventory Tracking Template Walkthrough
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Filter Multiple Criteria: AND, OR, and ExamplesYes. In Excel, use a column’s filter checklist to show several selected values (OR), or apply filters to…
- Excel VAT Formula at 20%: Add or Remove VATIf A2 contains a price before VAT, calculate VAT at 20% with =A2*20%; add that VAT to the…
- Excel Formula for Removing Spaces: TRIM and MoreIn Excel, use =TRIM(A2) to remove leading and trailing spaces and reduce repeated spaces between words to one.…
- Excel Formula to Get a Distinct List (With Examples)In Excel for Microsoft 365, use =UNIQUE(A2:A20) to return a distinct list from the values in A2:A20, with…
- Capitalize Each Word in Excel: Shortcut and FormulaExcel does not have a single keyboard shortcut that changes selected text to Proper Case. To capitalize the…
- Paste Values in Excel: Keep Results, Remove FormulasTo get paste values in Excel, copy the cells, select where the results should go, then choose Home…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.