How to Remove Decimals in Excel
Home >> Excel Tutorials from Compute Expert >> Excel Tips and Trick >> How to Remove Decimals in Excel
In this tutorial, you will learn how to remove decimals from your number in excel completely.
When we work with decimal numbers in excel, sometimes we need to remove their decimals and get their whole number only. We might need to do that because of our data processing requirements or to show the right results from our work.
There are various formulas we can use to just remove the decimals in our number without doing the normal rounding process. Want to know what are those formulas and how to use them to remove decimals in excel? Learn from this tutorial until its last part!
Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more
Want to work faster and easier in Excel? Install and use Excel add-ins! Read this article to know the best Excel add-ins to use according to us!
Table of Contents:
How to Remove Decimals in Excel 1: TRUNC
The first formula we can use to remove decimals in excel is TRUNC. TRUNC might even be the best formula you can use for this purpose because of its usage simplicity and flexibility.Here is the general writing form of TRUNC if we want to use it to remove decimals from our number.
= TRUNC ( number_with_decimals )
Easy, isn’t it? Just input the number we want to remove the decimals from to TRUNC and we will get our result!
You can give a second input to TRUNC, which is the number of decimals you want in your TRUNC result. However, this second input has a default value of 0 (which means no decimals in the TRUNC result). Therefore, we don’t have to give any second input if we just want to remove all decimals from our number.
Here is the implementation example of TRUNC to remove decimals from our number in excel.
As you can see in the example, we get our numbers without decimals by writing TRUNC just the way we discussed. Just input the number to TRUNC and we will get our number version without its decimals!
How to Remove Decimals in Excel 2: INT
The second formula you can use to remove decimals from your number is INT. Similar to TRUNC, we also only need to input our number into INT to get the result.
= INT ( number_with_decimals )
Here is the INT implementation example in excel.
It looks similar to TRUNC except for the formula name, isn’t it? Then, what is the difference between if we use TRUNC and if we use INT?
Besides TRUNC having its optional second input (we can only give one input to INT), there is also a difference in the process that INT and TRUNC do. INT rounds down our number to its nearest integer while TRUNC truncates the decimals in our number.
We will feel this difference if we input a negative number to TRUNC and INT. TRUNC will just remove decimals from it while INT will round it to a larger negative number!
How to Remove Decimals in Excel 3: FLOOR/FLOOR.MATH
Besides using TRUNC and INT, we can also use FLOOR/FLOOR.MATH to remove decimals from our number.FLOOR and FLOOR.MATH primary function is to round down a number to a particular multiple. If we input 1 as their multiple input, we can use both formulas to remove decimals from our number!
With that in mind, here is the general writing form of FLOOR and FLOOR.MATH to remove decimals from our number.
FLOOR
= FLOOR ( number_with_decimals , 1 )
FLOOR.MATH
= FLOOR.MATH ( number_of_decimals )
Why we don’t input any multiple to FLOOR.MATH above? FLOOR.MATH has a default multiple input of 1, unlike FLOOR. Thus, we don’t have to input any multiple if we want to round down our number to that particular multiple!
Here is the implementation example of FLOOR and FLOOR.MATH in excel.
We can give 1 too as the multiple input of FLOOR.MATH, just like FLOOR. However, we will get the same result as if we don’t give any multiple input to it.
How to Remove Decimals in Excel 4: ROUNDDOWN
The last formula we will discuss here is ROUNDDOWN. If we want to remove decimals using ROUNDDOWN, we just have to input 0 as its number of decimals input.
= ROUNDDOWN ( number_with_decimals, 0 )
As you may have already known, the 0 input here means we want 0 number of decimals in our ROUNDDOWN result. Because of that, ROUNDDOWN will round down our number to its nearest integer, thus, removing all of its decimals!
Here is the implementation example of the ROUNDDOWN formula in excel.
How to Remove Decimals in Negative Numbers in Excel: TRUNC/FLOOR/FLOOR.MATH/CEILING/CEILING.MATH
Most of the formulas above (except for TRUNC, which truncates the decimals in our number) use a round-down process to remove decimals from our number. What if we want to remove decimals from a negative number instead? If we round down a negative number for this purpose, we probably will get a different (larger) negative integer as a result.To remove decimals from a negative number in excel, we can still use TRUNC, FLOOR, and FLOOR.MATH. In addition, we can also use CEILING and CEILING.MATH to help us.
Here are the general writing form of the five formulas to remove decimals from a negative number in excel.
TRUNC
= TRUNC ( negative_number_with_decimals )
FLOOR
= FLOOR ( negative_number_with_decimals , -1 )
FLOOR.MATH
= FLOOR.MATH ( negative_number_with_decimals , , any_value_other_than_zero )
CEILING
= CEILING ( negative_number_with_decimals , 1 )
CEILING.MATH
= CEILING.MATH ( negative_number_with_decimals )
As TRUNC just truncates all decimals in our number, we write it the same way for a positive or negative number. For FLOOR and FLOOR.MATH, though, the inputs are quite different.
To remove decimals from a negative number, we have to round it closer to zero. As FLOOR rounds a negative number closer to zero if its multiple input is negative, we input -1 as the multiple.
For FLOOR.MATH, we have to input any value other than zero for its negative number rounding mode input part (the third input). It is because the default input for that is 0 or rounding a negative number away from zero. For the multiple input part (the second input), we don’t have to input anything since its default is the multiple of 1.
If we use CEILING to remove the decimals, then we need to give 1 as its multiple input. That is because CEILING rounds a negative number closer to zero if its multiple input is positive.
For CEILING.MATH, since its default nature rounds a negative number closer to zero, its input is simple. We only have to input the negative number we want to remove decimals from. As the default multiple input of CEILING.MATH is 1 (similar to FLOOR.MATH), we don’t have to input any multiple too.
Here is the implementation example of those five formulas to remove decimals from a negative number in excel.
As you can see in the example, we get the same results from all the five formula writings. We have removed all decimals in our negative numbers in those results.
Exercise
After you know how to remove decimals in excel by using various formula writings, now let’s do an exercise. This is so you can sharpen your understanding of the materials you just learned from this tutorial!Download the exercise file below and do the instruction. Download the answer key file if you have done the exercise and want to check your answer. Or probably if you are confused about how to do the instruction!
Link to the exercise file:
Download here
Instruction:
Answer on the appropriate number row in the gray-colored cell.- INT
- FLOOR
- ROUNDDOWN
- TRUNC
Link to the answer key file:
Download here
Additional Note
If you need to remove decimals from positive and negative numbers using the same formula writing, use TRUNC! It is the only formula which has the same writing to remove decimals in both positive and negative numbers!Related tutorials you should learn from:
