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.

Dataset of Getting 2 Decimal Places without rounding in Excel

Follow these steps:

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)

Using TRUNC function to get 2 decimal places without rounding in Excel

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).

Showing 2 decimal places without rounding in Excel using TRUNC function

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.

Showing result after using Fill Handle tool

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.

Follow these steps:

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)

Using ROUNDDOWN function to get 2 decimal places without rounding in Excel

Showing 2 decimal places without rounding in Excel using ROUNDDOWN function

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.

Showing result after using Fill Handle tool

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.

Follow these steps:

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))

Combining FLOOR and IF functions to get 2 decimal places without rounding in Excel

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).

Showing 2 decimal places without rounding in Excel using FLOOR and IF functions

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.

Showing result after using Fill Handle tool

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.

Dataset to get 2 decimal places without rounding in Excel using Flash Fill command

Follow these steps:

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.

Selecting row 8 to insert a new row between row 7 and 8

Step 2: Separating positive and negative values

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

Selecting Insert command to insert a row between row 7 and 8

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.

A new row inserted between positive and negative values

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.

Typing value in cell D5 up to 2 decimal places without rounding

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).

Using Fill Handle tool to copy the value to other cells

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.

Selecting Auto Fill Options to apply Flash Fill command

Step 7:

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

Applying Flash Fill command to get 2 decimal places without rounding for positive values in Excel

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.

Showing 2 decimal places without rounding for positive values in Excel after applying Flash Fill command

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.

Showing 2 decimal places without rounding for positive and negative values in Excel after applying Flash Fill command separately

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

First way:

Step 1: Type 36.16 in cell D5.

Typing value in cell D5 up to 2 decimal places without rounding

Step 2:

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

Applying Flash Fill command from Data tab to get 2 decimal places without rounding for positive values in Excel

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

Showing 2 decimal places without rounding for positive values in Excel after applying Flash Fill command

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.

Applying Flash Fill command using keyboard shortcut to get 2 decimal places without rounding for positive values in Excel

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.

Dataset of Getting 2 Decimal Places without rounding in Excel for only positive values

Follow these steps:

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

Using INT function to get 2 Decimal Places without rounding in Excel for only positive values

Showing 2 Decimal Places without rounding in Excel for only positive values using INT function

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.

Showing result after using Fill Handle tool

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.

Follow these steps:

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)

Using FLOOR.MATH function to get 2 Decimal Places without rounding in Excel for only positive values

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).

Showing 2 Decimal Places without rounding in Excel for only positive values using FLOOR.MATH function

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.

Showing result after using Fill Handle tool

Download Workbook

Getting 2 Decimal Places without Rounding.xlsx

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.