Back to Home

Rounding Formula in Excel

Your comprehensive guide to all Excel rounding formulas and functions

Complete Guide to Excel Rounding Formulas

Excel provides eight different functions for rounding numbers, each designed for specific scenarios. This guide covers all of them, helping you choose the right formula for your needs.

8 Excel Rounding Functions

ROUND

Standard rounding

ROUNDUP

Always round up

ROUNDDOWN

Always round down

MROUND

Round to multiple

CEILING

Round up to multiple

FLOOR

Round down to multiple

INT

Remove decimals

TRUNC

Truncate decimals

Quick Reference Chart

FunctionInput: 12.5Input: 12.3Input: 12.7Use Case
ROUND131213Standard rounding
ROUNDUP131313Conservative estimates
ROUNDDOWN121212Floor values
INT121212Integer only
TRUNC121212Cut decimals

1. ROUND - Standard Rounding

Syntax

=ROUND(number, num_digits)

Follows mathematical rounding: 5+ rounds up, <5 rounds down

Positive num_digits (decimals)

=ROUND(123.456, 2) → 123.46

=ROUND(123.456, 1) → 123.5

=ROUND(123.456, 0) → 123

Negative num_digits (place values)

=ROUND(1234, -1) → 1230

=ROUND(1234, -2) → 1200

=ROUND(1234, -3) → 1000

When to use: Most general-purpose rounding, financial calculations, statistics

2. ROUNDUP - Always Round Up

Syntax

=ROUNDUP(number, num_digits)

Always rounds away from zero, regardless of value

Examples

=ROUNDUP(12.1, 0) → 13

=ROUNDUP(12.01, 0) → 13

=ROUNDUP(123.456, 2) → 123.46

=ROUNDUP(1234, -2) → 1300

When to use: Inventory (need whole units), conservative estimates, capacity planning

3. ROUNDDOWN - Always Round Down

Syntax

=ROUNDDOWN(number, num_digits)

Always rounds toward zero

Examples

=ROUNDDOWN(12.9, 0) → 12

=ROUNDDOWN(12.99, 0) → 12

=ROUNDDOWN(123.456, 2) → 123.45

=ROUNDDOWN(1999, -2) → 1900

When to use: Age calculations, completed units, conservative revenue estimates

4. MROUND - Round to Nearest Multiple

Syntax

=MROUND(number, multiple)

Rounds to the nearest multiple of the specified value

Examples

=MROUND(13, 5) → 15 (nearest 5)

=MROUND(22, 5) → 20 (nearest 5)

=MROUND(123, 10) → 120 (nearest 10)

=MROUND(0.3, 0.2) → 0.4 (nearest 0.2)

When to use: Pricing (round to $5), scheduling (15-min intervals), packaging (dozens)

5. CEILING - Round Up to Multiple

Syntax

=CEILING(number, significance)

Always rounds UP to the nearest multiple

Examples

=CEILING(12, 5) → 15 (up to nearest 5)

=CEILING(123, 100) → 200 (up to nearest 100)

=CEILING(1234, 1000) → 2000 (up to nearest 1000)

=CEILING(0.3, 0.5) → 0.5 (up to nearest 0.5)

When to use: Budget padding, ordering in case lots, capacity planning

6. FLOOR - Round Down to Multiple

Syntax

=FLOOR(number, significance)

Always rounds DOWN to the nearest multiple

Examples

=FLOOR(18, 5) → 15 (down to nearest 5)

=FLOOR(567, 100) → 500 (down to nearest 100)

=FLOOR(5678, 1000) → 5000 (down to nearest 1000)

=FLOOR(0.7, 0.5) → 0.5 (down to nearest 0.5)

When to use: Conservative forecasts, guaranteed minimums, safe estimates

7. INT - Return Integer Part

Syntax

=INT(number)

Rounds down to the nearest integer (no decimals)

Examples

=INT(12.1) → 12

=INT(12.9) → 12

=INT(99.99) → 99

=INT(-12.9) → -13 (rounds down, away from zero)

When to use: Extract whole numbers, count complete units, age calculations

8. TRUNC - Truncate Decimals

Syntax

=TRUNC(number, [num_digits])

Cuts off decimals without rounding

Examples

=TRUNC(12.9) → 12

=TRUNC(123.456, 2) → 123.45 (keep 2 decimals)

=TRUNC(123.456, 1) → 123.4 (keep 1 decimal)

=TRUNC(-12.9) → -12 (truncates toward zero)

When to use: When you need to simply cut off decimals without any rounding logic

Complete Comparison

ValueROUNDROUNDUPROUNDDOWNINTTRUNC
12.11213121212
12.51313121212
12.91313121212

Rounding to Multiples

ValueMROUND(n,5)CEILING(n,5)FLOOR(n,5)
12101510
13151510
17152015

When to Use Which Formula

Use ROUND when:

  • You need standard mathematical rounding
  • Working with financial data (prices, taxes)
  • Displaying averages and statistics
  • General-purpose rounding

Use ROUNDUP when:

  • You need conservative estimates (always round up)
  • Calculating materials or inventory (can't order 2.3 boxes)
  • Budget padding for safety margin
  • Capacity planning

Use ROUNDDOWN when:

  • Calculating age or years of service
  • Conservative revenue projections
  • Guaranteed minimums
  • Completed units or cycles

Use MROUND, CEILING, or FLOOR when:

  • Rounding to specific multiples (5, 10, 100, 1000)
  • Pricing strategies ($4.95, $9.95)
  • Scheduling (15-minute intervals)
  • Packaging (dozens, cases)

Use INT or TRUNC when:

  • You only need the integer part
  • Removing decimals without rounding logic
  • Extracting whole numbers from calculations
  • Simple truncation is sufficient

Tips and Best Practices

1. Round at the End

Perform all calculations first, then round the final result. Rounding intermediate steps can introduce compounding errors.

2. Document Your Rounding Method

Always note which rounding method you're using and why. This ensures transparency and helps others understand your spreadsheet.

3. Be Consistent

Use the same rounding method throughout your workbook. Mixing methods can lead to confusion and errors.

4. Keep Original Values

Store exact values in one column and rounded values in another. This preserves precision for future calculations while displaying clean numbers.

5. Test Your Formulas

Always verify your rounding formulas with test values, especially edge cases like 0.5, negative numbers, and very large/small numbers.

Related Excel Resources

How to Use ROUND Function in Excel

Complete tutorial on the ROUND function

Excel Round to Nearest 5

Master MROUND for rounding to multiples of 5

Excel Round Up to Nearest 100

Round up to hundreds with CEILING

Excel Round Up to Nearest 1000

Round up to thousands for large numbers

Excel Round to Nearest Whole Number

Remove decimals and round to integers

How to Round in Excel

Master all Excel rounding techniques