- Use the function ROUND(number, num_digits) to round a number to the nearest number of digits specified.
- Use other functions like ROUNDUP and ROUNDDOWN to change the rounding method.
- Use cell formatting to display numbers as rounded while maintaining the original value.
The ROUND Function
Enter the data into your spreadsheet. Rounding numbers has plenty of useful applications! For example, if you’re tracking your bills in Excel, you can round the values to integer numbers to see a simpler view of your purchases.
Click a cell next to the one you want to round. This allows you to enter a formula into the cell.
Type =ROUND into the “fx” field. The field is at the top of the spreadsheet. The equal sign indicates that you’re creating a formula rather than typing text.
Type an open parenthesis after “ROUND.” The content of the “fx” box should now look like this: =ROUND(.
Click the cell that you want to round. This inserts the cell's location (e.g., A1) into the formula. If you clicked A1, the “fx” box should now look like this: =ROUND(A1.
Type a comma followed by the number of digits to round to. For example, if you wanted to round the value of A1 to 2 decimal places, your formula would so far look like this: =ROUND(A1,2. Use 0 as the decimal place to round to the nearest whole number. Use a negative number to round by multiples of 10. For example, =ROUND(A1,-1will round the number to the next multiple of 10.
Type a closed parenthesis to finish the formula. The final formula should look like this, using the example of rounding A1 to 2 decimal places: =ROUND(A1,2).
Press ↵ Enter or ⏎ Return. This runs the ROUND formula and displays the rounded value in the selected cell. Now you’re ready to SUM some numbers or create a chart. You can copy this formula as needed to round other numbers to the same specification.
Other Rounding Functions
Use ROUNDUP to round a number up. The ROUNDUP function uses the same parameters as the ROUND function, so you can round up to a specified number of digits. For example, =ROUNDUP(A1, 2) would round up the number in A1 to the nearest hundredths place. If A1 were 10.732, it would round up to 10.74.
Use ROUNDUP to round a number down. The ROUNDDOWN function uses the same parameters as the ROUND function, so you can round down to a specified number of digits. For example, =ROUNDDOWN(A1, 2) would round down the number in A1 to the nearest hundredths place. If A1 were 10.732, it would round up to 10.73.
Use MROUND to round a number to a nearest multiple. The MROUND function allows you to input a specific multiple that you want to round to. For example, =MROUND(A1, 15) would round the number in A1 to the nearest multiple of 15. If A1 were 10.732, it would round to 15.
Use EVEN to round up a number to the nearest even integer. The EVEN function only has one argument, the number you’re rounding. For example, =EVEN(A1) would round the number in A1 up to the nearest even integer. If A1 were 10.732, it would round to 12.
Use ODD to round up a number to the nearest even integer. The ODD function only has one argument, the number you’re rounding. For example, =ODD(A1) would round the number in A1 up to the nearest odd integer. If A1 were 10.732, it would round to 11.
Increase/Decrease Decimal Buttons
Enter the data into your spreadsheet. After making a spreadsheet in Excel, getting the formatting right requires knowing the formatting tools. Note that this method doesn’t change the actual value in the cell, only how it appears in the spreadsheet.
Highlight any cell(s) you want rounded. To highlight multiple cells, click the top left-most cell of the data, then drag your cursor down and to the right until all cells are highlighted.
Click the Decrease Decimal button to show fewer decimal places. It's the button that says .00 → .0 on the Home tab on the “Number” panel (the last button on that panel). Example: Clicking the Decrease Decimal button would change $4.36 to $4.4.
Click the Increase Decimal button to show more decimal places. This gives a more precise value (rather than rounding). It's the button that says ←.0 .00 (also on the “Number” panel). Example: Clicking the Increase Decimal button might change $2.83 to $2.834.
Cell Formatting
Enter your data series into your Excel spreadsheet. Note that this method doesn’t change the actual value in the cell, only how it appears in the spreadsheet.
Highlight any cell(s) you want rounded. To highlight multiple cells, click the top left-most cell of the data, then drag your cursor down and to the right until all cells are highlighted.
Right-click any highlighted cell. A menu will appear.
Click Number Format or Format Cells. The name of this option varies by version.
Click the Number tab. It's either on the top or side of the window that popped up.
Click Number from the category list. It's on the side of the window.
Select the number of decimal places you want to round to. Click the down-arrow next to “Decimal places” to reduce the number of decimal places and round the numbers down. Example: To round 16.47334 to 1 decimal place, select 1 from the menu. This would cause the value to be rounded to 16.5. Example: To round the number 846.19 to a whole number, select 0 from the menu. This would cause the value to be rounded to 846.
Click OK. It's at the bottom of the window. The selected cells are now rounded to the selected decimal place. To apply this setting to all values on the sheet (including those you add in the future): Click anywhere on the sheet to remove the highlighting. Click the Home tab at the top of Excel. Click the drop-down menu on the “Number” panel. Select More Number Formats. Set the desired “Decimal places” value, then click OK to make it the default for the file. In some versions of Excel, you'll have to click the Format menu, then Cells, followed by the Number tab to find the “Decimal places” menu.
Comments
0 comment