To round a number to the nearest ten in Excel, use the ROUND function with -1 as its second argument: with the number in A2, enter =ROUND(A2,-1). For example, 124 becomes 120 and 125 becomes 130; the formula returns a rounded number you can use in later calculations.
The quickest way to round to the nearest ten
Excel’s ROUND function takes a number and the number of digits to round to. A positive digit count rounds to places to the right of the decimal point, zero rounds to the nearest whole number, and a negative digit count rounds to places to the left. Because the tens place is one place left of the ones place, use -1 for the nearest ten.
With the original value in A2, enter =ROUND(A2,-1) in another cell. If A2 contains 138, the result is 140. If A2 contains 132, the result is 130. The source value remains in A2; Excel puts the calculated result in the cell containing the formula.
To apply the formula to a column, select the cell containing it and drag the small fill handle at the lower-right corner down through the rows. Excel adjusts the reference for each row, so a formula based on A2 becomes one based on A3, then A4, and so on. You can also copy the formula cell, select the destination cells, and paste.
How Excel decides which ten is nearest
Rounding to the nearest ten divides the number line into intervals that are ten units wide. Values below the midpoint go to the lower multiple of ten, and values at or above the midpoint go to the higher multiple. For example, values from 120 up to but not including 125 round to 120; values from 125 up to but not including 135 round to 130.
Excel’s ROUND function rounds halfway cases away from zero. For positive values, that means a final 5 at the rounding boundary goes up: 125 becomes 130. For negative values, it means the result moves farther below zero: -125 becomes -130. This is sometimes described as conventional half-away-from-zero rounding. It is not the same as “round half to even,” which some other systems use.
Digits after the decimal point count too. With a value of 124.9 in A2, the nearest ten is 120, while 125.0 is exactly halfway and rounds to 130. The same formula handles whole numbers, decimals, and negative values; you do not need to remove decimal places first.
Worked examples for positive and negative values
These examples assume the original number is in A2. Enter the formula in a different cell to keep the input available for comparison:
- With 124 in A2,
=ROUND(A2,-1)returns 120. - With 125 in A2,
=ROUND(A2,-1)returns 130. - With 146 in A2,
=ROUND(A2,-1)returns 150. - With -124 in A2,
=ROUND(A2,-1)returns -120. - With -125 in A2,
=ROUND(A2,-1)returns -130.
The negative examples can feel counterintuitive if “up” is taken to mean moving toward a larger number. Excel’s midpoint rule is about moving away from zero: -125 is halfway between -120 and -130, and -130 is farther from zero. For non-midpoint values, Excel selects whichever multiple of ten is numerically closest.
To verify a result, compare the distance to each neighboring multiple. For 146, the distance to 150 is 4 and the distance to 140 is 6, so 150 is closer. At 145, the distances are equal, and Excel’s midpoint rule returns 150.
Using MROUND as an alternative
You can also use MROUND to round to a specified multiple. It can be useful when rounding to a multiple other than ten, such as five or twenty-five. With a positive number in A2, enter =MROUND(A2,10) to round to the nearest multiple of ten. If A2 contains 146, the result is 150.
For this particular task, ROUND(A2,-1) is usually the clearest choice because the negative digit count directly expresses rounding to the tens place. MROUND expresses the interval instead: its second argument is the multiple to use. Both methods round halfway cases away from zero when the arguments have compatible signs.
There is an important sign difference: Excel’s MROUND requires the number and the multiple to have the same sign, or one of them to be zero. A negative number paired with a positive multiple, such as -146 and 10, can produce #NUM!. The ROUND method avoids that issue and works naturally for negative numbers.
Round up or down instead of to the nearest ten
Sometimes “round to the nearest ten” is not the actual requirement. A report may need every value rounded upward, or a quantity may need to be rounded down to avoid exceeding a limit. Excel has separate functions for those rules. With a value in A2, =ROUNDUP(A2,-1) rounds away from zero to a tens boundary; for 121 it returns 130.
With the value in A2, =ROUNDDOWN(A2,-1) rounds toward zero to a tens boundary; for 129 it returns 120. For negative inputs, rounding down with this function also means toward zero: -129 becomes -120. That differs from the everyday meaning of “round down” as always moving toward a smaller number, so check the intended rule before choosing the function.
Use ROUND when the closest ten is wanted, ROUNDUP when the result must move away from zero, and ROUNDDOWN when it must move toward zero. These functions change the numeric result; they are not merely display settings.
Formula results versus number formatting
Rounding with a formula changes the value returned by the formula. If A2 contains 127 and B2 contains =ROUND(A2,-1), B2 contains the numeric result 130. A calculation using B2 will use 130, while a calculation using A2 will still use 127.
Changing a cell’s number format is different. Formatting can control how many decimal places Excel displays, but it does not generally replace the underlying value with the nearest ten. A cell might display fewer decimal places while still retaining its original value for calculations. If later totals or comparisons must use rounded tens, calculate the rounded values with a formula and reference those results.
For a permanent replacement, copy the formula results and use Paste Special > Values to replace formulas with their current results. This removes the formulas, so keep a copy of the original data if you may need to revise the rounding rule or audit the inputs later.
Common problems and how to fix them
- The result is a decimal or a whole number, not a ten. Check the second argument. Use -1 for tens, -2 for hundreds, and 1 for one decimal place.
- The formula appears as text. The cell may be formatted as Text, or the formula may have a leading apostrophe. Change the cell format to General, then edit and re-enter the formula.
- Excel shows a formula error after pasting. Check that the cell reference exists and that the formula has balanced parentheses. In some regional settings, Excel uses a semicolon instead of a comma between function arguments; use the separator expected by your Excel installation.
- The displayed input looks slightly different from the result. A cell’s displayed value may be shortened by its number format. Check the underlying value in the formula bar when a boundary result seems unexpected.
- Only one row rounds correctly. When copying down, make sure the formula refers to the matching row. A reference such as A2 adjusts as it is filled down; a reference with dollar signs, such as $A$2, stays fixed.
One further check is to distinguish numeric values from text that looks like a number. Imported data can contain numbers stored as text, which may behave differently in calculations. If Excel does not calculate as expected, inspect the cell and convert the input to a real number before relying on the rounded result.
Choosing the right method for your worksheet
For most lists, place the original values in one column and rounded values in another. This keeps the raw data intact and makes the rounding rule visible. It is especially helpful when inputs may change, because the formula recalculates automatically.
Choose the function based on the question your worksheet needs to answer:
- Use
ROUND(value,-1)for the nearest ten. - Use
MROUND(value,10)when expressing the desired multiple is more useful, and the number and multiple have compatible signs. - Use
ROUNDUP(value,-1)to round away from zero. - Use
ROUNDDOWN(value,-1)to round toward zero.
For summaries, keep the original data if exact totals matter. Rounding each row and then adding those rounded results can produce a different total from adding the original values and rounding the final sum. For example, repeated small differences can accumulate. Decide whether the task calls for rounding each entry or rounding only the final total, then apply the formula at that stage.
Frequently Asked Questions
What is the Excel formula to round to the nearest 10?
With the number in A2, use =ROUND(A2,-1). The -1 tells Excel to round one place to the left of the decimal point, which is the tens place.
How do I round a whole column to the nearest ten?
Enter the formula in the first result row, then drag its fill handle down the column or copy and paste it into the remaining rows. Excel adjusts relative references to use the corresponding input row.
Does 125 round to 120 or 130 in Excel?
It rounds to 130. Excel’s ROUND function sends halfway values away from zero, so 125 rounds upward to 130 and -125 rounds to -130.
How do I round to the nearest hundred instead?
Use -2 as the second argument. With a number in A2, =ROUND(A2,-2) rounds it to the nearest hundred; for example, 1,249 becomes 1,200 and 1,250 becomes 1,300.
Why does MROUND return #NUM! for a negative value?MROUND requires its number and multiple arguments to have the same sign, unless one is zero. For negative inputs, use ROUND(A2,-1) for the nearest ten, or provide a negative multiple to MROUND.
Related
- Excel Weekday Text: Show Dates as Day Names
- Excel WEEKDAY: Return a Day Name from a Date
- Excel NOW as Text: Show the Current Date and Time
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Replace an Excel Cell Value: Methods, Formulas, and TipsTo replace a value in one Excel cell, select the cell, type the new value, and press Enter;…
- COUNTIF Excel for Specific Text: Examples and TipsTo count cells that match specific text in Excel, use =COUNTIF(A2:A10,"Apple"); to count cells that contain the text…
- Excel IRR Guess: How It Works and When to Change the ValueIn Excel, the optional guess argument in the IRR function is an initial estimate of the rate of…
- Excel Cell Value From List: Find or Select a MatchTo return a value from a list when you know a matching item, use XLOOKUP; with product IDs…
- Excel Formula to Split Text by Delimiter: How It WorksIn Microsoft 365 for Windows, split text with =TEXTSPLIT(A2,","), replacing A2 with the cell that contains your text…
- How to Copy a Formula in Excel All the Way Down a ColumnTo copy a formula all the way down in Excel, select the cell with the formula and double-click…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.