Mukesh Dipto
This tutorial will explain how to set decimal places in Excel with a formula. Sometimes, we may need to get values up to a fixed decimal place. For instance, we have a dataset that contains values with five decimal places. But, we want to limit the values only up to two decimal places. We can do this in several ways. In this article, we will use a unique dataset to illustrate the methods of this task.
Throughout this article, we will show 5 effective ways to set decimal places in Excel with a formula. To illustrate the methods, we will use the following dataset. The dataset contains the GPA of five students. In each method of this article, we will show how to set the values of GPA up to two decimal places in excel with a formula.
1. Using Excel ROUND Function to Set Decimal Places
First and foremost, we will use the ROUND function to set decimal places in Excel with a formula. The ROUND function rounds a number to the number of digits specified. The function allows you to round numbers up or down. Let’s see the steps to use the ROUND function in this method.
STEPS:
- To begin with, select cell D5.
- In addition, insert the following formula in that cell:
=ROUND(C5,2)
- Press Enter.
- Furthermore, drag the Fill Handle tool from cell D5 to D9.
- Lastly, we get results like the following image. In the image, we can see the values of GPA up to two decimal places.
Read More: How to Remove Decimals in Excel with Rounding
2. Set Decimal Places with ROUNDUP Function
In the second method, we will use the ROUNDUP function to set decimal places in Excel with a formula. The ROUNDUP function in Excel rounds a value up to the decimal point on the right or left. Let’s see the steps to use this function.
STEPS:
- First, select cell D5.
- Next, type the following formula in that cell:
=ROUNDUP(C5,2)
- Hit Enter.
- Next, pull the Fill Handle tool from cell D5 to D9 as well.
- Finally, we obtain the following results. We can see the GPA values up to two decimal places with upper rounding in the below image.
Read More: How to Remove Decimals without Rounding in Excel
3. Using Formula with Excel ROUNDDOWN Function
In this method, we will use the ROUNDDOWN function to set decimal places in Excel with a formula. The ROUNDDOWN function in Excel rounds a number down to the nearest tenth. To use this function follow the below steps.
STEPS:
- Firstly, select cell D5.
- Secondly, insert the following formula in that cell:
=ROUNDDOWN(C5,2)
- Press Enter.
- Thirdly, drag the Fill Handle tool from cell D5 to D10.
- Lastly, we get results up to two decimal places.
Read More: How to Get 2 Decimal Places Without Rounding in Excel
4. Fix Decimal Places with TRUNC Function in Excel
We can also use the TRUNC function to set decimal places in Excel with a formula. The TRUNC function in Excel truncates a number to a specified number of digits. To use this formula follow the below steps.
STEPS:
- To begin with, select cell D5.
- Additionally, enter the following formula in that cell:
=TRUNC(C5,2)
- Hit Enter.
- Furthermore, pull the Fill Handle tool from cell D5 to D10.
- In the end, we can see the result in the following image. We get the values of GPA up to two decimal places.
5. Combining FLOOR and IF Functions to Set Decimal Places
Another way to set decimal places in Excel with a formula is to use a combination of the FLOOR and IF functions. The FLOOR function in Excel rounds down both the integer number and decimal number to the nearest specified multiple of significance. The IF function is used to check if a condition is met and then the defined statements will be shown based on the given condition. Let’s see the steps to combine these two functions.
STEPS:
- Firstly, select cell D5.
- Secondly, type the following formula in that cell:
=FLOOR(C5, IF(C5>0, 0.01, -0.01))
- Press Enter.
- Furthermore, drag the Fill Handle tool from cell D5 to D9.
- As a result, in the following image, we can see the values of GPA up to two decimal places.
🔎 How Does the Formula Work?
- IF(C5>0, 0.01, -0.01): This section calculates the value of the given numbers to two decimal places. If the value in C5 is positive and greater than 0, it will return 01 rather than -0.01.
- FLOOR(C5, IF(C5>0, 0.01, -0.01)): In this part, we use the FLOOR function to pass the cell number, which is C5 as well as the IF function’s return value.
How to Set Decimal Places in Excel Without Formula
This section is the extension part of this article. Till now we used formulas to set decimal places in Excel. But, in this section, we will discuss how to set decimal places in Excel without a formula.
i. Apply Excel ‘Format Cells’ Option
We can use Excel’s Format Cells option to set decimal places without a formula. To illustrate this we will use the following dataset. The dataset contains GPAs of five students in two semesters. Also, we can see a column showing the CGPA of students. We will set the values of CGPA up to two decimal places with the Format Cells option.
To do this follow the below steps.
STEPS:
- First, select the cell range (E5:E9).
- Next, right-click on the selected area.
- Then, click on the option Format Cells.
- A new dialogue box named Format Cells will appear.
- Following, select the option Number from the Category
- Subsequently, set the value 2 in the Decimal places.
- Now, click on OK.
- Finally, we get results like the following image.
ii. Utilize the ‘Increase Decimal’ and ‘Decrease Decimal’ Buttons
Microsoft Excel provides us with two buttons in the Number section of the Home tab. These are the Increase Decimal and Decrease Decimal buttons. Using these two buttons we can easily set decimal places in Excel without using formulas. To illustrate this method we will use the dataset of the previous method. Let’s see the steps to perform this method.
STEPS:
- In the beginning, select the cell range (E5:E9).
- Then, go to the Home tab.
- After that, from the Number section of the ribbon click on the ‘Decrease Decimal’ button thrice.
- Finally, we can see the result in the following image. The value of CGPA is set to two decimal places.
NOTE: Similar to the above procedure we can use the ‘Increase Decimal’ button to increase the number of digits after the decimal point.
Download Practice Workbook
You can download the practice workbook from here.
Conclusion
In conclusion, this post will show you how to set decimal places in Excel with a formula. To put your skills to the test, use the practice worksheet that comes with this article. Please leave a comment below if you have any questions. Our team will try to reply to you as soon as possible. In the future, keep an eye out for more unique Microsoft Excel solutions.
Related Articles
- How to Roundup a Formula Result in Excel
- How to Add ROUND Formula to Multiple Cells in Excel
- How to Round to Nearest Whole Number in Excel
- How to Round Down to Nearest Whole Number in Excel
- Round to Nearest 5 or 9 in Excel
- How to Round Numbers to the Nearest Multiple of 5 in Excel
- Excel VBA: Round to Nearest 5
- Round Down to Nearest 10 in Excel
- How to Round to Nearest 100 in Excel
- How to Round Numbers to Nearest 10000 in Excel
- How to Round a Formula with SUM in Excel
- Round off Formula in Excel Invoice
- How to Round up Decimals in Excel
- How to Round Up to 2 Decimal Places in Excel
- How to Round Percentages in Excel
- How to Set Rounding Precision in Excel
- How to Round Time in Excel
- How to Round Time to Nearest Minute in Excel
- Round Time to Nearest 5 Minutes in Excel
- How to Round Time to Nearest 15 Minutes in Excel
- Rounding Time in Excel to Nearest Hour
- Rounding Time to Nearest Quarter Hour in Excel
- Rounding to Nearest Dollar in Excel
- How to Round to Nearest 10 Cents in Excel
- How to Round Off to Nearest 50 Cents in Excel
- How to Round Excel Data to Make Summations Correct
- How to Stop Rounding in Excel
- How to Stop Excel from Rounding Large Numbers
- How to Stop Excel from Rounding Up Decimals
- How to Round a Multiplication Formula in Excel
- How to Round Numbers in Excel Without Formula
