To round a number up to the next whole dollar in Excel, use the ROUNDUP function with 0 decimal places, like =ROUNDUP(A1,0). This always rounds away from zero, so $12.01 becomes $13, and $12.00 stays $12. For currency formatting, apply the Accounting or Currency number format after rounding.
Why Round Up to the Next Dollar?
Rounding up to the next dollar is common in accounting, inventory pricing, and donation tracking. For example, a store might round a subtotal of $19.25 up to $20.00 to simplify cash transactions, or a charity might round each donation up to the nearest dollar to increase total contributions. Excel’s ROUNDUP function is the direct tool for this, but there are several other methods depending on whether you want to change the cell value or just display it differently.
Using ROUNDUP: The Core Method
The ROUNDUP function rounds a number up, away from zero, to a specified number of digits. The syntax is =ROUNDUP(number, num_digits). When num_digits is 0, it rounds to the nearest whole number, always upward. For example, with value 12.01 in cell A1:
=ROUNDUP(A1,0)returns 13=ROUNDUP(12.00,0)returns 12 (because it’s already a whole number)=ROUNDUP(-12.01,0)returns -13 (rounds away from zero)
This function works in all versions of Excel, including Excel for Microsoft 365, Excel 2021, and older versions. On Mac, the same formula works identically.
Using CEILING for Multiples or Currency
If you need to round up to the next dollar but also want to control the multiple (e.g., round up to the next $5 or $10), use the CEILING function. Syntax: =CEILING(number, significance). For rounding to the next dollar, set significance to 1:
With value 12.01 in A1, =CEILING(A1,1) returns 13. To round up to the next $5, use =CEILING(A1,5), which returns 15 for 12.01. Note that CEILING also rounds away from zero, so negative numbers round to more negative multiples. For example, =CEILING(-12.01,1) returns -13.
Display Only: Using Number Formatting
Sometimes you don’t want to change the underlying value, just show it as a whole dollar. Use the number format to round for display only. Select the cells, then press Ctrl+1 (Windows) or Cmd+1 (Mac) to open Format Cells. Go to the Number tab, choose “Currency” or “Accounting”, and set Decimal places to 0. This will display 12.01 as $12, not $13—it rounds to the nearest dollar, not up. To force display rounding up, you cannot use number formatting alone; you must use a formula like =ROUNDUP(A1,0) in a helper column.
Common Mistakes and Misconceptions
A frequent error is using ROUND instead of ROUNDUP. ROUND rounds to the nearest value, so 12.50 becomes 13, but 12.49 becomes 12. If you need to always round up, ROUNDUP is the correct function. Another mistake is forgetting that ROUNDUP with negative num_digits rounds to tens, hundreds, etc. For example, =ROUNDUP(12, -1) returns 20, not 13. Also, be aware that ROUNDUP rounds negative numbers away from zero, which may not be what you expect in financial contexts where negative numbers represent refunds. In such cases, consider using IF to handle signs separately.
Comparing ROUNDUP, CEILING, and MROUND
Three functions can round up, but they differ in behavior:
ROUNDUProunds away from zero to a given number of digits. Good for simple whole-dollar rounding.CEILINGrounds up to a specified multiple. Good for rounding to $5, $10, or other increments.MROUNDrounds to the nearest multiple (not always up). For example,=MROUND(12.01,1)returns 12, not 13, because it rounds to the nearest whole number. SoMROUNDis not suitable for always rounding up.
For most cases, ROUNDUP is the simplest and most direct answer.
Real-World Example: Donation Tracker
Suppose you have a list of donations in column A (A2:A10) and you want to round each donation up to the next dollar for a fundraising report. In cell B2, enter =ROUNDUP(A2,0) and drag down. If A2 contains 25.01, B2 shows 26. If A3 contains 30.00, B3 shows 30. This gives you a column of whole-dollar amounts that overstate the actual donations slightly, which is fine for estimating totals.
Frequently Asked Questions
How do I round up to the next dollar in Excel with a formula?
Use =ROUNDUP(A1,0) where A1 contains the number. This always rounds up to the nearest whole dollar.
What is the difference between ROUNDUP and CEILING?ROUNDUP rounds to a specified number of decimal places, while CEILING rounds to a specified multiple. For whole dollars, both work, but CEILING allows multiples like 5 or 10.
Can I round up without changing the original value?
Yes, use a helper column with the formula, or use number formatting to display rounded, but formatting only rounds to nearest, not up. To display rounded up without changing the value, you must use a formula in another cell.
Does ROUNDUP work for negative numbers?
Yes, but it rounds away from zero. For example, =ROUNDUP(-12.01,0) returns -13. If you need to round toward zero for negatives, combine with IF and ROUND.
How do I round up to the next dollar in Google Sheets?
The same functions work: =ROUNDUP(A1,0) and =CEILING(A1,1). The syntax is identical to Excel.
Why is my ROUNDUP formula giving a decimal like 13.0?
That’s just the number format. If you see 13.0, change the cell format to Number with 0 decimal places, or use the Currency format.
Related
- Excel Lookup Min Value Based on Criteria
- Free Excel Loan Amortization Template: Get One Now
- Excel Learning Center Wilmington NC: Classes & Training
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Length of Service in Years and Months: FormulasTo calculate completed length of service in years and remaining months, enter the start date in A2 and…
- Excel Built-In String Functions and How to Use ThemExcel has built-in string functions—usually called text functions—that let you extract, search, clean, change, combine, and split text…
- Excel INDIRECT Function: Reference Another SheetYes. In Excel, INDIRECT can turn a text string containing a worksheet reference into a usable reference. For…
- Do Excel Merged Cells Need to Be the Same Size to Sort?Yes. When you sort a range in Excel, any merged cells included in that range must have the…
- Excel Formula for Checking Dates: Validity and RangesIf the date is in A2, use =ISNUMBER(A2) to check whether Excel stores it as a numeric value;…
- Calculate Weekdays Between Dates in Excel: FormulasTo calculate weekdays between two dates in Excel, use =NETWORKDAYS(A2,B2), where A2 contains the start date and B2…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.