The article will show you 10 effective and easy methods on how to reduce decimals in Excel. Sometimes, you may have repeating decimals or irrational or transcendental numbers as results after your data calculation. In order to simplify those decimal numbers, you need to reduce the decimal part of them.
In the dataset, we have information about a company’s percentage of profit on different products.
How to Reduce Decimals in Excel: 10 Ways
1. Applying Number Format Feature to Reduce Decimals in Excel
This section will show you how to reduce decimals from percentages by using a built-in command. Let’s go through the procedure below.
Steps:
- First, make a column for reduced percentages and select the range E5:E11.
- Go to Number Format >> Number
- Type the following formula in cell E5.
=D5
- Press the ENTER button and you will see the percentage in a reduced decimal
- Use the Fill Handle to AutoFill the lower cells.
Thus you can reduce decimals by using the Excel built-in command.
2. Implementing Button from Number Format to Reduce Decimals in Excel
Another easy way to reduce decimals is to use the Decrease Decimal button in the Number ribbon. Please, read the description below.
Steps:
- Select the range D5:D11 and put your cursor on the Decrease Decimal
- Click on the button one or multiple times. You will see the percentages in reduced decimals.
By following this approach, you can easily reduce decimals from a number.
3. Inserting Excel ROUND Function to Reduce Decimals
Now we are going to use a formula to reduce decimals. The ROUND Function is very effective when we want to simplify a decimal number.
Steps:
- Make a new column for reduced percentage and type the following formula in the cell E5.
=ROUND(D5,3)
Here, the ROUND Function leaves 3 numbers after the decimal point of the number in cell D5.
- Hit the ENTER button and you will see the reduced decimal format of the profit percentage in D5.
- Use the Fill Handle to AutoFill the lower cells.
By following this approach, you can reduce decimals from a number in Excel using the ROUND Function.
4. Applying Excel ROUNDDOWN Function to Reduce Decimals
We can also use the ROUNDDOWN Function to reduce decimals from the profit percentages of the products. Let’s go through the process below.
Steps:
- Make a new column for reduced percentage and type the following formula in cell E5.
=ROUNDDOWN(D5,1)
Here, the ROUNDDOWN Function leaves 1 number after the decimal point of the number in cell D5 and rounds it down.
- Hit the ENTER button and you will see the reduced decimal format of the profit percentage in D5.
- Use the Fill Handle to AutoFill the lower cells.
Thus you can reduce decimals from a number in Excel with the help of the ROUNDDOWN Function.
5. Implementing ROUNDUP Function to Reduce Decimals
There is another function that we can use to reduce decimals. The name of this function is the ROUNDUP function. Let’s go through the procedure below for a better understanding.
Steps:
- Make a new column for reduced percentage and type the following formula in cell E5.
=ROUNDUP(D5,2)
Here, the ROUNDUP Function leaves 2 numbers after the decimal point of the number in cell D5 and rounds it up.
- Hit the ENTER button and you will see the reduced decimal format of the profit percentage in D5.
- Use the Fill Handle to AutoFill the lower cells.
By following this approach, you can reduce decimals from a number in Excel using the ROUNDUP Function.
6. Use of MROUND Function
We can also use the MROUND Function to reduce decimals from the profit percentages of the products. Let’s go through the process below.
Steps:
- Make a new column for reduced percentage and type the following formula in cell E5.
=MROUND(D5*10,1)/10
Here, D5*10 will return 233.4531 and MROUND(D5*10,1) will return the nearest multiple of 1 to 233.4531 which is 233, as we set the multiple 1. We want 1 number after the decimal point so we divided the formula by 10..
- Hit the ENTER button and you will see the reduced decimal format of the profit percentage in D5.
- Use the Fill Handle to AutoFill the lower cells.
Thus you can reduce decimals from a number in Excel using the MROUND Function.
7. Application of INT Function
Another function that we can use to reduce decimals is the INT Function. We want to keep 3 numbers after the decimal. Let’s go through the procedure below for better understanding.
Steps:
- Make a new column for reduced percentage and type the following formula in the cell E5.
=INT(D5*1000)/1000
Here, the INT Function returns the whole part of D5*1000 which is 23345. We want 3 numbers after the decimal point so we divided the formula by 1000..
- Hit the ENTER button and you will see the reduced decimal format of the profit percentage in D5.
- Use the Fill Handle to AutoFill the lower cells.
By following this approach, you can reduce decimals from a number in Excel with the help of the INT Function.
8. Implementing TRUNC Function to Reduce Decimals
Now we are going to use a function named TRUNC function to reduce decimals. Let’s go through the description below.
Steps:
- Make a new column for reduced percentage and type the following formula in cell E5.
=TRUNC(D5,2)
Here, the TRUNC Function leaves 2 numbers after the decimal point of the number in cell D5, as we set [num_digits] to 2.
- Hit the ENTER button and you will see the reduced decimal format of the profit percentage in D5.
- Use the Fill Handle to AutoFill the lower cells.
By following this approach, you can reduce decimals from a number in Excel using the TRUNC function.
9. Application of FLOOR and IF Functions
We can also use the combination of FLOOR and IF Functions to reduce decimals from the profit percentages of the products. Let’s go through the process below.
Steps:
- Make a new column for reduced percentage and type the following formula in cell E5.
=FLOOR(D5, IF(D5>0, 0.001, -0.01))
Here, IF(D5>0, 0.001, -0.01) will return the Significance value for the FLOOR Function which is 0.001. The formula then will be simplified to FLOOR(D5, 0.001) which will return 23.345. We want 3 numbers after the decimal point so we set the Significance value to 0.001. You can also use the following formula instead of this one. It will give you the same result too.
=FLOOR(D5, 0.001)
- Hit the ENTER button and you will see the reduced decimal format of the profit percentage in D5.
- Use the Fill Handle to AutoFill the lower cells.
Thus you can reduce decimals from a number in Excel using the FLOOR and IF Functions.
10. Using CEILING Function to Reduce Decimals
Another function that we can use to reduce decimals is the CEILING Function. In this section, we want to keep 2 numbers after the decimal. Let’s go through the procedure below for a better understanding.
Steps:
- Make a new column for reduced percentage and type the following formula in cell E5.
=CEILING(D5,0.01)
Here, the CEILING Function returns the approximate value of cell D5 with 2 numbers after the decimal. As we want 2 numbers after the decimal point, we set the Significance value to 0.01.
- Hit the ENTER button and you will see the reduced decimal format of the profit percentage in D5.
- Use the Fill Handle to AutoFill the lower cells.
By following this approach, you can reduce decimals from a number in Excel with the help of the CEILING function.
Read More: Remove Decimals in Excel
Download Practice Workbook
Here I’m giving you the dataset of this article so that you can practice these methods on your own.
Download Practice Workbook
Conclusion
Suffice to say, you will learn almost all the easiest possible ways to reduce decimals in Excel after reading this article. If you have any questions or feedback regarding this article, please share them in the comment box. This will help me enrich my upcoming articles.
Related Articles
- How to Add Decimals in Excel
- How to Change Decimal Places in Excel
- How to Change Decimal Separator in Excel
- How to Change 1000 Separator to 100 Separator in Excel
- Excel Reduce Decimal Places Permanently
- How to Convert Decimal to Whole Numbers in Excel
- Convert Decimal to Fraction in Excel
- Excel Decimal Places Problem
- Excel Limit Decimal Places