How to Set Decimal Places in Excel with Formula (5 Ways)

Suppose we have a dataset that contains values with five decimal places, but we want to only display two decimal places. In this article, we demonstrate 5 effective ways to set decimal places in Excel with a formula (and 2 ways to do it without using a formula).

To illustrate the methods, we’ll use the following dataset, containing the GPA of five students, and set the values of GPA to two decimal places instead of five.

Method 1 – Using the ROUND Function

The ROUND function rounds a number, either up or down, to the number of digits specified.

STEPS:

Select cell D5.
Enter the following formula:

=ROUND(C5,2)

Press Enter.
Drag the Fill Handle tool from cell D5 to D9.

The values of GPA now display with two decimal places.

Method 2 – Using the ROUNDUP Function

The ROUNDUP function rounds a value up to the decimal point on the right or left.

STEPS:

Select cell D5.
Enter the following formula:

=ROUNDUP(C5,2)

Hit Enter.
Drag the Fill Handle tool from cell D5 to D9.

The GPA values now display two decimal places with upper rounding.

Method 3 – Using the ROUNDDOWN Function

The ROUNDDOWN function in Excel rounds a number down.

STEPS:

Select cell D5.
Enter the following formula:

=ROUNDDOWN(C5,2)

Press Enter.
Drag the Fill Handle tool from cell D5 to D10.

Results up to two decimal places are displayed.

Method 4 – Using the TRUNC Function

The TRUNC function in Excel truncates a number to a specified number of digits.

STEPS:

Select cell D5.
Enter the following formula:

=TRUNC(C5,2)

Hit Enter.
Drag the Fill Handle tool from cell D5 to D10.

The values of GPA are trunctated to two decimal places.

Method 5 – Combining FLOOR and IF Functions

The FLOOR function in Excel rounds down both the integer number and decimal number to the nearest specified multiple of significance. The IF function is used to check if a condition is met and return different results depending on whether the condition is met or not.

STEPS:

Select cell D5.
Enter the following formula:

=FLOOR(C5, IF(C5>0, 0.01, -0.01))

Press Enter.
Drag the Fill Handle tool from cell D5 to D9.

The values of GPA are limited to two decimal places.

How Does the Formula Work?

IF(C5>0, 0.01, -0.01): This section calculates the value of the given numbers to two decimal places. If the value in C5 is positive and greater than 0, it will return 01 rather than -0.01.
FLOOR(C5, IF(C5>0, 0.01, -0.01)): In this part, we use the FLOOR function to pass the cell number, which is C5 as well as the IF function’s return value.

How to Set Decimal Places in Excel Without Using a Formula

Method i – Apply Excel ‘Format Cells’ Option

To illustrate this method we will use the following dataset containing GPAs of five students in two semesters, along with a column showing the CGPA of students. Let’s set the values of CGPA up to two decimal places using the Format Cells feature.

STEPS:

Select the cell range E5:E9.
Right-click on the selected area.
Click Format Cells from the context menu.

A new dialogue box named Format Cells appears.
Select Number from the Category section.
Set the value 2 in the Decimal places box.
Click OK.

The result is as follows:

Method ii – Using the ‘Increase Decimal’ and ‘Decrease Decimal’ Buttons

STEPS:

Select the cell range E5:E9.
Go to the Home tab.
From the Number section on the ribbon, click the ‘Decrease Decimal’ button 3 times.