In case you are manually rounding up your numbers in Excel and want to learn a quick way to round up, then you can use Excel CEILING function! In this blog piece, we will cover everything about the Excel CEILING function including its advanced features.
Table of Contents
Introduction to Excel CEILING Function
As we all agree, Excel is a popular calculation and formulation program for data analysis. Hence, learning how to use CEILING function can really reduce your workload. This one specifically rounds up a number to the nearest specified multiple. For example, if you want to round up a number to the nearest 5, the CEILING function can do that for you.
How to use the CEILING Function in Excel
If you wanna use the Excel CEILING function, you need to follow a specific syntax.
=CEILING(number, significance)
The “number” argument is the number you want to round up. While the significance argument is the multiple to which you want to round up. So, if you want to round up to the nearest 5, you would use 5 as the significance.
=CEILING(23.45, 5)
The result would be 25. Because that is the next multiple of 5 above 23.45.
Using Negative Numbers with ceiling formula in excel
If you want to round up negative numbers, you must adjust significance argument accordingly. So for instance, if you want to round up to the nearest -10, you would use -10 as the significance.
=CEILING(-23.45, -10)
The result would be -20. Since it is the next multiple of -10 above -23.45.
Using ceiling excel function with Dates and Times
The function can also work with dates and times. However, you need to use a different syntax like below.
=CEILING(DateTime, time_unit)
The datetime is the date or time you want to round up. And “time_unit” argument is the unit of time you want to round up to. In case you want to round up to the nearest hour, you would use hour as the time unit.
=CEILING(A2, “hour”)
If cell A2 contained the date/time value 1/1/2023 2:45 PM, result would be 1/1/2023 3:00 PM. Since it is the next hour above 2:45 PM.
Excel CEILING Function Tips and Tricks: ceiling function symbol
- You can use the Excel CEILING function with other functions to create more complex formulas. For example, you could use CEILING with SUM to round up the total of numbers.
- If you want to round up to the nearest 100, but only if the number exceeds 100, you can use the IF function in conjunction with CEILING. Such as this =IF(A2>100, CEILING(A2,100), A2)
- The Excel CEILING function can also operate with arrays of numbers. To use the function with an array, you will enter the formula as usual. But you select multiple cells instead of a single cell. After that, it will automatically apply to each cell in the range.
- So in case you will round up to nearest fraction, you can use it for this purpose. Here, you can state the number of decimal places in the formula writing phase.
- Good news is we can combine it with FLOOR function. So we will create values rounded up or down to the nearest multiple. You can look at below formulation to see how we can do it for multiple as 5.
=IF(MOD(A1,5)=0, A1, FLOOR(A1,5)+5)
This will round all numbers if they are a multiple of 5. And it will round down any number that is not a multiple of 5.
Excel CEILING Function FAQs: What is the Excel CEILING Function?
- What is the difference between CEILING and ROUNDUP in Excel? First, CEILING rounds up to the nearest multiple as we discussed. While ROUNDUP rounds up to the specified number of decimal places.
- Can the Excel CEILING work with negative multiples? Yes, here you will enter the negative value as significance argument.
- How to use Excel CEILING function with text? No, the function only works with numerical values.
- How to round up to the nearest 10 in Excel? For that, you would use 10 as significance argument.
- Is it possible to it with conditional formatting? Yes, you can use it to highlight cells that meet certain criteria.
- Does it work with pivot tables? Yes, you can us it just like any other formula in Excel.
Conclusion
The CEILING function is ideal tool when rounding up numbers in Excel. Whether working with simple numbers or more complex and long number lists, the function can help you get the job done.
For sure we can say CEILING function can help simplify your work and make calculations more accurate.
- The CEILING function rounds up to the nearest multiple of any number.
- The significance argument specifies the multiple to round up to.
- You can round both positive and negative multiples. But you need to pay attention to bigger and smaller values for negatives more carefully.
- You can use the function with arrays and in combination with other Excel functions.
With this practical info, you can use the function to lessen your tasks at work or at school.
So next time you need to round up a number to the nearest multiple, you do not need to do it manually or adjusting the value. Instead of manual calculation, this time you can try using the CEILING function for your benefit.
When you use it for a few times, you will learn it in real sense and take your Excel skills to the next level!
A dedicated Career Coach, Agile Trainer and certified Senior Portfolio and Project Management Professional and writer holding a bachelor’s degree in Structural Engineering and over 20 years of professional experience in Professional Development / Career Coaching, Portfolio/Program/Project Management, Construction Management, and Business Development. She is the Content Manager of ProjectCubicle.
