When using Excel, you’ll likely get a lot of different results from various calculations and functions. However, it may not always be helpful to have a spreadsheet filled with decimal points and fractional digits. Instead, you may want to round up or round down to the nearest whole number. There are also other ways of rounding that may better fit your needs.
But if you’ve never tried rounding in Excel before, you probably have a lot of questions. For example, what’s the easiest way to round to the nearest whole number? Can you round to the nearest 5? Can you round to a specific decimal place?
In today’s guide, we will answer all of these questions and more. And remember, if you need additional help with Excel, you can always reach out to the trained IT experts at Geeker. Now let’s get started!
The Rounding Excel Function
The standard ROUND function is often the best way to round a fractional number to suit your needs. It is the most common formula for rounding in Excel, especially since it gives you the freedom to round numbers in different ways. To use the ROUND function, you have to include two vital pieces of information: the number to be rounded and the number of digits to round the number to.
To better understand how the ROUND function works, let’s imagine that you have the number 98.763 in cell A1. You could then use some of the formulas in the table below to get the specific result you want:
|=ROUND(A1,0)||98.763||99||Round to the nearest whole number|
|=ROUND(A1,1)||98.763||98.8||Round to 1 decimal place|
|=ROUND(A1,2)||98.763||98.76||Round to 2 decimal places|
It’s important to note that the ROUND function has a few built-in rules that dictate how numbers are rounded, particularly when rounding to different decimal places. For instance, digits between 0 and 4 are rounded down, while digits between 5 and 9 are rounded up. This means that the ROUND function relies on the numbers themselves to decide which direction to round to (up or down). However, if you specifically want to round up or round down, you can use separate functions that we will explain in the sections below.
How To Round Up
Excel’s ROUNDUP function works exactly like the standard ROUND function, except that it always rounds up (never down). This means that, unlike ROUND, ROUNDUP completely ignores the traditional rules of rounding up and rounding down. So, even if a decimal is a 3, the ROUNDUP function would still round up, whereas the ROUND function would automatically round down.
Let’s say that you have the number 7.681 in cell B2. Now we can use this example to create a few different scenarios with ROUNDUP:
|=ROUNDUP(B2,0)||7.681||8||Round up to the nearest whole number|
|=ROUND(B2,1)||7.681||7.7||Round up to the first decimal point|
|=ROUND(B2,2)||7.681||7.69||Round up to the second decimal point|
Another way to round up in Excel is the CEILING function. This rounds the number up to the nearest multiple of a given integer. For example, you can round to the nearest 5, round to the nearest 100, or round to the nearest 1000 multiplier using the CEILING function. Here are a few examples:
|=CEILING(B2,1)||763||764||Round up to the nearest multiple of 1|
|=CEILING(B2,5)||763||765||Round up to the nearest multiple of 5|
|=CEILING(B2,10)||763||770||Round up to the nearest multiple of 10|
|=CEILING(B2,100)||763||800||Round up to the nearest multiple of 100|
|=CEILING(B2,1000)||763||1000||Round up to the nearest multiple of 1000|
How To Round Down
The ROUNDDOWN function works in the exact same way as the ROUNDUP function — but in the opposite direction. Now let’s say that you have the number 29.998 in cell C3. Here are a few examples of how you could use ROUNDDOWN and what results it would return:
|=ROUNDDOWN(C3,0)||29.998||29||Round down to the nearest whole number|
|=ROUNDDOWN(C3,1)||29.998||29.9||Round down to the first decimal point|
|=ROUNDDOWN(C3,2)||29.998||29.99||Round down to the second decimal point|
There is also an alternative to ROUNDDOWN; it is called the FLOOR function. Once again, it works just like the CEILING function, but it rounds down rather than up. Now let’s say you have the number 1,168 in cell D4. Here are a few examples of how to use the FLOOR function in this scenario:
|=FLOOR(D4,1)||1168||1167||Round down to the nearest multiple of 1|
|=FLOOR(D4,5)||1168||1165||Round down to the nearest multiple of 5|
|=FLOOR(D4,10)||1168||1160||Round down to the nearest multiple of 10|
|=FLOOR(D4,100)||1168||1100||Round down to the nearest multiple of 100|
|=FLOOR(D4,1000)||1168||1000||Round down to the nearest multiple of 1000|
Round To The Nearest Multiple
If you want to round to multiples of numbers without specifying whether you want to go up or down, you can use the MROUND function. Essentially, MROUND works as a combination of the FLOOR and CEILING functions. This means that the syntax of MROUND is virtually the same.
To use a new example, let’s say that you have the number 101.6 in cell E5. Here are a few ways to use MROUND in this scenario:
|=MROUND(E5,1)||101.6||102||Round to the nearest multiple of 1|
|=MROUND(E5,5)||101.6||100||Round to the nearest multiple of 5|
|=MROUND(E5,10)||101.6||100||Round to the nearest multiple of 10|
|=MROUND(E5,100)||101.6||100||Round to the nearest multiple of 100|
|=MROUND(E5,1000)||101.6||1000||Round to the nearest multiple of 1000|
It’s also important to note that you can round using negative multiples with the FLOOR, CEILING, and MROUND functions. However, to do this, the sign has to be matching for the original number and the multiple. If not, Excel will return a #NUM! error. In other words, if you want to round to a negative multiple, the original must also be negative.
To demonstrate this concept, let’s say that you have the number -10 in cell F6. Here are a few examples of how to use negative multiples with MROUND:
|=MROUND(F6,-5)||-10||-10||Round to the nearest multiple of -5|
|=MROUND(F6,-4)||-10||-8||Round to the nearest multiple of -4|
|=MROUND(F6,-3)||-10||-9||Round to the nearest multiple of -3|
|=MROUND(F6,-2)||-10||-10||Round to the nearest multiple of -2|
|=MROUND(F6,-1)||-10||-10||Round to the nearest multiple of -1|
|=MROUND(F6,0)||-10||0||Round to the nearest multiple of 0|
Need help from dependable Excel experts? Reach out to Geeker today for on-demand IT and software solutions!