How to Round to Nearest 100 in Excel? 6 Easy Ways!

Sometimes in Excel, we want to convert the number to the nearest 100 instead of an exact value.

Rounded-off numbers are often easier to read and comprehend.

For instance, if your monthly expense is $3,587, you may want to round it off to the nearest 100 and say that expense was $3,600. While it’s good to be precise, in some situations, it’s a good idea to round numbers as it can make them easier to comprehend.

In this tutorial, I will show you can round the number to the nearest 100 in Excel.

There are various built-in functions available in Excel that can help you round a number to the nearest 100.

Round to Nearest 100 Using the ROUND Function

The ROUND function allows you to round a number to the nearest specified value.  

Below I have a sample data set where I have employee names in column A and their sales in column B, and I want to round the sales to the nearest 100.

Dataset with sales values that needs to be rounded

Below is the syntax of the ROUND function.

ROUND(number, num_digits)

where:

  • number is the value that you want to round (these would be sales values in our example)
  • num_digit – this is the digit to which you want to round the number. We will use 2 here as we want to round to the nearest 100

For rounding of the sales value done by the first representative (Michael), the formula would be

ROUND(C2,-2)

Enter the above formula in cell D2 and it will convert the sales done by Michael to the nearest 100 as shown in the screenshot below.

Enter the ROUND formula in cell D2

Now drag the formula in the entire column to convert the sales for all representatives to the nearest 100.

To drag the formula use the fill handle (+ plus icon) that appears when moving the cursor to the bottom-right of the selected cell.

Apply the formula to the entire column

Now let me quickly explain how this works.

In the ROUND function, I’ve used -2 as the second argument.

This gives us the result where the sales values have been rounded to the nearest 100.

Had we used -1, it would have rounded it to the nearest 10, and had we used 0, it would have rounded it to the nearest integer.

Round to Nearest 100 Using ROUNDUP Function

If you want to round your number away from 0 you can use the ROUNDUP function, instead of the ROUND function.

For demonstration purposes, I have a data set where I have employee names in column A and their sales in column B, and I want to round up the sales to the nearest 100.

Dataset with sales values that needs to be rounded

Here is the syntax of the ROUNDUP function

ROUNDUP(number,num_digits)

In order to round the sales done by the first representative (Michael) the formula would become

ROUNDUP (C2,-2)

Enter the above formula in cell D2 and it will round up the sales to the nearest 100 as shown in the screenshot.

Enter the ROUNDUP formula in cell D2

Now copy the formula in the entire column to round up the sales of all representatives to the nearest 100.

Apply the formula to the entire column

If the number is greater than 0 ROUNDUP function will always round it to 100 if we specify num_digit = -2.

The difference between the ROUND and the ROUNDUP function is that the ROUNDUP function would always give you the higher value that meets the criteria. For example, 122 would be rounded to 100 by the ROUND function and 200 by the ROUNDUP function

Round to Nearest 100 Using ROUNDDOWN Function

Contrary to the ROUNDUP function, ROUNDDOWN will round down the number to the nearest 100. Let’s see it in practice.

Here is the syntax of the ROUNDDOWN function

ROUNDDOWN(number,num_digits)

In order to round the sales done by the first representative (Michael) the formula would become.

ROUNDDOWN (C2,-2)

Enter the above formula in cell D2 and It will round down the sales to the nearest 100 as shown below.

Enter the ROUNDDOWN formula in cell D2

Drag the formula in the entire column to round down the sales for all representatives to the nearest 100.

Apply the formula to the entire column

The ROUNDDOWN function will always round the number between 0 – 100 to 0 if we specify num_digit = -2 as in the above example.

While rounding to the nearest 100, ROUNDUP would always give you the value that is a multiple of 100 and higher than the value that is being rounded. On the other hand, ROUNDDOWN would give you the nearest 100 value that is less than the value that is being rounded

Round to Nearest 100 using the CEILING Function

The CEILING function works similarly to the ROUNDUP function.

It rounds up the number to the nearest 10, 100, and so on.

However, in this tutorial, we are looking at rounding the number to the nearest 100 so let’s see how we can achieve this. 

Here is the syntax of the CEILING function.

CEILING(number,significance)

Below is the CEILING formula that will round the sales value to the nearest 100

CEILING(C2,100)

Now enter the formula in Excel as shown below.

Enter the CEILING formula in cell D2

It will round up the sales done by Micheal to 200 as shown above.

Now drag the formula in the entire column to round up the sales done by all representatives to the nearest 100.

Apply the formula to the entire column

Tip: The Significance in the CEILING function (which is the second argument) represents the multiple to which you want to round. Here I rounded the number to the nearest 100 so I set the significance equal to 100. You can specify significance equal to 10 if you want to round the number to the nearest 10. Similarly, if you want to round the nearest integer specify a significance equal to 0.

Round to Nearest 100 using the FlOOR Function

The FLOOR function works similarly to the ROUNDDOWN function.

It rounds down the number to the nearest 10, 100, and so on.

Here is the syntax of the FlOOR function.

FLOOR(number,significance)

Below is the FLOOR formula that will round the sales value to the nearest 100

FLOOR(C2,100)

Now write the formula in Excel as shown below

Enter the Floor formula in cell D2

Now drag the formula to the entire column in order to round the sales done by all the representatives.

Apply the formula to the entire column

The FLOOR function will always round the number between 0 – 100 to 0 if you specify the significance equal to 100.

Round to Nearest 100 Using MROUND Function

MROUND function works similarly to the ROUND function.

It returns the number rounded to a multiple of (10,100, . .).

Here is the syntax of the MROUND function.

MROUND(number,multiple)

In order to round the sales done by the first representative (Michael) the formula would be 

MROUND(C2,100)

Now write the formula in Excel as shown below.

Enter the MROUND formula in cell D2

The formula will round the sales done by Micheal to the nearest 100 depending on the value. For instance, if the value to round is greater or equal to 50 it will round it to 100 otherwise 0. 

Now to round the sales done by all the representatives, apply the formula to the entire column using the (+) icon available by moving the cursor to the bottom right of the selected cell.

Apply the formula to the entire column

In this tutorial, I covered different formulas to round to the nearest 100 in Excel.

If you want to round to the nearest 100, it would be best to use the ROUND or the MROUND functions. Excel also offers the ROUNDUP/ROUNDDOWN and CEILING/FLOOR function in case you always want to round up or down.

Other Excel articles you may also like: