How to Round Up to 2 Decimal Places in Excel (with Calculator)

Get FREE Advanced Excel Exercises with Solutions!

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.

Dataset to Round Numbers to 2 Decimal Places in Excel


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.

Round Numbers to 2 Decimal Places Using Excel Format Feature

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.

Number Formatting to Round Up to 2 Decimal Places in Excel

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.

Custom Formatting to Round Up to 2 Decimal Places in Excel

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.

Result for applying Custom Format to Round Up to 2 Decimal Places

  • 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:

ROUND(number, num_digits)

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.

Round to 2 Decimal Places Numbers Using Formula in Excel

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).

Read More: How to Get 2 Decimal Places Without Rounding in Excel


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.

ROUNDUP(number, num_digits)

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.
=ROUNDUP(F5,2)
  • Then, press ENTER.

Applying ROUNDUP Function to Round Up to 2 Decimal Places in Excel

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.

ROUNDDOWN(number, num_digits)

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.
=ROUNDDOWN(F5,2)
  • Then, press ENTER.

Use of ROUNDDOWN Function to Round 2 Decimal Places in Excel

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.

Read More: How to Set Decimal Places in Excel with Formula


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.

TRUNC(number, num_digits)

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-
=TRUNC(F5,2)
  • Secondly, press ENTER and you’ll have the same result as found in the previous method.

Employing TRUNC Function to Round Up to 2 Decimal Places in Excel

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.

Steps:

  • Firstly, you have to choose the Developer tab >> then select Visual Basic.

Use of Excel VBA to Round to 2 Decimal Places

  • 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

Code Breakdown

  • 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.


Calculator

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.

Creating Calculator to round to 2 decimal places in Excel

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.


Conclusion

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.


Related Articles


<< Go Back to Round Up Decimals | Rounding in Excel | Number Format | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Shakil Ahmed
Shakil Ahmed

My name’s Shakil. My articles are targeted to support you in enriching knowledge regarding different features related to Microsoft Excel. I am fond of literature, hope some knowledge from them will help me providing you some engaging articles even though some weary technical terms.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo