If the dataset has decimals, it could be challenging to read data easily. To make it look simple, you have to remove decimals in Excel using different methods. Don’t worry about how to remove decimals in Excel, because we have come up with useful hacks.
Remove Decimals using Cell Formatting
Considering the following dataset, we will continue exploring how you can remove decimals from a worksheet.
Follow the steps given below:
- Select the range with decimal numbers (B2:B8), and then right-click on the selected area. Choose Format Cells. Or else you can open the Home tab from the ribbon, and choose the Format Number icon placed on the lower side of the Number group.
- Open the Number category from the Format Cells window and set 0 Decimal Places. Click OK.
Ultimately, your all numbers will be rounded to the nearest whole number.
Remove Decimals using INT Function
With the INT function, you can round the number down to the nearest integer. That’s why the INT function is helpful to remove decimal in Excel. Using this function, you can have an integer number without decimal points. Now, enter the following formula in a blank cell (E5),
=INT(D5)
The INT function will return the integer part of the number of cell D5 in cell E5.
Press ENTER and you will get all the numbers without decimals.
Drag the cell E5 to apply this formula in all other cells.
Removing Decimals Without Rounding
Using the TRUNC function is helpful when you simply need to convert a decimal value to a whole number. With this function, you can truncate a decimal value to make it precise. On the basis of a given number of digits, you can use it to truncate a number.
Let’s see how Excel truncates decimal without rounding:
The TRUNC function syntax is given below:
=TRUNC (number; [num_digits])
In this syntax, you can see:
number is known to be a numerical value or cell reference having the number you need to truncate.
num_digits is known to be the precision of the truncation. This value comes with a default value of 0, which is optional.
To extract the integer part of a number by removing the fractional part, you have to overlook the other optional argument. In that case, the function TRUNC (4.7) returns the value 4, whereas the function TRUNC (2.2) returns the value 2.
Below you will see how the TRUNC function works using multiple decimal inputs:
Remove Decimals using TRUNC Function
As you already know about the TRUNC function, it simply truncates the decimal figure and returns the integer value. Enter the following formula in cell E5 to utilize this function:
=TRUNC (D5)
The decimal value will be truncated in cell D5 and return the integer value in cell E5.
Hit ENTER and the number without decimals in cell E5 will appear.
Put the same formula in all cells by dragging the cell E5.
ROUND Function
Using the ROUND function lets you round the number to a given number of digits. You can remove decimal values by adding 0 as the given number of digits. If you think the decimal value is bigger than 0.5, you will see it rounds up the digit and if the decimal value is smaller or equal to 0.5, you will see it rounds down the number.
Let’s follow the steps of removing decimals using the TRUNC function. To get rid of decimals in Excel let’s follow the given steps.
Enter the formula in cell E5:
=ROUND (D5, 0)
Here you will see this function rounds the number of cell D5 without any decimals. Furthermore, you will get the value in cell E5.
Press the ENTER key and get the number without decimals.
Now, you will see the decimal figure was .80, which is bigger than .5 that’s why it rounds up the value. So, finally, you will get the number without decimals by dragging the cell E5.
Remove Decimals with CEILING Function
The CEILING function helps round up the number to the nearest multiple of significance. In that case, if you add 1 as the significance, the function helps in removing the decimals from the number. Enter the formula in cell E5:
=CEILING(D5, 1)
The CEILING function will round up the number of cell D5 to a multiple of 1. This is another useful formula to remove decimals in Excel.
Press ENTER to get the number without decimals.
Now, drag the cell E5 in the end, it will let you apply the same formula in other cells. This is how to remove decimal point in Excel without rounding.
Wrapping Up
That’s it!
Now, you have an idea about how to remove decimals in Excel, so just be clear about it whether you can use any of the above-mentioned methods. Each method will give you a chance to experience uniqueness. Continue exploring new tricks and hacks used in Excel.
