How to Get 2 Decimal Places Without Rounding in Excel: 4 Methods

Method 1 – Getting 2 Decimal Places without Rounding for Both Positive and Negative Values in Excel

Using TRUNC Function

Using Excel TRUNC function, we can get 2 decimal places without rounding in Excel easily.

I have a dataset with City Name and Latitude. The latitudes are in positive and negative values and are up to 3 or 4 decimal places. We want to get them up to 2 decimal places without rounding the values.

Use the TRUNC function to get the latitude values up to 2 decimal places without rounding them.

Step 1: In cell D5, Get the latitude value up to 2 decimal places without rounding. Input this formula in the cell D5:

`=TRUNC(C5,2)`

Step 2: Click on the Enter button on your keyboard to find the following output. Hover your mouse over the bottom right corner of cell D5, you will find the Fill Handle icon (Green Plus).

Step 3: Double-click on the Fill Handle icon to obtain all the latitude values up to 2 decimal places without rounding across the cells D5:D10.

Method 2 – Using ROUNDDOWN Function

Using Excel ROUNDDOWN function, we can also get 2 decimal places without rounding.

Use the same dataset and use the ROUNDDOWN function to get the latitude values up to 2 decimal places without rounding them.

Step 1: In cell D5, I want to get the latitude value up to 2 decimal places without rounding. So, I input this formula in the cell D5:

`=ROUNDDOWN(C5,2)`

Step 2: Click on the Enter button on your keyboard to find the following output. Hover your mouse over the bottom right corner of cell D5, you will find the Fill Handle icon (Green Plus).

Step 3: Double-click on the Fill Handle icon to obtain all the latitude values up to 2 decimal places without rounding across the cells D5:D10.

Method 3 – Using FLOOR and IF Functions

Combining Excel FLOOR and IF functions together, we can find out the 2 decimal places without rounding.

Use the same dataset and use the FLOOR and IF functions to get the latitude values up to 2 decimal places without rounding.

Step 1: In cell D5, get the latitude value up to 2 decimal places without rounding. Input this formula in the cell D5:

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

Step 2: Click on the Enter button on your keyboard and you will find the following output. Hover your mouse over the bottom right corner of cell D5, you will find the Fill Handle icon (Green Plus).

Step 3: Double-click on the Fill Handle icon to obtain all the latitude values up to 2 decimal places without rounding across the cells D5:D10.

Method 2 – Using Flash Fill Command to Get 2 Decimal Places without Rounding

Using the Excel Flash Fill feature, you can easily get 2 decimal places in Excel without rounding. Apply the Flash Fill feature separately on the positive and negative values.

Flash Fill is a useful feature in MS Excel that automatically fills your data when it finds a pattern. Column A contains first names and column B contains last names. You want full names in column C. So, if you type the full name in a cell of column C and create a pattern, the Flash Fill feature will fill the rest of the cells for you.

Availability: The Flash Fill feature is available in Excel 2013 and later versions.

Caution:

• You have to turn it on if you don’t find the Flash Fill feature in your Excel sheet. Go to File tab >= Options menu >= Advanced tab >= Editing Options menu >= Check the Automatically Flash Fill
• If you change the values in the original column, the values of the column where you apply the Flash Fill feature won’t change.

The keyboard shortcut for the Flash Fill feature is Ctrl + E.

Use the same dataset. Separate the positive and negative values of the dataset by inserting a row between them. Use the Flash Fill command separately on the positive and negative values to get the latitude values up to 2 decimal places without rounding.

Step 1: Separating positive and negative values

Separate the positive and negative values of my dataset first. Insert a new row between the positive and negative values to make them separate.

Select the whole row 8 by clicking on the row number 8.

Step 2: Separating positive and negative values

Right-click to open the Context menu => Locate the Insert command.

Step 3: Separating positive and negative values

Click Insert command to insert a new row between the positive and negative values. They are separate now.

Step 4: In cell D5, I want to get the latitude value up to 2 decimal places without rounding. Type 36.16 in cell D5.

Step 5: Click Enter button on your keyboard and you will find the following output. Hover your mouse over the bottom right corner of cell D5, you will find the Fill Handle icon (Green Plus).

Step 6: Drag the Fill Handle icon up to cell D7 to copy the value across the cells D5:D7. Locate the Auto Fill Options tool at the bottom right corner of cell D7.

Step 7:

Click on the Auto Fill Options tool => the Context menu will open => Locate the Flash Fill command.

Step 8: Select the Flash Fill command, and you’ll get the latitude values up to 2 decimal places without rounding for only positive values.

Step 9:

Repeat the steps from Step 4 to get the latitude values up to 2 decimal places without rounding for negative values across cells D9:D11.

You can also use the Flash Fill command in these two ways:

First way:

Step 1: Type 36.16 in cell D5.

Step 2:

Press Enter => Select cell D5 => Go to Data tab => You will get the Flash Fill command.

Step 3: Click on the Flash Fill command to get the following output.

Second way: Keyboard Shortcut to use Flash Fill feature:
Type 36.16 in cell D5 => Press Enter => Press the keyboard shortcut Ctrl + E, and you will get the following result.

Method 3 – Getting 2 Decimal Places without Rounding for Only Positive Values in Excel

Using the Excel INT function and the Excel FLOOR. MATH function can give 2 decimal places without rounding for only positive values in Excel.

Method 1: Using INT Function

Using Excel INT function, we can get 2 decimal places without rounding for only positive values in Excel.

The same dataset with City Name and Latitude. I want to get them up to 2 decimal places without rounding the values.

Multiply the latitude value by 100 to move the decimal point 2 places to the right. Use the INT function to round down the result to the nearest whole number. Divide the rounded-down value by 100 to get the latitude value up to 2 decimal places.

Step 1: In cell D5, I want to calculate the latitude value up to 2 decimal places without rounding. Input this formula in the cell D5:

`=INT(C5*100)/100`

Step 2: Click on the Enter button on your keyboard to find the following output. Hover your mouse over the bottom right corner of cell D5, you will find the Fill Handle icon (Green Plus).

Step 3: Double-click on the Fill Handle icon to obtain all the latitude values up to 2 decimal places across the cells D5:D10.

You’ll get the positive values up to 2 decimal places without rounding. You’ll get the negative values up to 2 decimal places with rounding.

Method 2: Using FLOOR.MATH Function

Using Excel FLOOR.MATH function, you can also get 2 decimal places without rounding for only positive values in Excel.

Note: The FLOOR.MATH function is available in Excel 2013 and later versions.

Use the same dataset and use the FLOOR.MATH function to get the latitude values up to 2 decimal places without rounding for only positive values.

Step 1: In cell D5, calculate the latitude value up to 2 decimal places without rounding. Input this formula in the cell D5:

`=FLOOR.MATH(C5,0.01)`

Step 2: Click the Enter button on your keyboard to find the following output. Hover your mouse over the bottom right corner of cell D5, you will find the Fill Handle icon (Green Plus).

Step 3: Double-click on the Fill Handle icon to obtain all the latitude values up to 2 decimal places across the cells D5:D10.

You’ll get the positive values up to 2 decimal places without rounding. Get the negative values up to 2 decimal places with rounding.

I have shown you several methods and examples of getting 2 decimal places without rounding in Excel. I have used the TRUNC function, the ROUNDDOWN function, and the combination of the FLOOR and IF functions to get 2 decimal places without rounding for both positive and negative values. We have also shown a case, where you can apply the Flash Fill command separately for positive and negative values to get the 2 decimal places without rounding. I have used the INT function and the FLOOR.MATH function to get 2 decimal places without rounding for only positive values. If you know any other methods or you face any problems related to getting 2 decimal places without rounding in Excel, let us know in the comment box.

Related Articles

<< Go Back to Round Up Decimals | Rounding in Excel | Number Format | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Sajid Ahmed

Sajid Ahmed, a BSc graduate in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, assumes the position of an Excel & VBA Content Developer at ExcelDemy. A self-motivated individual, his profound interest in research and innovation aligns seamlessly with his passion for Excel. In this role, Sajid not only adeptly addresses challenges but also demonstrates enthusiasm and expertise in gracefully navigating complex situations. This underscores his steadfast commitment to consistently delivering exceptional content. His interests... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF