How to Round Up to the Nearest 10 in Excel

Excel's math functions include several options for rounding numbers, including ROUND, ROUNDUP, MROUND and CEILING, each of which differs slightly in purpose and syntax. For rounding up to the nearest 10, CEILING works best, whereas MROUND can round to the nearest 10 in either direction. ROUND and ROUNDUP work better for rounding off decimals, but they can also round to the nearest 10 through a workaround. You can use any of these functions individually to round a single number, or in conjunction with cell references to round an entire set of existing data.

Excel's CEILING function always rounds up to a specified value, making it the best way to round up to the nearest 10. The formula for CEILING requires two variables: the original number and how far to round it. For example, to round the number 12 up to 20, enter "=CEILING(12, 10)" (without quotes) into a cell. No matter what number you want to round, change only the first variable in the formula. The second, "10," tells Excel to round up to the nearest 10, so as long as your goal remains to round to the nearest 10, you never need to adjust it.

MROUND, short for Multiple Round, rounds one number to the nearest multiple of another number. In practice, MROUND works almost the same as CEILING, except it can round either up or down -- the formula "=MROUND(12, 10)" produces a result of 10, whereas "=MROUND(17,10)" yields 20. Use MROUND in place of CEILING only when you want to round to the nearest 10, as opposed to rounding up universally.

Excel's simplest rounding functions, ROUND and ROUNDUP, don't normally work for rounding to the nearest 10. These functions are intended for rounding off extra decimal places. For example, "=ROUND(1.2, 0)" rounds 1.2 to 0 decimal places, producing 1. ROUNDUP works similarly, but would turn 1.2 into 2 in this case. You can use a trick to apply ROUND or ROUNDUP for rounding to the nearest 10: First divide the internal number by 10, and then multiply the entire function by 10, such as "=ROUNDUP(12/10, 0)*10." This works by turning the chosen number, 12, into 1.2. The function rounds 1.2 to 2, which the formula multiplies by 10, producing the desired result, 20. Unless you want to use ROUND or ROUNDUP for some particular reason, however, it's faster to just use MROUND or CEILING instead.

Whichever formula you choose, you need to enter it in each cell that you want to round, requiring a lot of busywork if you already have an entire sheet of data. Instead of rewriting every cell, open a blank sheet in the same workbook, write one iteration of the formula using a cell reference and then fill in the rest of the sheet. For example, if you want to round up 50 rows of data already entered in column A of Sheet1, type "=CEILING(Sheet1!A1, 10)" in cell A1 on Sheet2 and then drag the fill handle down 50 cells. The column on Sheet2 will perfectly match the column on Sheet1, but rounded up. Next, copy the entire column on Sheet2, right-click the header of that same column and choose "Values" from the Paste Options. This replaces the formulas in each cell with the static rounded values, allowing you to modify or delete Sheet1 without losing your numbers on Sheet2.