This article explains how to round a numeric value stored in an Excel sheet cell using the "Round" formula or formatting features.
Steps
Method 1 of 3: Using the Increase and Decrease Decimal Buttons
Step 1. Enter the data to be rounded into the Excel sheet
Step 2. Select all the cells which contain the values to be rounded
To perform a multiple selection of cells, click on the one placed in the upper left part of the data set to be processed, then drag the mouse cursor in the lower right part of the sheet, until all the cells in question are highlighted.
Step 3. Click on the "Decrease Decimal" button to make sure that fewer decimal places are displayed
This button is characterized by symbols .00 →.0 and is located within the "Number" group of the Home tab (it is the last button to the right of the section).
-
Example:
by clicking on the "Decrease decimal" button the value 4, 36 € will become 4, 4 €.
Step 4. Click on the "Increment Decimal" button to display more decimal places
This button is characterized by symbols ←.0.00 and is located within the "Number" group of the Home tab. This way, numeric values will have higher precision than rounded ones.
-
Example:
by clicking on the "Increase decimal" button the value 2, 83 € will become 2, 834 €.
Method 2 of 3: Using the Round Formula
Step 1. Enter the data to be rounded into the Excel sheet
Step 2. Click on the cell next to the one that contains the value to be rounded
This way, you will be able to insert a formula inside the chosen cell.
Step 3. Enter the keyword "Round" into the "fx" field
It is located at the top of the worksheet. Type the equal sign followed by the word "Round": = ROUND.
Step 4. Now open a round bracket after the "Round" keyword
At this point, the contents of the "fx" field should look like this: = ROUND (.
Step 5. Click on the cell that contains the value to be rounded
In this way, the cell name (for example A1) will be automatically inserted into the formula. If you clicked on cell "A1", the formula displayed in the "fx" field should look like this: = ROUND (A1.
Step 6. Enter a comma followed by the number of decimal places that should be used to perform the rounding
For example, if you have decided to round the number contained in cell A1 to two decimal places, the formula displayed in the "fx" field will be the following: = ROUND (A1, 2.
- Use the value 0 as the number of decimal places to use if you want the cell value to be rounded to the nearest whole number.
- Use a negative value to round the number to the nearest multiple of 10. For example, using the formula = ROUND (A1, -1 the cell contents will be rounded to the nearest multiple of 10.
Step 7. Complete the formula by closing the parenthesis
At this point, the example formula (for cell "A1" which uses two decimal places for rounding) will be as follows: = ROUND (A1, 2).
Step 8. Press the Enter key
The formula created will be executed immediately and the result will be displayed rounded inside the selected cell.
- You can substitute the Round function for Round. For. Ec or Round. For. Dif if you want to round up or down a value to a specific number of decimal places.
- Use the Round Multi function to round a value to the nearest multiple of the number specified in the formula.
Method 3 of 3: Use the Format Cells Menu
Step 1. Enter the data to be rounded into the Excel sheet
Step 2. Select all the cells which contain the values to be rounded
To perform a multiple selection of cells, click on the one placed in the upper left part of the data set to be processed, then drag the mouse cursor in the lower right part of the sheet, until all the cells in question are highlighted.
Step 3. Select any of the highlighted cells with the right mouse button
A context menu will be displayed.
Step 4. Click on the Number Format item or Cell format.
The name of this option varies based on the version of Excel you are using.
Step 5. Click on the Number tab
It is located at the top or along one side of the dialog that appears.
Step 6. Click on the Number item listed in the "Category" box
It is located on one side of the screen.
Step 7. Select the number of decimal places you want the rounding to take place
Click on the down arrow button located inside the "Decimal Places" text field to view the list of available options, then click on the one you want to select.
-
Example: To round the value 16, 47334 to a single decimal, you will need to select the option
Step 1. from the indicated menu. The value in question will be rounded to 16.5.
- Example: To round the value 846, 19 to the nearest whole number, you must select the option 0 from the indicated menu. In this way, the result of the rounding will be 846.
Step 8. Click the OK button
It is displayed at the bottom of the window. The contents of all currently selected cells will be rounded according to the selected number of decimal places.
- To apply the chosen settings to all numbers in the worksheet (including all those you will enter in the future), click anywhere on the sheet to cancel the current cell selection, then click on the tab Home located at the top of the Excel window, click on the drop-down menu displayed in the "Number" group, then select the option Other number formats. At this point, set the number of "Decimal places" you want and click on the button OK to make the chosen option the default for the file under consideration.
- In some versions of Excel, you will need to click on the menu Format, then on the voice Cells and finally you will have to access the tab Number to find the "Decimal places" option.