How to Round Numbers in Excel Without Formula?

by

Rounding numbers is a common task for many Excel users, and there are many ways you can achieve it. 

One of the easiest and most used methods is to use a formula to round numbers (either up or down or to a specific number), but not everyone is comfortable with formulas.

Or you may have other constraints where you don’t want to use a formula to round values but still want it done.

Well, if you’re creative enough, you can always find a workaround in Excel.

In this short article, I’ll show you a couple of techniques to round numbers in Excel without using formulas.

Method 1 – Using Decrease Decimal Icon to Round Numbers in Excel

In most cases, when Excel users want to round numbers, they want to reduce the number of decimal places in a number.

This can easily be achieved using the Decrease Decimal option in the ribbon in Excel.

Let me show you how this works with an example.

In column B of the table below, we have the average points of students. Now, we need to round all average points to two decimal places.

Data set where we need to round numbers without using formula

To reduce decimal places, follow the steps outlined below.

  1. Select the cells that contain average points. In this case, you have to select cells B2 to B5.
  2. Then, go to the “Number” group of the “Home” tab.
  3. Click the “Decrease Decimal” icon until the selected numbers only get two decimal places.
click on the decrease decimal icon in the ribbon

Then, as shown in the image below, you can get the average points to two decimal places.

the values have now been rounded off to two decimal points

Note: When you use this option to round numbers in Excel, it only changes how these numbers are shown to you in the cell in Excel. The actual value in these cells remains the same. This is also a big advantage of this method over the formula method, which ends up changing the actual value in the cell.

Also read: How to Round to Nearest 100 in Excel?

Method 2 – Using the Number Formatting to Round Numbers in Excel

Another fast and easy way to round numbers is by using Custom Number formatting.

The table below shows the number of items sold in each branch. The branch names are listed in column A, and the number of items sold in millions shows in column B.

quantity data set that we want to round off and show in millions

Now, in column B, we just need to keep one decimal place.

To do so, follow the steps below.

  1. Select the cells that you want to format. In this case, you have to select cells B2 to B5.
  2. Then, open the “Format cells” dialog box. You can open the Format cells dialog box using one of the following methods.
    • Using the “Control + 1” shortcut key
    • Clicking the expand button of the Home tab’s number group.
click on the dialog box launcher in the ribbon
  • Right-click and select the “Format cells” command from the popup menu.
right click and then click on the format cells option
  1. In the Number tab, select “Number” as the category.
click on the number option in the number tab
  1. Change the number of decimal places as you wish. In this example, we need to round the number to one decimal place. So, we can change the number of decimal places to 1.
change the decimal places value in the format cells dialog box to round numbers without using a formula
  1. Finally, click the “Ok” button.

Then, all the numbers in the selected cells are rounded to one decimal place.

the numbers have been rounded off to one decimal place

Changing the number of decimal places option is available for the following number format categories as well.

So, you can select the relevant category as per your number type and do the rounding.

For example, if you want to round the decimal places of a percentage, you can select the percentage from the category and adjust the decimal places.

Also read: Round Up to the Nearest Whole Number in Excel (Formulas)

Method 3 – Using the Custom Number Formatting to Round Numbers in Excel

The two methods listed above can be used to round decimal places. 

But, in reality, there are times when we have to round numbers to places other than decimal points. You may have to round to the closest thousand, million, and so forth in some cases.

The table below shows the number of quantities sold in each branch.

The branch is shown in column A, and the quantity sold is shown in column B.

Column C is required to display the number of units sold in millions, so we need to round the values in column C to show these values as millions.

data set where we need to show quantity in millions

You can follow the below steps to format numbers as millions.

  1. Select the cells that you want to format. In this case, you have to select cells C2 to C5.
  2. Then, open the “Format cells” dialog box. You can open the Format cells dialog box using one of the following methods.
    • Using the “Control + 1” shortcut key
    • Clicking the expand button of the Home tab’s number group.
click on the dialog box launcher
  • Right-click and select the “Format cells” command from the popup menu.
right click and then click on the format cell option
  1. Select “Custom” as the category.
click on the custom option in the format cells dialog box
  1. Use commas to round numbers.
    • Adding one comma at the end of the format code will round the number to the nearest thousand. 
    • Adding two commas at the end of the format code will round the number to the nearest million. 
    • Adding three commas at the end of the format code will round the number to the nearest billion. 

In this case, we want to round numbers to the nearest million. Therefore, we simply need to add two commas at the end of the format code.

Tip: If you want to show two decimal places in your number format, add a dot and two zeros (Change the number of zeros based on the number of decimal places you want)  before you add commas at the end of the format code.

So our custom format code would be:

#,##0.00,,
enter the custom format with two trailing commas
  1. Finally, click the “Ok” button.

Then, all the numbers in the selected cells are rounded to the nearest million.

numbers have been rounded off to show values in millions
Also read: Round UP or DOWN to Nearest 5 in Excel (Formula)

Method 4 – Reduce the Column Width to Round Numbers in Excel

Sometimes you need an extremely quick way to display Excel numbers with only a few decimal places.

The best option is to change the column’s width.

The table below shows the average points of four students. You wish to quickly display all numbers up to three digits.

data set where we want to round values in column B

Simply keep the cursor at the right side edge of the numbers column’s column header.

In this example, you have to keep the cursor on the right side edge of column B’s header. Your cursor will then become a double-pointed arrow, as you will see.

place the cursor at the column edge in the label and drag to reduce the column width

Then, click the mouse and drag the double-pointed arrow to the left side until you see only three decimal places in your numbers.

drag to reduce the column width

Then, Excel will round the decimal places up to the visible decimal place.

numbers adjust to fit in the given column width by rounding these numbers

This strategy only works if all of the numbers in the adjustment column have the same number of digits to the left of the decimal point. In the example above, each number in column B has two digits. As a result, changing the column width can be used to round decimal places.

Custom number formatting is the only way that can be applied to both rounding decimal places and rounding numbers to the nearest thousand, million, and so on. However, other methods are simpler to use than custom number formatting.

Thus, to round numbers in Excel without formulas, choose from any of the above methods.

Also read: How to Remove Decimals in Excel?

Scenario Where It’s Better to Use Formula to Round Numbers

While I have shown you four methods you can use to round numbers without losing a formula in Excel, there are a few scenarios where using a formula would be the right way to go.

When Rounding to a Specific Number (say round to 5 or 20)

And that is when you want to round to a specific number (such as round to the nearest 5 or 20).

While this can easily be done using a formula, it isn’t possible to do this using the decimal decrease option or custom number formatting.

When You Want All Numbers to Rounding Up or Round Down

All the above methods work by rounding numbers to the nearest value. So 3.1 is rounded to 3 and 3.7 to 4.

With formulas, you can use the ROUNDUP/ROUNDDOWN or CELING/FLOOR function to always round up or always round down.

When You want to Use the Rounded Values in Calculations

In the four methods that I’ve covered to round numbers without using a formula, it rounds the number but doesn’t change the actual value in the cell.

So, while you see the rounded value in the cell, in the back end, the value remains what it was earlier.

In case you want to actually round off the value so that the rounded value can then be used in other formulas, it is better to use a formula to round these values instead of using the formatting method covered above.

I hope you found this article useful, and learned some simple ways to round numbers without using formulas.

Do let me know your thoughts in the comments section. If you think there is any method that I have missed, please let me know.

Other Excel articles you may also like:

I am a huge fan of Microsoft Excel and love sharing my knowledge through articles and tutorials. I work as a business analyst and use Microsoft Excel extensively in my daily tasks. My aim is to help you unleash the full potential of Excel and become a data-slaying wizard yourself.

Leave a Comment