The easiest way to display numbers with two decimal places without rounding in Excel is to use the TRUNC function:
This function is used to truncate a number to only show a specific number of decimal places.
This particular formula truncates the value in cell A2 to only two decimal places.
For example, if cell A2 contains 12.48832 then this formula will return 12.48.
The following example shows how to use this formula in practice.
Example: Display Two Decimal Places Without Rounding in Excel
Suppose we have the following list of values in Excel:
Suppose we would like to only display each value with two decimal places.
Once common way to display fewer decimals in Excel is to highlight the range A2:A9, then click the Decrease Decimal icon in the Number group on the Home tab along the top ribbon:
We can repeatedly click this icon until only two decimals are shown in each number:
The problem is that the decimal values are automatically rounded to the nearest hundredth.
For example, the value 12.48832 is rounded to 12.49.
If you would instead like to display just two decimal places without rounding, you can type the following formula into cell B2:
You can then click and drag this formula down to each remaining cell in column B:
Notice that column B now shows each value from column A with two decimal places without rounding.
- 12.48832 is shown as 12.48
- 15.30092 is shown as 15.30
- 16.73482 is shown as 16.73
And so on.
Note: You can find the complete documentation for the TRUNC function in Excel here.
The following tutorials explain how to perform other common operations in Excel: