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.
To reduce decimal places, follow the steps outlined below.
- Select the cells that contain average points. In this case, you have to select cells B2 to B5.
- Then, go to the “Number” group of the “Home” tab.
- Click the “Decrease Decimal” icon until the selected numbers only get two decimal places.
Then, as shown in the image below, you can get the average points to two decimal places.
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.
Now, in column B, we just need to keep one decimal place.
To do so, follow the steps below.
- Select the cells that you want to format. In this case, you have to select cells B2 to B5.
- 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.
- Right-click and select the “Format cells” command from the popup menu.
- In the Number tab, select “Number” as the category.
- 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.
- Finally, click the “Ok” button.
Then, all the numbers in the selected cells are rounded to one decimal place.
Changing the number of decimal places option is available for the following number format categories as well.
- Currency
- Accounting
- Percentage
- Scientific
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.
You can follow the below steps to format numbers as millions.
- Select the cells that you want to format. In this case, you have to select cells C2 to C5.
- 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.
- Right-click and select the “Format cells” command from the popup menu.
- Select “Custom” as the category.
- 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,,
- Finally, click the “Ok” button.
Then, all the numbers in the selected cells are rounded to the nearest million.
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.
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.
Then, click the mouse and drag the double-pointed arrow to the left side until you see only three decimal places in your numbers.
Then, Excel will round the decimal places up to the visible decimal place.
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:
