CEILING and FLOOR Functions in Excel – Round to a Specific Multiple

When working in Excel, especially with things like invoices or pricing, we often need to round numbers.

But what if someone (like me, with a bit of OCD 😅) wants all the numbers to look neat and uniform?

For example, what if I want all numbers to end with 5 or 0, means I want them rounded to a multiple of 5?

Is that possible in Excel?

I’d say yes!

That’s exactly what the CEILING and FLOOR functions are made for.

What Are CEILING and FLOOR Functions?

These two functions are used when you want to round a number to a specific multiple — not just to the nearest whole number.

Think of them as two friendly helpers:

 CEILING always rounds your number up to the next multiple you choose.

 FLOOR always rounds your number down to the previous multiple.

It’s that simple!

For example:

 `=CEILING(12, 5)` → gives 15 (rounds up to the next multiple of 5)

 `=FLOOR(12, 5)` → gives 10 (rounds down to the previous multiple of 5)

So, whether you want your numbers to go up or down— these two functions have you covered.

Syntax:

Both functions are used in a similar way.

 CEILING Syntax:

=CEILING(number, significance)

 FLOOR Syntax:

=FLOOR(number, significance)

Here’s what it means:

• number → the value you want to round.
• significance → the multiple (or step) you want to round to.

Example:

`=CEILING(27, 10)` → rounds up to 30

`=FLOOR(27, 10)` → rounds down to 20

When to Use CEILING and FLOOR:

Let’s look at a few everyday examples where these functions can make your Excel work cleaner and smarter 👇

1. Pricing or Billing:

Let’s say you’re setting prices and want them to end with a clean 0 or 5.

Example:

=CEILING(47, 5)

Result → 50

If you’d rather round down to make it look cheaper (😉), use:

=FLOOR(47, 5)

Result → 45

This is great for invoices, price lists, and billing sheets — keeps things tidy and easy to read.

2. Time Calculations:

If you track work hours or appointments, you may want to round time up or down to the nearest 15 or 30 minutes.

Example:

=CEILING(A1, “0:15”)

→ Rounds up to the next 15-minute mark.

=FLOOR(A1, “0:15”)

→ Rounds down to the previous 15-minute mark.

Perfect for time tracking, payroll, or scheduling tasks.

Simple Way to Remember:

 CEILING = Always Up

 FLOOR = Always Down

They’re perfect for:

•  Invoices
•  Prices
•  Time entries
•  Inventory
•  Reports

They make your data look neat, organized, and consistent — just the way any Excel perfectionist would like it!