Round to the Nearest 5 in Excel Using 3 Different Functions

Whether you’re working with something like test scores, tracking expenses, or any other type of data, being able to round numbers to the nearest 5 in Excel is a handy skill. This is helpful when you need to round to the nearest multiple (like 5) instead of simply to the nearest whole number. 

Here are a few different ways you can round to the nearest five (or any other multiple you need)!

Round to the Nearest 5 in Excel Using the MROUND Function

Let’s say you’re taking a tally of the average number of pretzels in a snack-size bag from different brands, and want to round each number to the nearest multiple of five. In the example below, 23 would be rounded to 25, 21 would be rounded to 20, and so on. We’ll use the MROUND function to make it happen.

The MROUND function in Excel allows you to round a number up or down to the nearest multiple of a given value. It works by taking two arguments: the number to be rounded and the multiple to round to. In our case, the multiple is five.

To use this in our example, create another column titled “Rounded” in cell D2, and enter this formula in cell D3:

=MROUND(C3,5)

Then, use the Excel fill handle to drag the formula down through the rest of the cells in the table, and your task is complete!

This method works well when you want to round the numbers up or down to the closest multiple. If you want to round the numbers either up or down specifically, you’ll need help from the following functions.

Note from Microsoft help: When a decimal value is provided to the Multiple argument , the rounding direction is undefined for midpoint numbers. For example MROUND(6.05,0.1) returns 6.0 while MROUND(7.05,0.1) returns 7.1.

Round Up or Down to the Nearest 5 in Excel

There are two functions in Excel you can use to round up or down: CEILING and FLOOR. As you probably guessed, the first function rounds up and the second rounds down.

Whereas MROUND will round up or down depending on the midpoint, CEILING only rounds up and FLOOR only rounds down.

These functions support the same basic arguments as MROUND, the value to round and the multiple to which you want to round it.

To round up to the nearest multiple of five, enter this formula:

=CEILING(C3,5)

To round down, use this one:

=FLOOR(C3,5)

Note that both of these functions will round up or down to the nearest multiple of the specified number, so if the number you are rounding is already a multiple of that number, it won’t be changed.

Depending on your version of Excel, you can also use the CEILING.MATH and FLOOR.MATH functions instead of CEILING and FLOOR. The MATH versions of these functions default to a multiple of 1 and provide extra control over the rounding direction for negative numbers with an additional mode argument.

Do you have any other quick ways to round to the nearest five (or any other multiple)? Let us know in the comments!