How to remove digits after decimal in Excel?
Sometimes you need to remove all digits after decimal to make numbers to whole numbers. You may think the Decrease Decimal feature can deal with it. Actually, the Decrease Decimal feature just changes a number’s display value, for example, show 2.5 as 3, but the actual value still is 2.5 showing in the formula bar. Therefore, how to remove digits after decimal in Excel? There are several methods for you.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
Supposing we have a purchase table as below screen shot shown, and now we need to remove digits after decimal for values in Amount Column. Excel functions can help you to remove digits after decimal easily. You can do as follows:
INT function: In a blank cell besides the value you will remove digits after decimal, enter the formula =INT(E2), and the drag the Fill Handle to the range you need.
(1) In the formula =INT(E2), E2 is the cell that you will remove digits after decimal.
(2) INT function can also remain certain places of digits after decimal. For example, to remove all digits except the first one after decimal, you can apply the formula =INT(E2*10)/10.
TRUNC function: Besides the value you will remove digits after decimal, enter the formula =TRUNC(E2,0) into a blank cell, and then drag the Fill Handle to the range you need.
(1) In the formula =TRUNC(E2,0), E2 is the cell you will remove digits after decimal, 0 means removing all digits after decimal;
(2) To remove all digits but first one after decimal, you can apply the formula =TRUNC(E2,1);
Tip: Remove formulas but keep formula results: (1)select all formula cells; (2) copy these formula cells with pressing the Ctrl + C keys at the same time; (3) right click the first cell of destination range, and select Values below Paste Options in the context menu.
By the way, If you have Kutools for Excel installed, you can apply its To Actual feature to quickly convert all formulas to their formula results with only one click. See screenshots:
All functions don’t modify original values in Excel, but sometimes you may just need to change original values. To remove digits after decimal from original values directly, you can apply the Round utility of Kutools for Excel.
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
1. Select the cells that you will remove digits after decimal, and then click the Kutools > Round.
2. In the opening Round without Formula dialog box, type 0 in the Decimal place box, check the Rounding down option, and click the Ok button.
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 3 months agohelps a lot, thanks
To post as a guest, your comment is unpublished.· 4 months agoawesome very informative
To post as a guest, your comment is unpublished.· 6 months agoNICE INFORMATION
To post as a guest, your comment is unpublished.· 10 months agoHi.. I would like to Round the digits to 2 or 3 decimals, but when i use ROUND command it round up the number to the 2 or 3 decimals if the number contains more decimal places. But if the number is whole number for example , it displays as 1. Is there any command to make it as 1.00
To post as a guest, your comment is unpublished.· 11 months agoThanks very helpfull