How to Round to Nearest 100 in Excel (6 Quickest Ways)

Get FREE Advanced Excel Exercises with Solutions!

In certain circumstances, we prefer the rounded or approximate number rather than the exact number to make communication easier. For example, the exact population of New York is 8,253,213. But we like to say it is around 8 million. So, you may need your given number to round to the nearest 100 in Excel.

In this article, you’ll find the six quickest ways to round any given number to the nearest hundred (100) in Excel.


How to Round to Nearest 100 in Excel: 6 Methods

As shown in the following figure, we have the unit price for each Product ID.

Now, we have to round the unit prices to the nearest 100.

How can we do that?

It’s really a simple task.

Let’s get started.

dataset

Here, we have used the Microsoft 365 version. You can use any other version at your convenience.


1. Using ROUND Function

Firstly, we may utilize the ROUND function, one of the most popular functions used for rounding any number. The function returns a number rounded to a number of digits. Follow the steps to do it.

📌 Steps:

  • First of all, go to cell D5 and insert the formula.
=ROUND(C5,-2)

Here,

C5= The number to be rounded.

-2= The number of digits to which the number should be rounded.

The ROUND(C5, -2) syntax takes C5 as the number to round and “-2” is the number of digits as we want to round the nearest 100.

Using the ROUND Function to round to nearest 100 in excel

  • Then, press ENTER and drag down the Fill Handle tool for other cells.

Fill Handle

Finally, you will get your numbers rounded to the nearest 100, just like in the image below.

Read More: Round to Nearest 5 or 9 in Excel


2. Utilizing ROUNDUP Function

The Excel ROUNDUP function returns a rounded number to a given number. This acts like the ROUND function, but it always rounds a number up. That means it rounded the number upward for the given number. Follow the steps to clear your confusion.

📌 Steps:

  • In the beginning, move to cell D5 and write up the formula.
=ROUNDUP(C5,-2)

The ROUNDUP(C5, -2) syntax takes C5 as the number to round, and “-2” is the number of digits as we want to round the nearest 100. This function always rounds the function up to the next nearest 100.

Utilizing the ROUNDUP Function to round to nearest 100 in Excel

  • Then, drag it down for other cells to get the following result after pressing ENTER.

Read More: How to Round Numbers to the Nearest Multiple of 5 in Excel


3. Applying ROUNDDOWN Function

The Excel ROUNDDOWN function returns a number rounded down to a given number. It operates like the ROUND function, except it always rounds a number down. If you have a number less than 100, then it will round it down to 0, as you want to round down to the nearest 0.

📌 Steps:

  • Firstly, move to cell D5 and enter the formula.
=ROUNDDOWN(C5,-2)

The ROUNDUP(C5, -2) syntax takes C5 as the number to round, and “-2” is the number of digits as we want to round the nearest 100. This function rounds down the number to the nearest 100.

Applying the ROUNDDOWN Function to round to nearest 100 in excel

  • Then, press ENTER and get the result like the image below.

Read More: How to Round Numbers in Excel Without Formula


4. Employing CEILING Function

When you have to require a rounded-up number, you may utilize the CEILING function which is similar to the ROUNDUP function. The function rounds up a given number based on the specified significance. Go through the steps below to get a basic idea.

📌 Steps:

  • Firstly, move to cell D5 and insert the formula.
=CEILING(C5, 100)

This CEILING(C5, 100) syntax takes the number to be rounded as C5 and the significance as 100.

Employing the CEILING Function to round to nearest 100 in excel

  • Eventually, you will get the result after pressing ENTER.


5. Using FLOOR Function

If there is any need to round the number down, you can use the FLOOR function, which is similar to the ROUNDDOWN function.  The function in Excel rounds down a certain number to the nearest multiple of a given significance. You need to follow the steps to do it.

📌 Steps:

  • Primarily, input the following formula in cell D5.
=FLOOR(C5, 100)

This function takes the number to be rounded as C5 and the significance is 100. It rounds the number down to the below number.

Using the FLOOR Function to round to nearest 100 in Excel

Thus, you will get your desired result.

Rounded to nearest 100 by FLOOR function in Excel

Read More: Round Down to Nearest 10 in Excel


6. Applying MROUND Function

The MROUND function rounds up or down a given number based on the specified multiple significance. It is similar to the ROUND function, except the ROUND function has no option to calculate based on significance. Follow the steps to do it.

📌 Steps:

  • Firstly, select cell D5 and enter the formula.
=MROUND(C5, 100)
  • Then, press ENTER and drag it down for other cells.

Applying the MROUND Function to round to nearest 100 in Excel

Consequently, you will get the result just like the image below.

Read More: How to Round Numbers to Nearest 10000 in Excel


Round to the Nearest Whole Number in Excel

Sometimes, you may need to round your whole number. You can do it by applying the INT function. This function rounds the number without the fraction and displays the number of decimals.

📌 Steps:

  • Firstly, select cell D5 and enter the formula.
=INT(C5)

This function rounds the whole number without the fraction.

Excel Round to the Nearest Whole Number

Secondly, press ENTER and drag it down to get the following result.

Using INT function

Read More: How to Round to Nearest 1000 in Excel


Round to the Nearest 5/1000 in Excel

Also, we can round the number to the nearest 5 by employing the CEILING function. We mentioned earlier how we can use this function. It rounds the entered number to the nearest 5 as we have entered the significance as 5. If you insert the argument 1000, it will round the number to the most immediate 1000. We will demonstrate to you the steps to get proper visualization.

📌 Steps:

  • Initially, go to cell D5 and input the formula.
=CEILING(C5,5)

This function rounds the value to the nearest 5 as we insert the significance as 5.

Round to the Nearest 5/1000 in Excel

  • Then, press ENTER and you will get the following result after dragging down.


Difference Between ROUND, ROUNDUP, and ROUNDDOWN Functions

Have you noticed any differences between the ROUND, ROUNDUP, and ROUNDDOWN functions? Let’s look at the differences in our dataset.

See the output of D6, E6, and F6 cells in the following figure. The ROUND function rounds 121.56 to 100, ROUNDUP rounds it up to 200, and ROUNDDOWN rounds it down to 100.

Difference Between ROUND, ROUNDUP, and ROUNDDOWN Functions


Practice Section

We have provided a practice section on each sheet on the right side for your practice. Please do it by yourself.

Practice Section


Download Practice Workbook

Download the following practice workbook. It will help you to realize the topic more clearly.


Conclusion

That’s all about today’s session. And these are some easy methods to round to the nearest 100 in Excel. Please let us know in the comments section if you have any questions or suggestions. For a better understanding please download the practice sheet. Visit our website ExcelDemy, a one-stop Excel solution provider, to find out diverse kinds of excel methods. Thanks for your patience in reading this article.


Further Readings


<< Go Back to Round to Nearest Whole Number | Rounding in Excel | Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Abdul Kader
Md. Abdul Kader

MD. ABDUL KADER is an engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SWM, GIS, RS, and SPSS. He holds a B.Sc in Urban & Regional Planning from Chittagong University of Engineering and Technology and has shifted to become a content developer. In this role, he crafts technical content centred around... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo