How to Use the ROUND Function in Excel?

(Note: This guide on how to round in Excel is suitable for all Excel versions including Office 365)

The Round function in Excel is used to handle irrational numbers or numbers with trailing digits after the decimal point. It is the same concept as in mathematics to adjust the digits of a decimal number to give an approximate value.

When working with large numbers or data, the trailing digits can be a bit intricate. For example, consider the famous number Pi. It is an irrational number with a fractional value of 22/7 and in decimal terms, its value is 3.141592653589793238, and so on. To avoid confusion while calculating and operating with the value, Pi is rounded off to 3.14. 

Rounding off makes the number easy to read and simple to use. 

Note: Round off values only when there isn’t much need for precision. Or, to represent the number as a whole. For example, it is easy to affirm the population of California is approximately 39,000,000 instead of 38,959,247.

In Excel, you will be working around numbers and calculations. If countered with an irrational number, you can use the ROUND function to round up any number to the desired decimal places based on your need.

In this guide, I will show you how to use the ROUND function to round off values.

Related:

The Excel SMALL Function – 3 Best Examples

How to Make An Excel Forecast Sheet in 3 Steps – The Best Guide

How to Use Excel COUNTIFS: The Best Guide

ROUND Function in Excel

In Excel, the ROUND function works similarly to the conventional mathematical concept of rounding off a number.

Rounding off a number depends on the number of places or significant digits.

When the number after the last significant digit is greater than 5, the last significant digit is incremented by 1. When the number after the last significant digit is less than 5, the last significant digit remains the same.

Also, note that if the digit is a positive value, the number is rounded off to the right of the decimal point. If the digit is a negative value, the number is rounded off to the left of the decimal point.

How to Round in Excel Using the ROUND Function?

Step 1.     Enter the number and digit in the cells

Step 2.     Enter the following formula in the cell or formula bar

Syntax

= (ROUND(number,num_digit))

Here, ‘number’ refers to the number to be rounded off, and ‘num_digit’ is the place to the right or left of the decimal point. The num_digit can be a positive value, negative value, or zero.

The (number,num_digit) in the formula represents the cells of the number and digit respectively.

Example

In this example, the number 3.1415926 is to be rounded to 1 decimal place. The rounded value is 3.1. If the number is 3.1415926 rounded to 2 decimal places, the rounded value would be 3.14.

Also Read:

How to Add Error Bars in Excel? 7 Best Methods

How to Superscript in Excel? (9 Best Methods)

Excel Goal Seek—the Easiest Guide (3 Examples)

How to use the ROUND Function Based on Numbers and Digits?

Based on the value of the number after the last significant digit and the number of digits to be rounded off, there are two cases of three possibilities each.

• If the number after the last significant digit is greater than or equal to 5
  • If the num_digit is >0
  • If the num_digit is <0
  • If the num_digit = 0

• If the number after the last significant digit is lesser than 5
  • If the num_digit is >0
  • If the num_digit is <0
  • If the num_digit = 0

When the Last Significant Digit Is Greater Than or Equal 5 

• Num_digit Greater Than 0

In this case, the rounding works towards the right of the decimal point, and the last significant digit is incremented by 1.

Let us see the second example (Row 4), where the number is 3.575871 and the num_digit is 3. The number to the right of the three decimal places is 8 which is greater than 5. The last significant digit is incremented by 1 and the rounded-off value is 3.576.

• Num_digit Lesser Than 0

In this case, the rounding works towards the left of the decimal point, and the last significant digit remains the same.

Let us see the second example (Row 4), where the number is 56183.57 and the num_digit is -2. The last two digits to the left of the decimal point are rounded off to zero. Since the second digit to the left of the decimal pointer is greater than 5, the number before the second digit is incremented by 1. The rounded-off value is 56200.

• Num_digit Equals 0

In this case, the rounding off works towards the left of the decimal point. The number is rounded off to a whole number and the last significant digit to the left of the decimal point is incremented by 1.

Let us see the second example (Row 3), where the number is 56183 and the num_digit is 0. The number to the right of the decimal point is 5. The number to the left of the decimal is incremented by 1 and the rounded-off value is 56184. The value is returned as a whole number.

When the Last Significant Digit Is Greater Than or Equal 5

• Num_digit Greater Than 0

In this case, the rounding works towards the right of the decimal point and the last significant digit remains the same.

Let us see the second example (Row 4), where the number is 3.42314 and the num_digit is 2. The number to the right of the two decimal places is 3 which is less than 5. The last significant digit remains the same and the rounded-off value is 3.42.

• Num_digit Lesser Than 0

In this case, the rounding works towards the left of the decimal point, and the last significant digit to the left of the decimal point remains the same.

Let us see the second example (Row 4), where the number is 56123.57 and the digit is -2. The rounding works towards the left of the decimal point. The last two digits before the decimal point are rounded off to zero. And the third digit to the left of the decimal point stays the same. That is, the rounded-off value is 56100.

• Num_digit Equals 0

In this case, the rounding off works towards the left of the decimal point. The number is rounded off to a whole number and the last significant digit stays the same

Let us see the second example (Row 3), where the number is 56123.17 and the num_digit is 0. The number to the right of the decimal point is 1. The number to the left of the decimal remains the same and the rounded-off value is 56184. The value is returned as a whole number. 

Suggested Reads:

How to Shade Every Other Row in Excel? (5 Best Methods)

How to Extract an Excel Substring? – 6 Best Methods

How to Split Cells in Excel? 3 Easy Methods

Closing Thoughts

In this guide, you have seen the importance of rounding up values and how to round up values in Excel. 

Use the ROUND function regularly to improve the readability of your spreadsheets.

To find more high-quality guides, visit our free Excel resources section. 

Want to learn more about Excel? Click here to access our advanced Excel courses with in-depth training modules. You can train your entire team in Excel and other business software, for a low one-time monthly fee here.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA.He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!