Md. Abdullah Al Murad
We get numbers as output in most operations in Excel. But if those numbers have decimal digits too how then this could cause irritation. For this, we need to reduce the decimal value of the numbers. If you are curious to find how you can get 2 decimal places in Excel without rounding, then this article may come in handy for you. In this article, we will discuss how you can get the 2 decimal places without rounding them in Excel with an elaborate explanation.
Download Practice Workbook
Download this practice workbook below.
4 Easy Ways to Get 2 Decimal Places Without Rounding in Excel
Below, we presented 4 separate easy methods to show only two decimals without rounding them in Excel. To avoid any kind of compatibility issue, try to use Excel 365 edition.
1. Using TRUNC Function
Let’s assume we have a dataset of some Fruits with their Prices. Now I will show how to get the Prices up to two decimal points using the format cell option. Now we will update the Princess using the TRUNC function.
Steps
- Enter the formula below in cell D5.
=TRUNC(C5,2)
- Drag down the Fill Handle to cell D11.
- After then, you will notice that the prices are now updated with only two decimal places.
Read More: How to Remove Decimals Without Rounding in Excel (4 Suitable Ways)
2. Applying ROUNDDOWN Function
Another common function is the ROUNDDOWN function which can be used to get numbers up to two decimal places in Excel. ROUNDDOWN is really not the most advisable method, but it works well in these conditions.
➥ Read More: How to Round up Decimals in Excel (4 Simple Ways)
Steps
- Enter the formula in cell D5.
=ROUNDDOWN(C5,2)
- Then drag the Fill Handle to cell D11.
- After then, you will notice that the prices are now with only two decimal places
- Moreover, their decimal places are not moved from two decimal places.
Read More: How to Fix Decimal Places in Excel (7 Simple Ways)
Similar Readings
- Insert Dot between Numbers in Excel (3 Ways)
- How to Remove Decimal Places in Excel (5 Easy Methods)
- Set Decimal Places in Excel with Formula (5 Effective Ways)
- How to Change Decimals to Percentages in Excel (4 Easy Ways)
3. Use of INT Function
In this section let’s see how to do the formation using a formula with the INT function.
Steps
- Enter the following formula in cell D5.
=INT(C5*100)/100
- Then drag the Fill Handle to cell D11.
- Doing this will fill the range of cell D5:D11 is filled with values with only 2 decimal places
- Moreover, the values are not rounded at all. So the method worked perfectly.
Read More: How to Remove Decimals in Excel with Rounding (10 Easy Methods)
4. Combining FLOOR and IF Functions
Using two of the most commonly used functions FLOOR and IF we can easily do that. In this section, I will show how to do that using formulas for the previous dataset.
Steps
- Enter the formula in cell D5.
=FLOOR(C5, IF(C5>0, 0.01, -0.01))
Formula Breakdown
Firstly, in the inner function which is IF(B4>0, 0.01, -0.01), retrieve the value from the given numbers up to two decimal places. If the value in B4 is greater than 0 which is positive then it will return 0.01 other than -0.01.
Then using the FLOOR function again we are passing the cell number which is B4 and the return value of the IF function which will be used as significance here.
- Then drag the Fill Handle to cell D11.
- Doing this will fill the range of cell D5:D11 is filled with values with only 2 decimal places
- Moreover, the values are not rounded at all. So the method worked perfectly.
Read More: How to Reduce Decimals in Excel (10 Easy Methods)
How to Get 2 Decimal Places with Rounding in Excel
In the previous examples, we got the decimal places without rounding. In this topic, we will discuss how we can get the values with rounding values.
Steps
- We have the Prices presented in the range of cell C5:C11.
- And these prices are actually in 4 decimal places.
- We need to make them as 2 decimal values without any rounding.
- Select the range of cells D5:D11, and then go to the Home tab > Number
- Then click on the Decrease Decimal command icon.
- By doing this we can see that the range of cell C5:C11 is filled with values rounded to their nearest integer values.
How to Reduce Decimal Places Permanently in Excel
In the previous methods, we have seen how we can turn higher decimals to lower decimal values. But those methods actually do not obliterate the original value completely. The values are actually in the cell all along and just the output views are now showing as two decimal values. In this method, however, we are going to permanently remove the extra decimal from the numbers. Using two of the most commonly used functions FLOOR and IF.
Steps
- Here, we are going to reduce the decimal places mentioned here in the range of cells C5:C11.
- We need to reduce it to 4.
- To do this, enter the following formula in the cell.
=FLOOR(C5, IF(C5>0, 0.01, -0.01))
Doing this will fill the range of cell D5 with the reduced decimal place from the cell value C5.
Formula Breakdown
Firstly, in the inner function which is  IF(C5>0, 0.01, -0.01), retrieve the value from the given numbers up to two decimal places. If the value in B4 is greater than 0 which is positive then it will return 0.01 other than -0.01.
Then using the FLOOR function again we are passing the cell number which is B4 and the return value of the IF function which has significance here.
- Then drag the Fill Handle to cell D11.
- Doing this will fill the range of cells D5:D11 with reduced decimal prices compared to the cell values in the range of cells C5:C11.
💬 Things to Remember
Be careful for which purpose you are going to use this article’s methods. Some situations and calculations demand more precision compared to others. Reducing decimals can create anomalies in the sophisticated process and bring disaster.
Conclusion
To sum it up, the issue of how we can get the 2 decimal places without rounding them in Excel is shown here with 4 separate methods.
For this problem, a workbook is available to download where you can practice these methods.
Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable
Further Readings
- Excel Round to 2 Decimal Places (with Calculator)
- Excel Round to Nearest 100 (6 Quickest Ways)
- Using Excel to Round to Nearest 1000 (7 Easy Ways)
- How to Apply Accounting Number Format in Excel! (Easy way)
- Excel Custom Number Format Multiple Conditions
- How to Round Numbers in Excel Without Formula (3 smart ways)
