Contents:
Methods for Rounding Numbers in Excel: Cell Format
Rounding using the cell format in Excel is a convenient way to change the display of numbers without changing their actual values. This is especially useful when preparing reports or presentations where an aesthetically pleasing presentation of data is required.
It is important to remember that if you refer to the rounded value in formulas, Excel uses the original number. For example, if you round 44.7 to 45, calculations such as 45 + 5 will return 49.7 instead of 50.
Excel's rounding rules are based on standard mathematical principles: numbers ending in 1-4 are rounded down, and numbers ending in 5-9 are rounded up.
Step-by-step instructions for rounding numbers
To round numbers, follow these steps:
1. Select the range of cells with the numbers you want to round.
2. Go to the Home tab and click the arrow next to the formats field.
3. In the window that opens, select "Other number formats."
4. In the cell format, change the General format to Number, Currency, or Accounting, as rounding is not possible with the General format.
5. Set the number of decimal places to 0 and click OK.
After completing these steps, you will get integers. For example, 17560.42 will be rounded to 17560, and 5149.8 will be rounded to 5150.
A quick way to round in Excel
There is a faster method of rounding. Select the original values, then on the Home tab, click the Decrease Decimal button as many times as there are digits after the decimal point.
This method will automatically change the cell format to numeric and round the values according to the same rules as before.
Using the function ROUND in Excel
The ROUND function in Excel is an indispensable tool for processing numerical data. It is useful when you need to round values for further calculations, following the mathematical rules: numbers from 1 to 4 are rounded down, and from 5 to 9 - up.
Before working with the function, select a separate column for rounded values. This will preserve the original data and avoid confusion.
There are two simple methods to open the function entry window. The first is to go to the Formulas tab and select the Insert Function option. The second is by clicking the "fx" button in the formula bar, which is accessible from any table tab.
After performing one of these actions, the Formula Builder window will appear. In it, using the search bar, we find the ROUND function and click "Insert function".
Now a window will open where you need to fill in the function arguments:
- "Number" is the cell address or value to be rounded.
- "Number_of_digits" — the number of decimal places or the ordinal number of the digit to which the number should be rounded.
Fill in the required arguments. To do this, select the cell with the number, in our example it is B3, and transfer the value to the formula builder. As a result, the formula will look like this: fx=ROUND(B3).
Now we move on to the "Number_of_digits" argument. If we want to round to a whole number, we put 0, which means there are no values after the decimal point.
The formula for rounding looks like this: fx=ROUND(B3;0). Click "Done".
As a result, the rounded integer value will be displayed in the selected cell. To apply rounding to other values, simply drag the formula down.
Excel will round the number 17560.42 to 17560, and 5149.8 to 5150.
To round numbers to thousands, specify -3 in the "Number_of_digits" field. In this case, 17560.42 will be rounded to 18000, and 5149.8 will be rounded to 5000.
To remove extra zeros after the decimal point, select both values and go to the "Home" tab. Then click the "Reduce Bit Depth" button.
Overview of the ROUNDUP and ROUNDDOWN functions in Excel
The ROUNDUP and ROUNDDOWN functions are important for Excel users because they allow you to round numbers according to your desired rules. These tools are especially useful when you want to control the rounding process without relying on standard mathematical rules.
Unlike traditional rounding methods, where numbers from 1 to 4 are rounded down and 5 to 9 are rounded up, the ROUNDUP and ROUNDDOWN functions allow the user to decide which way to round. This provides greater flexibility in managing data.
To use one of these functions, you must first select the cell in which you want the result. Next, open the function dialog box using one of the following methods:
- Go to the "Formulas" tab and click "Insert Function."
- Click the "fx" icon in the link bar, accessible from any table tab.
After opening the formula building window, fill in the arguments: "Number" and "Number_of_digits". These parameters work similarly to the ROUNDUP function.
- "Number" — specify the cell with the data, for example, B3.
- "Number_of_digits" - set to 0 if you want to round to a whole number.
In the reference bar, the function will be represented as: fx=ROUNDUP(B3;0). After filling in, click the "Done" button.
The rounding result will show an integer value, for example, 17561, which confirms that the ROUNDUP function works by rounding the number up. You can drag the result down for other values, and the function will apply the same rules to them.
Now let's look at the ROUNDDOWN function, which works on the opposite principle, rounding all values down, regardless of their digit after the decimal point.
To use this function, also call the Formula Builder window, find ROUNDDOWN and fill in the arguments in the same way as in the previous case.
In the reference line, the function will look like this: fx=ROUNDDOWN(B3;0). After that, click "Done".
With this function, you get an integer, which, for example, might be 5149, which illustrates how ROUNDDOWN rounds down, despite the rules of mathematical rounding.
1. Can these functions be used to round to tenths or hundredths?
Yes, you can set the "Number_of_digits" value to -1, -2, etc. to round to tenths or hundredths.
2. What are the main differences between ROUND, ROUNDUP, and ROUNDDOWN?
The ROUND function rounds a number correctly, while ROUNDUP always rounds up and ROUNDDOWN always rounds down.
Understanding the ROUND Function in Excel
The ROUND function in Excel is designed to round numbers to a specified precision, with the result being a multiple of the selected value. This is useful when the user needs to determine how exactly to round a number.
For example, rounding the number 49.5 with an accuracy of 2 gives 50, while using an accuracy of 7 gives 49. This allows for flexible rounding control depending on the task.
To use this function, open an empty cell and launch the function entry window, as in the previous cases. Find ROUND and click "Insert Function."
In the window that opens, you will need to fill in the function arguments:
- "Number" is the cell or value to be rounded.
- «Precision» is the value by which the rounded number should be divided.
Fill in the argument fields. For "Number", select the value from cell B3. For "Precision", enter 5 so that the resulting number is divisible by 5 without a remainder.
As a result, the function will look like this: fx=ROUND(B3;5). Click Finish to complete.
You'll then get a rounded value that you can drag down to apply the formula to other values. For example, the numbers 17560 and 5150 will be rounded to 5, both of which are divisible by 5.
It's worth noting that the ROUND function, like its ROUNDUP and ROUNDDOWN counterparts, doesn't follow traditional rounding rules: numbers 1 through 4 are rounded down, and numbers 5 through 9 are rounded up.
As for Google Sheets, all the mentioned rounding methods are available there, but with one important condition: Google Sheets does not have a built-in formula builder, so you will have to enter them manually. Examples of formulas can be as follows:
- fx=ROUND(17560,42;0);
- fx=ROUNDUP(17560,42;0);
- fx=ROUNDBOTTOM(17560.42;0);
- fx=ОКРУГЛТ(17560,42;7).
Excel and Google Sheets: from beginner to PRO in 30 days
Want to become a master of Excel and Google Sheets? Learn how to automate your work and reduce the time spent on reports!
Learn more