EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Maths Function in Excel Excel Rounding
Secondary Sidebar
Excel Functions
  • Maths Function in Excel
    • Calculating Investment Return In Excel
    • Sumif Function Examples
    • MROUND in Excel
    • SUM Cells in Excel
    • MMULT in Excel
    • MINVERSE in Excel
    • Trunc in Excel
    • SUMIF with Text
    • COS Function in Excel
    • RANDBETWEEN in Excel
    • Excel SUMIF with OR
    • Excel DEGREES Function
    • Calculations in Excel
    • Sumif Between Two Dates
    • Calculate Income Tax in Excel
    • PRODUCT Function in Excel
    • How to Sum Multiple Rows in Excel
    • Matrix in Excel
    • ROUND Formula in Excel
    • How to SUM in Excel
    • SUMPRODUCT Formula in Excel
    • Excel Running Total
    • Excel Rounding
    • Excel Exponents
    • Excel SIN Function
    • Excel Square Root Function
    • Excel ROUNDUP Function
    • ROUND Excel Function
    • Excel ROUNDDOWN Function
    • ODD Excel Function
    • SUMPRODUCT Function in Excel
    • AGGREGATE Function in Excel
    • ABS Function in Excel
    • Excel TAN Function
    • COMBIN Function in Excel
    • CEILING Function in Excel
    • INT Excel Function
    • SUBTOTAL Function in Excel
    • LN Function in Excel
    • Excel RAND Function
    • TANH Function in Excel
    • SUMIF Function in Excel
    • SUM Function in Excel
    • POWER Function in Excel
    • EVEN Function in Excel
    • LOG Function in Excel
    • FLOOR Excel Function
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Lookup Reference Functions in Excel (35+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL ADVANCED Certification
  • Excel for HR Training

Excel Rounding

By Madhuri ThakurMadhuri Thakur

Rounding in Excel

Excel Rounding (Table of Contents)

  • Rounding in Excel
  • How to Round Numbers in Excel?

Rounding in Excel

Rounding in Excel alters the actual value in a cell. In simple words, rounding means eliminating unwanted numbers and keeping the nearest numbers. A simple example will make you understand better. The rounding value of 99.50 roundings is 100. We have different kinds of rounding functions available to make rounding as per requirements.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,412 ratings)

How to Round Numbers in Excel?

Let’s understand how to Round Numbers in Excel using various functions with examples explained below.

You can download this Rounding Numbers Excel Template here – Rounding Numbers Excel Template

ROUND Function

Round is the first function that strikes into our minds when we think of rounding in excel. The round function makes rounding of numbers as per the number of digits we want to make rounding.

Syntax of ROUND is =ROUND(number, number_digit). Find the below screenshot for reference.

Round Function Syntax

Suppose there is a number 45.78956 in cell A2, and we wish to have only 2 digits after the decimal point. Apply ROUND function with number_digits as 2.

Rounding in Excel Example 1

Observe the screenshot .78956 converted to 79 as we mentioned 2 in the number of digits after the decimal point; hence it makes rounding to nearer number .79

ROUNDUP Function

Roundup name itself suggests that this function rounds the number upwards only.

The syntax for ROUNDUP Function is =ROUNDUP(number, number_digits). Find the below screenshot for reference.

ROUNDUP Function Syntax

The number of digits it will take upward is depending on the number of digits we give in “number_digit” while input roundup formula.

Rounding in Excel - Roundup Function 1

In the above screenshot, there is only one value after the decimal point, and we also give 1 in the formula; hence there is no change in the results. Suppose if we increase the number of digits after the decimal point as below.

Rounding in Excel - Roundup Function 2

23.436 rounded as 23.5 as after decimal there should be only one digit hence it makes rounding to its upper value and make as 23.5

ROUNDDOWN Function

It is quite the opposite to ROUNDUP as it is rounding to a lower value and displays the number of digits mentioned by the user after the decimal point. The syntax for ROUNDUP Function is =ROUNDUP(number, number_digits). Find the below screenshot for reference.

ROUNDDOWN Function Syntax

Consider the same example which used for ROUNDUP; there, we got the result as 23.5, here we got the result as 23.4 as it is rounded to the lower value.

Rounding in Excel - Rounddown Function 1

MROUND Function

MROUND function performs rounding, which may go up or down depending on the given multiple in the formula.

The syntax of MROUND is =MROUND(number, multiple). Find the below screenshot for reference.

MROUNDUP function syntax

In the below example, we use a multiple of 5; hence the results should be multiple of 5. 50 is the nearest multiple values of 5 for 51.789

Rounding in Excel - MROUND Function 2

Now in the below example, 55 is the nearest multiple values of 5 for 53.789.

Rounding in Excel - MROUND Function 1

FLOOR Function

The floor is another function that helps to round of numbers down. The syntax of the FLOOR Function is =FLOOR(number, significance). Find the below screenshot for reference.

FLOOR Function syntax

We want to do rounding, and significance is the number that we want to do the multiple. In the below example, we want the multiple of 3; hence we have given 3 in the place of significance. 9 is the nearest multiple of 3 for the value 10.20.

Rounding in Excel - FLOOR Function 1

We can perform this for negative values too; as told earlier, it helps to down the number; hence it gives the result as -12 because if we reduce -10.20 further, -12 is the nearest multiple of 3; hence it is -12.

Rounding in Excel - FLOOR Function 2

CEILING Function

CEILING is another function to rounding up the numbers. The syntax for the CEILING Function is =CEILING(number, significance). Find the below screenshot for reference.

CEILING Function syntax

It is similar to the FLOOR Function, but the difference is that it is used for Round down, which is used for Roundup.

In the below example, we have given the significance as 4, which means we need a rounding value greater than 21.60 and should be a multiple of 4. 24 is greater than 21.6, and it is a multiple of 4.

Rounding in Excel - CEILING Function Example

ODD Function

The ODD function is one of the rounding functions that help to find the nearest odd number of the given number.

Syntax for ODD is =ODD(number).

Odd Syntax

In the below example, we applied the ODD function for number 38.567. 39 is the nearest Odd number available hence it gave the result as 39.

Rounding in Excel - Odd Function Example

EVEN Function

Even function also works quite similar as ODD function, but the only difference is, it gives the nearest EVEN numbers after rounding.

Syntax of EVEN is =EVEN(number).

EVEN Function syntax

Find the below example for reference. The nearest number for 38.567 is 40.

Rounding in Excel - EVEN Function Example

TRUNC Function

TRUNC is another function available for rounding numbers. The syntax for TRUNCATE Function is =TRUNC(number, [number_digits])

TRUNC Funcion syntax

As through the number of digits, it will eliminate numbers after the decimal point. If we do not provide any number, it will round to the nearest integer.

Rounding in Excel - TRUNC Funcion 1

Observe the above screenshot; TRUNC is applied without a number of digits. 18 is the nearest integer of 18.2543.

Now we will see how it will work if we give the number of digits as 2 for the same example.

Rounding in Excel - TRUNC Funcion 2

It keeps only two values after the decimal point and truncates the other digits. If we give the number a positive number, it will truncate from the right side of the decimal point. If we give negative values and negative numbers, it will truncate from the left side of the decimal point.

INT Function

INT is another function that rounds the value down to the nearest integer. Syntax of INT is =INT(number)

INT Function syntax

If we use a positive value or negative value, it will always be rounding down the value. Even we use 10.7; still, it downs the value to the nearest integer 10.

Rounding in Excel - INT Function 1

Perform the same for the negative value now. Observe below it further reduces the value of-10.70 to -11.

Rounding in Excel - INT Function 2

ROUNDING for TIME

Rounding can apply to time also. We will see a few examples of rounding the time. The syntax for rounding the time is =CEILING(time, “1:00”). Find the below screenshot for reference.

Rounding in Excel - Round time 1

Observe the above formula; in the same way, I applied for FLOOR and CEILING.

If we want the rounding nearer to 20 minutes like that, we have to follow a different format. Please find the below screenshot for rounding to the nearest 20 minutes.

Rounding in Excel - Round time 2

Observe the formula; the moment we give the time, it will ask for hours, minutes, seconds; hence we can give the nearest time details for rounding. Below is the syntax for that.

Round time formula

I hope you understand how to apply rounding formulas in different ways as per the situation.

 Time function syntax

Things to Remember

  • ROUNDUP and ROUNDDOWN can be applied for negative numbers too.
  • For CEILING and FLOOR Functions, if we use positive numbers and negative significance, then the results will be #NUM, which is like an error message.
  • Rounding can also be applied to time but while working with time input formula as per minutes and hours.

Recommended Articles

This has been a guide to Rounding in Excel. Here we discussed How to Round Numbers, Time using functions like ROUND, ROUNDDOWN, CEILING, FLOOR, INT, TRUNC, etc., in Excel, along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

  1. Excel ROUNDUP Function
  2. ROUND Excel Function
  3. Excel ROUNDDOWN Function
  4. FLOOR Excel Function
Popular Course in this category
Excel Advanced Training (16 Courses, 23+ Projects)
  16 Online Courses |  23 Hands-on Projects |  140+ Hours |  Verifiable Certificate of Completion
4.8
Price

View Course

Related Courses

Excel for HR Training (8 Courses, 10+ Projects)4.9
2 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download Rounding Numbers Excel Template

EDUCBA

Download Rounding Numbers Excel Template

EDUCBA

डाउनलोड Rounding Numbers Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more