be more attractive: How to Round in Excel

samedi 17 janvier 2015

How to Round in Excel

Microsoft’s Excel spreadsheet program is an exceptional program for formatting and listing important data. If you want to display round numbers, you can round numbers in Excel in two ways: with cell formatting or with formulas. Cell formatting is best when you want to round the numbers in a box, while formatting is best when you want to reference other data in the sheet.

Steps

Using Cell Formatting

Enter your data series into your Excel spreadsheet. Save it frequently to avoid losing data.

Highlight any cell(s) you want rounded. For multiple cells, highlight by clicking on the top left-most cell of the data and dragging your cursor down and to the right until you’ve reached the bottom right-most cell. The cells you’ve highlighted should automatically shade if you’ve highlighted correctly.

Right-click your mouse on any highlighted cell. Scroll down the drop down menu until you find “Format Cells.”

Ensure you are in the “Number” tab in the formatting dialogue box. Then, choose “Number” in the list on the left hand side of the box.

Find the place where it says “Decimal Places.” Click on the arrow button, up or down, until you get to the desired amount of decimal places.
- For instance, if you want to round the number 16.47334 to 1 decimal place, make sure the “Decimal Places” box shows “1.” After clicking “Ok,” the number should read “16.5.”
- For example, if you want to round the number 846.19 to a whole number, make sure the “Decimal Places” box shows “0.” After clicking “Ok,” the number should read “846.”

Click “Ok” to apply your changes and return to your sheet.
- In order to ensure that this change is made with the entire sheet going forward, return to the sheet and click anywhere to remove highlighting. Click the “Format” menu at the top and choose “Cells.”
- Repeat the action, choosing the Number tab and the Number section. Then, ensure the Decimal Places number is listed as desired.

Using Formulas

Enter the data into your spreadsheet. Save the sheet often as you enter.

Create a new column to show the rounded numbers in the field. Click on the blank cell next to the number you want to round.

Bring your cursor to the blank field next to the “fx” button on your home tab. You can also press the “fx” button if you want help building your formula.

Type an equals sign and the word “ROUND” into the field. Make sure to use all capitals.

Start an open parenthesis. Type the cell location where the original data is listed, such as A1, followed by a comma and the number of decimal places you want to round. Close your parenthesis.
- For example, your formula may appear as “=ROUND(A4, 2)” and it will round up by two decimal places in column A, row 4.
- Press “Enter” to activate the formula.
- Use “zero” as the decimal place if you want to round to the nearest whole number.

Opt to replace ROUND with ROUNDUP or ROUNDDOWN if you know you want to round up or round down by a certain number of decimal points.

Use a negative number to round by multiples of 10. For example, “=ROUND(A4,-1)” will round the number into the next multiple of 10.
- Similarly, the formula “MROUND” will round to the nearest multiple of any specified number. If you want to round to the nearest multiple of two or five, this is a great formula to use.^[1]

Consider using the same formula on a whole column. Select the cell where you just made your first round formula. Click and drag your cursor on the small box in the lower right hand corner. Drag it down to copy your formula to cells in the same column.
- Excel should automatically adjust the location based on the relative row, but it should not change your specified number of decimal places.