Edit Article

Two Methods:Using cell formattingUsing formulas

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.

Ad

Method 1 of 2: Using cell formatting

  1. Round in Excel Step 1.jpg
    1
    Enter your data series into your Excel spreadsheet. Save it frequently to avoid losing data.
    Ad
  2. Round in Excel Step 2.jpg
    2
    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.
  3. Round in Excel Step 3.jpg
    3
    Right-click your mouse on any highlighted cell. Scroll down the drop down menu until you find “Format Cells.”
  4. Round in Excel Step 4.jpg
    4
    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.
  5. Round in Excel Step 5.jpg
    5
    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.”
  6. Round in Excel Step 6.jpg
    6
    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.

Method 2 of 2: Using formulas

  1. Round in Excel Step 7.jpg
    1
    Enter the data into your spreadsheet. Save the sheet often as you enter.
  2. Round in Excel Step 8.jpg
    2
    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.
  3. Round in Excel Step 9.jpg
    3
    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.
  4. Round in Excel Step 10.jpg
    4
    Type an equals sign and the word “ROUND” into the field. Make sure to use all capitals.
  5. Round in Excel Step 11.jpg
    5
    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 the number value currently in column A, row 4 to two decimal places.
    • Press “Enter” to activate the formula.
    • Use “zero” as the decimal place if you want to round to the nearest whole number.
  6. Round in Excel Step 12.jpg
    6
    Opt to replace ROUND with ROUNDUP or ROUNDDOWN if you know you want to round up or round down to a certain number of decimal points.
  7. Round in Excel Step 13.jpg
    7
    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]
  8. Round in Excel Step 14.jpg
    8
    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.
    Ad

We could really use your help!

Can you tell us about
singing?
Yes
No
singing
how to sight sing
Can you tell us about
Adobe Photoshop?
Yes
No
Adobe Photoshop
how to use Photoshop CS3
Can you tell us about
nail polish?
Yes
No
nail polish
how to make your nail polish matte
Can you tell us about
Vans shoes?
Yes
No
Vans shoes
different ways to lace Vans shoes
Thanks for helping! Please tell us everything you know about
...
Tell us everything you know here. Remember, more detail is better.
Tips
Provide Details.
Please be as detailed as possible in your explanation. Don't worry about formatting! We'll take care of it. For example:
Don't say: Eat more fats.
Do say: Add fats with some nutritional value to the foods you already eat. Try olive oil, butter, avocado, and mayonnaise.
Submit

Sources and Citations

  1. ↑ http://chandoo.org/wp/2012/09/28/round-numbers-excel-formulas/

Article Info

Featured Article

Categories: Featured Articles | Microsoft Excel

In other languages:

Deutsch: In Excel runden, Español: Cómo redondear en Excel, Português: Como Arredondar Números no Excel, Русский: округлять числа в Excel, Français: Comment arrondir dans Excel

Thanks to all authors for creating a page that has been read 365 times.

Did this article help you?
Yes No

Become
an Author!

Write an Article