How to Get 2 Decimal Places Without Rounding in Excel (4 Ways)

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.

excel 2 decimal places without rounding


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)

Using TRUNC Function to 2 decimal places without rounding in excel

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

Applying ROUNDDOWN Function to get 2 decimal places without rounding in Excel

  • 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


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

Use of INT Function to get 2 decimal places without rounding in Excel

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

Combining FLOOR and IF Functions to get 2 decimal places without rounding in Excel

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.

Get 2 Decimal Places with Rounding in Excel

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

rounding with tow decimal places


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.

Reduce Decimal Places Permanently in Excel

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

reduced decimal places permanently


💬 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

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo