For different purposes, you may need to present a number rounded up to 2 decimal places. Our agenda for today is to show you several ways to round numbers to 2 decimal places in Excel. For conducting the session, we’re going to use Microsoft 365 version. Here, you can use your preferred one.
Before diving into the big picture, let’s get to know about today’s workbook. Actually, in this workbook, we have a basic table for students and their scoring out of 1000. Basically, using this table we will see how to round numbers to 2 decimal places in Excel. Furthermore, to keep it simple we have brought this table to you, where the Average score is in decimal values. But, in a real scenario, not all-time averages will be fraction values and at the same time, your data sets may have other fraction values.
1. Using Number Formatting in Excel to Round Up to 2 Decimal Places
Eventually, Excel provides a Format feature, using which you can round values easily. For the purpose of comparing the original and rounded values, we have copied the original values (Average column) to another column named Rounded Average.
Now, select any of the values and explore the Number section for that one. Here, for a number having more than 2 decimal places (unless you have something predefined) you should see it’s in the format of the General category.
- Now, from the Home tab >> you will find the Number section. From there you can format your values.
- Then, Click on the List icon next to the category name. Here, you will find a number of categories.
- After that, select the category called Number.
As a result, the value will be rounded to 2 decimal places and the format will be changed to Number from General.
Now, let’s try another way of rounding up to 2 decimal points.
- Firstly, explore the Number section again. Here, you will find the Decrease Decimal option.
- Secondly, click on the Decrease Decimal for the number of times you need to reach 2 decimal places. In this example, we clicked a couple of times since we had 7 decimal places.
Lastly, the value will be rounded to 2 decimal places and here, we have changed the format to Number from General.
- Now, you can use any of the Number Formatting techniques for the rest of the values in the example sheet.
2. Applying Custom Formatting to Round Up to 2 Decimal Places
Similarly, Excel provides you the option to choose your customized format to round the numbers to 2 decimal places. Now, let’s talk about the steps.
- Firstly, select the data. Here, we have selected G5:G10 cells.
- Secondly, press CTRL+1.
As a result, a new window named Format Cells will appear.
- Now, go to the Custom option.
- Then, we have selected 0.00 since we need numbers up to 2 decimal places. Here, you can see the format immediately as Sample.
- After that, hit ENTER or click OK.
Finally, we got the numbers up to 2 decimal places successfully.
- Now, let’s roll back a bit and for this again select the data.
- Then, from the Home tab >> explore the Number section and click the List icon next to the category.
Here, you will find a number of options (did it earlier also). At the bottom, you will find an option called More Number Formats.
- Subsequently, click More Number Formats, it will pop up a dialog box in front of you.
Now, in this dialog box, you will find an option called Custom.
- So, selecting that option, you can insert your suitable format in the Type box, though Excel has some predefined format created evaluating several case studies.
- Here, we have inserted “#.##” as a format.
- Then, click on OK (or hit ENTER).
It worked perfectly. Similar to this you can form your format for any type of input in Excel. But, for the other cells, you don’t need to type the format again and again. You may just choose the format you have inserted.
- Firstly, select the cell you want to format, and go to the Format Cells dialog box again.
- Usually, your inserted formula will be at the bottom of the format type. So, Scroll Down and select the format.
- Now, you can use any of the Custom Formatting techniques for the rest of the values in the example sheet.
3. Using ROUND Function for Up to 2 Decimal Places in Excel
For rounding a number, you can use a function called the ROUND function. Furthermore, you can know more about the ROUND function by clicking the above link.
Here, the syntax for this function is:
number: The number you want to round.
num_digits: The number of digits to which the number should be rounded.
Since our agenda is to round numbers in Excel by 2 decimal places, in the following examples, we will use 2 in the placeholder for num_digits.
Here, we have written the ROUND function for the average score of Jasmin. From a number of 4 decimal places, it provided a number to 2 decimal places.
Furthermore, if you notice, you will see in the Average column the number was 748.6667, but in the Rounded Average column, the value is 748.67.
Hope you can remember, whenever the leaving digit is going to be more or equal 5, 1 is added to the rightmost remaining digit. The ROUND function adapts the same method.
- Now, for the rest of the values, write the function or you can exercise the Excel Autofill Feature. Since it’s time to practice, better write the function.
As a result, you will get all the rounded averages up to 2 decimal places.
Here, we’re explaining the 2nd cell whose value was 749.3333. When we wrote the ROUND function for converting the number to the format of 2 decimal places, it gave 749.33, as the last leaving digit was 3 (less than 5).
4. Applying ROUNDUP Function to Round Up to 2 Decimal Places
Similar to the ROUND function you can use the ROUNDUP function. Furthermore, for syntax, you will not find any dissimilarities between the ROUND and ROUNDUP function.
number: The number you want to round up.
num_digits: The number of digits to which the number should be rounded up.
- Now, write the function in the G5 cell.
- Then, press ENTER.
Here, you might wonder why you should use ROUNDUP instead of ROUND since the syntax is the same and the result is the same as well!
Actually, the ROUNDUP function returns numbers in its upper limit or ceiling closest to the original number.
Here, our number was 748.6667 and our aim was to get up to 2 decimal places. Writing the formula, we have got 748.67 which is higher than the original 748.6667
Now, let’s observe another value here. Similarly, by using the ROUNDUP function for the 749.3333, we have got 749.34.
- Now, do the same for the rest of the values.
Finally, you will see all the rounded values.
Read More: How to Round up Decimals in Excel
5. Use of ROUNDDOWN Function to Round up to 2 Decimal Places
You can round a number by applying the ROUNDDOWN function. Here, the name says the whole story, this function will return the number toward the closest value in a downwards direction. Moreover, the syntax is still the same as ROUND or ROUNDUP.
number: The number you want to round down.
num_digits: The number of digits to which the number should be rounded down.
- Now, write the formula in the G5 cell.
- Then, press ENTER.
As a result, you will find a number like this 748.66. Actually, the ROUNDDOWN function will return numbers in such a way that the decimal values are closer to 0.
Where for the ROUND function, 2 decimal places the format value of 748.6667 was 748.67, for the ROUNDDOWN function it’s 748.66.
- After that, write the functions for the rest of the values for better understanding.
6. Inserting TRUNC Function to Round Up to 2 Decimal Places
One function that may come to your mind is the TRUNC function. Here, the syntax for TRUNC is also similar to the ROUND.
number: The number you want to truncate.
num_digits: The precision of the truncation.
The num_digits parameter for the TRUNC function is optional. If you don’t provide it, it will be 0 by default.
- Firstly, in cell G5, type-
- Secondly, press ENTER and you’ll have the same result as found in the previous method.
The TRUNC function also aims to provide decimal place values close to 0. This function will provide you with a value that is lesser than the original value.
- Lastly, do the same for the rest of the values and you will get all the rounded values.
7. Using Excel VBA to Round to 2 Decimal Places
Here, you can employ the VBA code to round a number to 2 decimal places. The steps are given below.
- Firstly, you have to choose the Developer tab >> then select Visual Basic.
- Now, from the Insert tab >> you have to select Module.
- At this time, you need to write down the following Code in the Module.
Sub Rounding_upto_2_decimal() Dim my_row As Integer Dim my_work_sheet As Worksheet Set my_work_sheet = Worksheets("VBA") For my_row = 5 To 10 my_work_sheet.Cells(my_row, 7).Value = Application.WorksheetFunction.Round(Cells(my_row, 7).Value, 2) Next my_row End Sub
- Here, we have created a Sub Procedure named Rounding_upto_2_decimal. Also, we used the dim statement to define a variable my_work_sheet as Worksheet.
- Next, we used the Set statement to set the worksheet named “VBA” using the Worksheets object in my_work_sheet.
- We also took a variable my_row as an integer and applied a For loop for my_row which will go from the 5th to the 10th row. And 7 is our column number.
- Then, we used Round to get the numbers rounded.
- Now, Save the code then go back to Excel File.
- Then, from the Developer tab >> you have to select Macros.
- Now, you need to select Macro (Rounding_upto_2_decimal) and click on Run.
At this time, you can see the rounded average.
You can use today’s practice workbook as a calculator to count round numbers. Here, you will find a sheet called Calculator. So, explore the sheet.
We have set the calculator in such a way that you can choose your desired number of decimal places. Just select the method you want to use from the drop-down Choose Method section. Then, insert your number and preferred decimal places. It will provide you with the result.
Furthermore, we have done one example for you.
Download Practice Workbook
You are welcome to download the practice workbook from the link below.
That’s all for the session. Here, we have tried listing out several ways to round numbers to 2 decimal places in Excel. Hope that will help you. Feel free to comment if anything seems difficult to understand. Let us know which of the methods you have liked most and will use.
You can also let us know your way to round numbers to 2 decimal places in Excel.
- How to Remove Decimals in Excel with Rounding
- How to Remove Decimals Without Rounding in Excel
- How to Stop Excel from Rounding Up Decimals
- How to Round Percentages in Excel
- Excel VBA: Round to 2 Decimal Places
- How to Round to Nearest 10 Cents in Excel
- How to Round Off to Nearest 50 Cents in Excel
- How to Stop Rounding in Excel
- How to Stop Excel from Rounding Large Numbers