Rounding in Excel (All You Need to Know)

Rounding in Excel means using Excel’s features and functions for rounding decimal or fractional numbers to integers or to a certain number of decimal places.

In this Excel tutorial, we will show you how to round numbers, percentages, time, and currencies in Excel.

For example, the dataset below represents the Temperature of a certain State. Therefore, we have used formulas with different functions to round the temperature for better visuality.

The overview image of this article is shown below as a representation of this article. We will get to learn the entire article once we go through it.

In this article, we will round numbers in Excel by adjusting column width and using some basic Excel features such as the Decrease Decimal command and the Format Cells dialog box. Besides, we will use 10 different functions, such as the ROUND, ROUNDUP, ROUNDDOWN, MROUND, FLOOR, CEILING, INT, TRUNC, EVEN, and ODD functions, to do the same. Later on, we will provide six examples of rounding numbers in Excel based on some practical applications. Finally, we will also show you a way to stop the rounding in Excel where decimal or fractional numbers are necessary.

⏷Use Excel Features for Rounding Numbers in Excel
   ⏵Adjusting Column Width
   ⏵Using Decrease Decimal Feature
   ⏵Using Format Cells Dialog Box
⏷Use Excel Functions for Rounding Numbers in Excel
   ⏵Using the ROUND Function
   ⏵Using ROUNDUP Function
   ⏵Using ROUNDDOWN Function
   ⏵Using MROUND Function
   ⏵Using FLOOR Function
   ⏵Using CEILING Function
   ⏵Applying INT Function
   ⏵Using TRUNC Function
   ⏵Using EVEN Function
   ⏵Using ODD Function
⏷Rounding in Excel Based on Specific Applications
   ⏵Rounding to Nearest 0.5/5/10/100/1000
   ⏵Rounding Negative Numbers
   ⏵Rounding to Nearest 10/50 Cents
   ⏵Rounding to Nearest Hours
   ⏵Rounding to Nearest 15 Minutes
   ⏵Rounding Percentages
⏷Stop Rounding in Excel
⏷Knowledge Hub

How to Use Excel Features for Rounding Numbers in Excel

In this section, we will show you how to round numbers by adjusting column width and also using two features, such as the Decrease Decimal command and the Format Cells dialog box.

These methods are really useful if you want to round numbers in Excel without using formulas.

The dataset below shows the Humidity, Wind Speed, and Temperature of some different States. Here we will round the temperature of these states using those features.

1. Adjusting Column Width for Rounding in Excel

Adjusting column width is a common and easy way to round numbers in Excel.

Here, we will place the cursor to the very left of the column bar of column F. Then, left-click on the mouse and drag the column divider ruler to the left to decrease the column width.

Once the column width is decreased, Excel will show you a rounded number adjusting to the new width of the column.

Finally, the temperature is rounded to 17.15 in cell F6.

2. Using Decrease Decimal Feature from the Home Tab

Rounding the value by adjusting column width doesn’t allow you to round the value up to a specific digit. So, using the Decrease Decimal command can be another handy option.

Here, we will use the Decrease Decimal command from the Number group in the Ribbon to round numbers in Excel.

• Initially, select cell range F6:F13 and go to the Home tab >> Number group of commands >> click on the Decrease Decimal option.

• Click on the Decrease Decimal option as much as you prefer, as this option decreases one decimal place with every click.
• Now, the result (17) will be like the GIF shown below.

3. Using Format Cells Dialog Box

In this method, we will learn how to round numbers in Excel using the Format Cells dialog box. This method is useful when you need to fix the decimal places up to a certain digit.

• Initially, select cell range F6:F13 from the dataset >> right-click on the mouse to get the context menu.
• Once the menu appears, select the Format Cells option as below.

• After that, the Format Cell dialog box will pop up.
• Therefore, select Number (from Format Cells options) >> Number (from Category) >> Click on the drop-down option in Decimal places, or you can type the number (e.g., 2).
• Last, click OK and complete this process.

• The final output (17.15) shows two decimal numbers, as (2) has been selected before.

How to Use Excel Functions for Rounding Numbers in Excel

In this part, we will use different Excel functions such as ROUND, ROUNDUP, ROUNDDOWN, MROUND, FLOOR, CEILING, INT, TRUNC, EVEN, and ODD functions for rounding numbers in Excel.

1. Using the ROUND Function for Rounding Numbers to Nearest Integer in Excel

The ROUND function rounds numbers to the nearest integer in Excel. For example, cell E6 contains 17.1456376. Now use the ROUND function to round the number to your required decimal place.

Here, select cell F6 and apply the formula.

Then press Enter, and you will get the value rounded to the nearest integer 17. Use the AutoFill in Excel to get the values for the cells up to F13.

2. Using ROUNDUP Function for Rounding Upward in Excel

In this method, we will use the ROUNDUP function for rounding the numbers upward in Excel. For instance, cell E6 in your dataset contains a fractional number (17.1456376) and now you need to use the ROUNDUP function to round up the fractional number to 18.

Therefore, write this formula in cell F6 and press Enter.

Therefore, you will get the rounded-up number, which is 18.

3. Using ROUNDDOWN Function for Rounding Number Downward in Excel

The ROUNDDOWN function is used to round down any fractional number.

For instance, cell E6 contains fractional data (17.1456376) now you can round down the number to 17 using the ROUNDDOWN function.

Apply the formula below in cell F6 and press Enter to get the required round-down value.

Here, we will get the rounded-down output, which is 17.

4. Using MROUND Function to Round Numbers to Specific Multiple

The MROUND function returns a number that is rounded to a desired multiple.

For example, 17.1456376 is fractional data; now you need rounded data that can be divided by 5. Here, we will use the MROUND function to get the round numbers to a specific multiple.

Apply the formula in cell F6 to round numbers that are multiplied by five (5).

So, the number 17.1456376 will result in 15.

Read More: How to Round to Significant Figures in Excel

5. Rounding Number Down Towards Zero to a Specific Multiple Using FLOOR Function

The FLOOR function is used to round numbers down to the nearest multiple of significance. This function is quite similar to the ROUNDDOWN function. The only difference is that the FLOOR function rounds down the value according to the significance, and the ROUNDDOWN function rounds down the value according to the decimal places given in the formula.

For example, cell E6 contains a number (17.1456376). Now use the FLOOR function to get a rounded value that is multiplied by 2. Therefore, you will get 16 as the output.

So, apply this formula in cell F6 to get the number rounded down and multiplied by 2.

Therefore, the result will be 16.

6. Using CEILING Function to Round Number Up to a Specific Multiple

The CEILING function is quite similar to the FLOOR function. Here, the FLOOR function is used to round numbers down to the nearest multiple of significance, and the CEILING function is used to round up the numbers to the nearest multiple of significance.

Apply the formula below to use the CEILING function in cell F6 to round the number up to zero multiplied by 2 (two).

The output of using the CEILING function will be 18.

7. Rounding Number to Nearest Integer in Excel with INT Function

In this method, we will learn to round numbers to the nearest integer in Excel with the INT function. The INT function contains one argument, which is Number. This argument is the number you want to round down to an integer.

For example, cell C6 contains a number (17.1456376). Here, we will use the INT function to eliminate the decimal places. After using this function, the output will be 17.

Now apply the below formula in cell F6 to round numbers to the nearest integer in Excel.

Lastly, the output is 17.

Read More: How to Round to Nearest Whole Number in Excel

8. Removing Fractional Part of Number Using TRUNC Function

In this method, we will remove the fractional part of a number using the TRUNC function. Here, we will truncate a number to an integer by removing the fractional part of the number.

For example, cell E6 contains data 17.1456376. If you use the TRUNC function on this data, then the final output will be 17, as this function eliminates all the fractional values.

Now, apply the formula in cell F6 to remove the fractional part of the number using the TRUNC function.

Applying the TRUNC function results in 17.1.

9. Rounding Number to the Nearest Even Number Using EVEN Function

The EVEN function is usually used to remove the fractional part of any data and return the value after rounding up the number to the nearest even number.

For instance, E6 contains data 17.1456376. Here, the output will be 18 if you use the EVEN function.

Applying the formula in cell F6 to round the numbers to the nearest even number using the EVEN function:

The result will be 18.

Read More: How to Round Time in Excel

10. Using ODD Function to Round Number to the Nearest Odd Number

The ODD function is quite similar to the EVEN function. In this method, we will use the ODD function to return the numbers rounded up to the nearest odd number.

Therefore, apply the formula in cell F6 to round numbers to the nearest odd number, removing the fractional part.

Here, the result of applying the ODD function is 19.

6 Examples of Rounding in Excel Based on Specific Applications

In this part, we will show you some examples of rounding in Excel based on people’s queries.

Here, we will describe how to round numbers

– to the nearest 0.5/5/10/100/1000
– to the nearest negative numbers
– to the nearest 10/50 cents
– to the nearest hours
– to the nearest 15 minutes/quarter hours
– to the nearest percentages

So, go through the examples below.

Rounding to Nearest 0.5/5/10/100/1000

In this method, we will round numbers to the nearest 0.5/5/10/100/1000 using different functions such as the ROUNDUP, ROUNDDOWN, FLOOR, CEILING, and MROUND functions. The image below shows the variety of applications using these functions.

• Rounding to the nearest 5 means rounding up or rounding down any data to 0.5. For instance, cell B7 contains a number (75785.9775). If you round up or round down this number to the nearest 0.5 then the output will be either 75786 or 75786.5.
• Similarly, rounding to the nearest 5 means rounding up or rounding down any number to the nearest multiple of 5.
• Last, rounding to the nearest 10, rounding to the nearest 100, and rounding to the nearest 1000 means rounding up or rounding down a number, respectively, to the multiple of 10, 100, and 1000.

Click Here to View the Image

Rounding Negative Numbers

In this method, we will learn how to round negative numbers using the ROUND function. Here, the value will be rounded up, and the fractional part will be eliminated. Here we used the ROUND function because this function is most accurate for rounding the negative value.

Select cell F6 and apply the formula to round up the negative number as below.

Here, the final output is -2.

Read More: How to Round up Decimals in Excel

Rounding to Nearest 10/50 Cents in Excel

Here, we will round numbers for currency to the nearest 50/10 cents in Excel using the ROUND and MROUND functions.

The dataset below shows the Store name, Products, Weight, and Price of the products. Here we will round the price of the products nearest to 10 cents and nearest to 50 cents.

For example, the price of a banana is 23 dollars and 25 cents. Here we will round up the price to $23.30, which means 23 dollars and 30 cents, and $23.50 while rounding to 50 cents.

• Initially, apply the formula to round to the nearest 50 cents in cell F6.

Therefore, you will get the value rounded to the nearest 50 cents, which is 23 dollars and 50 cents.

Therefore, suppose the price of an Apple is 21 dollars and 57 cents. Here, the final output after rounding to the nearest 10 cents will be 21 dollars and 60 cents, as this function will round the data after every 10 cents.

• Now apply the MROUND function in cell F6 using the below formula to round up to the nearest 10 cents.

Here the output is 23 dollars and 70 cents.

Read More: How to Stop Rounding in Excel

Rounding to Nearest Hours

Here, we will use the MROUND function for rounding time in Excel to the nearest hours. For example, if it’s 9:44 AM, once you apply this formula, the output will be 10:00 AM as a rounded hour.

So, apply the below formula in cell F6 to get the rounded hour.

Therefore, the rounded time will be 9:00 AM.

Rounding to Nearest 15 Minutes/Nearest Quarter Hour

Rounding time to the nearest 15 minutes or quarter-hour is similar to the previous method. The MROUND function has two arguments: Number and Significance. Here we will show you rounding to the nearest 15 minutes, so the significance will be “0:15” as this part represents 15 minutes.

Now, apply the same formula in cell F6 and get the rounding to the nearest 15 minutes.

Here, the rounded time will be 8:15 AM.

Rounding Percentage in Excel

In this method, we will learn how to round percentages in Excel using the ROUND function. Here, we will use 2 as a num-digit to show two decimals before the percentage.

There are differences between rounding percentages and rounding numbers. Change the format of the dataset to Percentage while rounding the percentage in Excel. Otherwise, the output will be shown in number format. Here we can round up and round down the percentage by using the ROUNDUP and ROUNDDOWN functions.

Apply the function in cell F6 and round the percentage in Excel.

Therefore, the output of the ROUND function is 93%

Here, we also used the ROUNDUP and ROUNDDOWN functions to determine the rounded-up and rounded-down percentages.

Therefore, the outputs are, respectively, 94% and 93%.

Read More: How to Round Excel Data to Make Summations Correct

How to Stop Rounding in Excel

Sometimes, we need to stop rounding in Excel because rounding numbers in Excel can sometimes cause data inaccuracies. In that case, follow the below steps to stop rounding in Excel.

• Initially, select cell range F6:F13 and right-click on the mouse to get the context menu bar.
• Then, select the Format Cells option from the menu bar as below.

• After that, the Format Cells dialog box will pop up, and you can select Number (from Options) >> Number (from category) >> Select 10 from the decimal places to avoid rounding the decimal numbers.
• Last, select OK to complete the process.

• The below output (17.1456376000) will show that the decimal numbers are visible and not rounded in the dataset.

Download Practice Workbook

This article describes all the ways of rounding numbers, time, and currency in Excel. We also learned to round percentages and stop rounding when fractional numbers are necessary. We used different functions such as ROUND, ROUNDUP, ROUNDDOWN, MROUND, FLOOR, CEILING, INT, EVEN, and ODD functions. Excel features like Format cells, Fill Handle, and Decrease Decimal are also used to round numbers.

Rounding data will help you make the data more readable and understandable; it will give you a clearer vision while analyzing the data and make the data look more consistent. Please leave a comment here if there are any queries.

Rounding in Excel: Knowledge Hub

<< Go Back to Number Format | Learn Excel