How to Reduce Decimal Places Permanently in Excel (5 Ways)

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for ways to reduce decimal places in Excel permanently, then you are in the right place. There are many easy ways to reduce decimal places but all of them do not work to reduce them permanently. So, let’s get into the main article to know more about the ways.

In the following dataset, we have a list of products with their corresponding selling prices and profits. But the profits are written up to five decimal places. If we want to reduce them up to two decimal places permanently, then we can follow the 5 procedures stated below.

excel reduce decimal places permanently

For creating this article, we have used the Microsoft Excel 365 version. However, you can use another version according to your necessary.


1. Combining Advanced Option Feature with Number Format to Reduce Decimal Places Permanently

Here, we will customize the number format of the profits, but prior to that, we will activate an option under advanced options.

Combining Advanced Option Feature with Number Format to reduce decimal places permanently in Excel

  • Go to File.

  • Select Options.

Then, the Excel Options dialog box will open up.

  • Go to the Advanced tab >> check the Set precision as displayed option under When calculating this workbook.

advanced

All of a sudden, a message box will appear to ensure your last change.

  • Press OK.

  • Also, in the Excel Options wizard click on OK.

Now, return to the worksheet to do the next part.

  • Select the range, and then go to the Home tab >> expand the Number Format dialog box by clicking on this icon.

number format

Afterward, the Format Cells dialog box will open up.

  • As our values are in Currency format, go to the Currency tab >> select 2 as the value of Decimal places >> press OK.

Finally, the decimal places of the profits are reduced from 5 decimal places to 2 decimal places, and it is permanent as we can see the same value also in the formula bar.

Combining Advanced Option Feature with Number Format to reduce decimal places permanently in Excel

Read More: How to Change Decimal Places in Excel


2. Using TRUNC Function to Permanently Reduce Decimal Places in Excel

In this section, we will use the TRUNC function to minimize the decimal places of the profits permanently.

Using TRUNC Function to Reduce Decimal Places in Excel Permanently

  • Type the following formula in cell E4.
=TRUNC(D4,2)

Here, D4 is the profit value, and 2 is the number of the decimal places up to which we want to keep.

  • Press ENTER and drag down the Fill Handle.

Finally, we will be able to reduce the decimal places of the profits permanently.

Using TRUNC Function to Reduce Decimal Places in Excel Permanently


3. Reducing Decimal Places Permanently Using ROUND Function

Here, we will show the usage of the ROUND and ROUNDDOWN functions to decrease the decimal places of the profit values of the following dataset.

Using ROUND Function to Reduce Decimal Places in Excel Permanently

  • Enter the following formula in cell E4.
=ROUND(D4,2)

Here, D4 is the profit value, and 2 is the number of the decimal places up to which we want to keep. The ROUND function will round the digit of the 2 decimal places to an upper value if the next digit is 5 or greater than 5.

For example, we can see 78.97534 has been changed into 78.98.

  • Drag down the Fill Handle.

Finally, we will have profits with permanently reduced decimal places.

Now, we will show the difference in the results when we are using the ROUNDDOWN function.

  • Apply the following formula to get the modified profits.
=ROUNDDOWN(D4,2)

Here, D4 is the profit value, and 2 is the number of the decimal places up to which we want to keep. The ROUNDDOWN function will reduce the decimal places without changing any digits.
For example, we can see 78.97534 has been changed into 78.97.

Using ROUNDDOWN Function to Reduce Decimal Places in Excel Permanently


4. Applying INT Function for Reducing Decimal Places in Excel Permanently

Here, we will use the INT function to perform the task of decreasing the decimal places permanently from the profit values.

Using INT Function to Reduce Decimal Places in Excel Permanently

  • Write down the following formula in cell E4.
=INT(D4*100)/100

Formula Breakdown

  • D4*100 → becomes
    • 97534*100
      • Output → 7897.534
  • INT(D4*100) → becomes
    • INT(7897.534)
      • Output → 7897
  • INT(D4*100)/100 → becomes
    • 7897/100 → 78.97
  • Drag down the Fill Handle.

Finally, the decimal places of the profits are reduced from 5 decimal places to 2 decimal places.

Read More: How to Remove Decimals in Excel


5. Combining FLOOR with IF Function to Reduce Decimal Places

In this section, we are going to use the FLOOR function with the IF function to perform the reduction of the decimal places of the profits.

Using FLOOR, IF Functions to Reduce Decimal Places in Excel Permanently

  • Type the following formula in cell E4.
=FLOOR(D4,IF(D4>0,0.01,-0.01))

Formula Breakdown

  • D4>0 → it is the logical condition, if the profit value is positive then we will get TRUE, otherwise FALSE.
    • Output → TRUE
  • IF(D4>0,0.01,-0.01) → becomes
    • IF(TRUE,0.01,-0.01) → returns 01 for TRUE, otherwise -0.01.
      • Output → 0.01
  • FLOOR(D4,IF(D4>0,0.01,-0.01)) → becomes
    • FLOOR(78.97534,0.01) → The FLOOR function will reduce the profit value up to 2 decimal places due to the significant value of 0.01
      • Output → 78.97
  • Drag down the Fill Handle.

Eventually, you will be able to reduce the decimal places of the profits permanently.


Practice Section

To practice by yourself, we have created a Practice section on the right side of each sheet.

practice


Download Practice Workbook


Conclusion

In this article, we have discussed various ways to reduce decimal places permanently in Excel. Hope these methods will help you a lot. If you have any further queries, then leave a comment below.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel projects.
Tanjima Hossain
Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo