How to Use Excel Formula to Display Numbers Without Decimal Places?
If you work with numbers in Microsoft Excel, you may often need to display numeric values without any decimal places. Whether you’re dealing with integers, whole numbers, round numbers, or simply want to truncate the decimal part, Excel provides several ways to achieve this. In this article, we’ll explore different Excel formulas and techniques to format and display numbers without decimal places.
Using the TRUNC Function
One straightforward way to remove decimal places from a number in Excel is by using the TRUNC function. The TRUNC function truncates a number to a specified number of digits, effectively removing any decimal places.
The syntax for the TRUNC function is:
=TRUNC(number, [num_digits])
- number: The number you want to truncate.
- num_digits: The number of digits to the right of the decimal point to retain. If omitted, it defaults to 0.
Examples of Using TRUNC
Let’s look at a few examples of how to use the TRUNC function to remove decimal places:
Original Number | Formula | Result |
---|---|---|
12.3456 | =TRUNC(12.3456) | 12 |
-4.789 | =TRUNC(-4.789) | -4 |
56.9999 | =TRUNC(56.9999, 2) | 56.99 |
As you can see, the TRUNC function effectively removes the decimal places and returns the integer portion of the number.
Advantages of Using TRUNC
The TRUNC function offers several advantages when removing decimal places from numbers:
- Simplicity: The TRUNC function is straightforward to use, requiring only the number as the argument. It provides a quick and easy way to truncate decimal places.
- Flexibility: By specifying the optional num_digits argument, you can control the number of decimal places to retain. This allows you to truncate numbers to a specific number of decimal places if needed.
- Consistency: Unlike rounding functions, the TRUNC function always truncates the decimal places without any rounding behavior. This ensures consistent results regardless of the decimal values.
Limitations of Using TRUNC
While the TRUNC function is useful for removing decimal places, it does have some limitations:
- Truncation vs. Rounding: The TRUNC function truncates the decimal places without any rounding. If you need to round numbers based on the decimal values, you’ll need to use other functions like ROUND, ROUNDUP, or ROUNDDOWN.
- Negative Numbers: When using TRUNC with negative numbers, it truncates towards zero. This means that negative numbers will have their decimal places removed, but the integer portion will remain negative.
Using the INT Function
Another Excel function that can be used to remove decimal places is the INT function. The INT function rounds a number down to the nearest integer, discarding any decimal places.
The syntax for the INT function is:
=INT(number)
- number: The number you want to round down to the nearest integer.
Examples of Using INT
Here are a few examples of how to use the INT function to remove decimal places:
Original Number | Formula | Result |
---|---|---|
3.14159 | =INT(3.14159) | 3 |
-7.8 | =INT(-7.8) | -8 |
0.999 | =INT(0.999) | 0 |
The INT function always rounds down, so keep in mind that negative numbers will be rounded down to the next lower integer.
Advantages of Using INT
The INT function has some advantages when removing decimal places:
- Simplicity: Like the TRUNC function, the INT function is easy to use, requiring only the number as the argument.
- Rounding Down: The INT function always rounds numbers down to the nearest integer. This can be useful when you specifically need to round down and remove decimal places.
Limitations of Using INT
However, the INT function also has some limitations:
- Rounding Behavior: The INT function always rounds down, regardless of the decimal value. If you need to round up or round based on the decimal value, you’ll need to use other rounding functions.
- Negative Numbers: When using INT with negative numbers, it rounds down to the next lower integer. This means that -3.2 will be rounded down to -4.
Using the ROUND Function
If you want more control over the rounding behavior when removing decimal places, you can use the ROUND function. The ROUND function rounds a number to a specified number of digits, allowing you to round up or down based on the decimal value.
The syntax for the ROUND function is:
=ROUND(number, num_digits)
- number: The number you want to round.
- num_digits: The number of digits to round the number to. Positive values round to the right of the decimal point, while negative values round to the left.
Examples of Using ROUND
Let’s look at a few examples of how to use the ROUND function to remove decimal places:
Original Number | Formula | Result |
---|---|---|
45.67 | =ROUND(45.67, 0) | 46 |
23.4 | =ROUND(23.4, 0) | 23 |
-12.8 | =ROUND(-12.8, -1) | -10 |
By specifying the num_digits argument as 0, the ROUND function will round the number to the nearest integer, effectively removing any decimal places. You can also use negative values for num_digits to round to the nearest tens, hundreds, thousands, etc.
Advantages of Using ROUND
The ROUND function offers several advantages when removing decimal places:
- Rounding Control: With the ROUND function, you have control over the rounding behavior. You can specify the number of digits to round to, allowing you to round to the nearest integer, tens, hundreds, etc.
- Flexibility: The ROUND function provides flexibility in handling different rounding scenarios. You can round up, round down, or round based on the decimal value.
Limitations of Using ROUND
However, the ROUND function also has some limitations:
- Rounding vs. Truncation: The ROUND function performs rounding based on the decimal value. If you specifically need to truncate decimal places without any rounding, you’ll need to use the TRUNC function instead.
- Midpoint Rounding: When rounding numbers that are exactly halfway between two integers (e.g., 1.5, 2.5), the ROUND function follows the “round half up” rule. It rounds up to the nearest even number. If you need different midpoint rounding behavior, you may need to use other functions or custom formulas.
Using Number Formatting
In addition to using formulas, you can also remove decimal places by formatting the cells that contain the numbers. Excel provides various number formats that allow you to control how numbers are displayed, including removing decimal places.
To format numbers without decimal places:
- Select the cell(s) containing the numbers you want to format.
- Right-click and choose “Format Cells” from the context menu, or press Ctrl+1.
- In the “Format Cells” dialog box, select the “Number” category.
- Adjust the “Decimal places” field to 0.
- Click “OK” to apply the formatting.
By setting the decimal places to 0, Excel will display the numbers without any decimal places. However, it’s important to note that this is only a visual formatting change and does not affect the underlying value of the cell.
Advantages of Using Number Formatting
Using number formatting to remove decimal places has some advantages:
- Visual Presentation: Number formatting allows you to control how numbers are displayed in Excel without changing the actual values. This is useful when you want to present numbers in a specific format for reporting or visual purposes.
- Flexibility: Excel provides a wide range of number formatting options, including different number formats, currency symbols, percentage formats, and more. You can easily customize the formatting to suit your needs.
Limitations of Using Number Formatting
However, number formatting also has some limitations:
- Underlying Values: Number formatting only affects the visual presentation of numbers and does not change the underlying values in the cells. If you perform calculations or use the formatted numbers in other formulas, the original values will be used.
- Consistency: When using number formatting, it’s important to ensure consistency across your worksheet or workbook. If you change the formatting in one place, you may need to update it in other places as well to maintain a consistent appearance.
Combining Formulas and Formatting
You can also combine formulas and formatting to remove decimal places and ensure that the underlying values are also integers. Here’s an example:
- Start with a cell containing a number with decimal places (e.g., A1 contains 3.14159).
- In another cell (e.g., B1), use one of the formulas mentioned earlier to remove the decimal places:
=TRUNC(A1) - Format cell B1 to display the number without decimal places using the steps described in the previous section.
By using a formula to truncate or round the number and then applying number formatting, you can ensure that both the displayed value and the underlying value are without decimal places.
Handling Errors and Special Cases
When working with formulas to remove decimal places, there are a few error cases and special considerations to keep in mind:
- #VALUE! error: If the cell you’re referencing with the formula contains a non-numeric value, you may encounter the #VALUE! error. To handle this, you can wrap the formula inside an IFERROR function, like this:
=IFERROR(TRUNC(A1), “”)
This will return an empty string if the formula encounters an error. - Negative numbers: When using the INT function, negative numbers will be rounded down to the next lower integer. If you want to round negative numbers up to the nearest integer, you can use the ROUNDUP function instead:
=ROUNDUP(number, 0) - Precision and rounding: Keep in mind that when using the ROUND function, Excel follows the standard rounding rules. Numbers ending in .5 or greater are rounded up, while numbers less than .5 are rounded down. If you need more control over the rounding behavior, you can use the ROUNDUP or ROUNDDOWN functions.
Final Thoughts
Removing decimal places from numbers in Excel is a common task that can be accomplished using various formulas and techniques. The TRUNC, INT, and ROUND functions provide straightforward ways to truncate or round numbers to remove decimal places. Additionally, number formatting can be used to visually display numbers without decimal places.
By understanding these different approaches, you can choose the most appropriate method based on your specific requirements. Whether you’re working with integers, whole numbers, or need to round numbers in a specific way, Excel provides the tools to handle number formatting efficiently.
FAQs
What is the easiest way to remove decimal places from numbers in Excel?
How do I round numbers down to the nearest integer in Excel?
Can I round numbers to a specific number of decimal places in Excel?
How can I format numbers in Excel to display without decimal places?
Does formatting numbers without decimal places change the underlying values in Excel?

Vaishvi Desai is the founder of Excelsamurai and a passionate Excel enthusiast with years of experience in data analysis and spreadsheet management. With a mission to help others harness the power of Excel, Vaishvi shares her expertise through concise, easy-to-follow tutorials on shortcuts, formulas, Pivot Tables, and VBA.