How to Get 2 Decimal Places Without Rounding in Excel (4 Ways)

Get FREE Advanced Excel Exercises with Solutions!

In this Excel tutorial, you will learn how to get 2 decimal places without rounding in Excel.

Getting 2 decimal places without rounding in Excel can be done for both positive and negative values using Excel TRUNC function; using Excel ROUNDDOWN function and combining Excel FLOOR function with Excel IF function. Using the Excel Flash Fill feature, you can also get 2 decimal places in Excel without rounding easily. Getting 2 decimal places without rounding in Excel can be done for only positive values using Excel INT function and using Excel FLOOR.MATH function.

Note: We have used the Microsoft 365 version in this article. But all of these methods are shown here except the FLOOR.MATH function and the Flash Fill feature are applicable to any Excel version. The FLOOR.MATH function and the Flash Fill feature are available in Excel 2013 and later versions.

Suppose, you have a dataset with some measurements. It can be the latitudes or the longitudes of some places or some experimental values. The data may have decimal values up to 3, 4, or any decimal places like 36.169. Normally, when you show the values up to 2 decimal places, you show them with rounding like 36.17. This is the correct approach. But you can also show the values without rounding like 36.16. By the end of this article, you will have complete knowledge of getting 2 decimal places without rounding in Excel.

Overview of Getting 2 Decimal Places without rounding in Excel using the TRUNC function

Watch Video – Get 2 Decimal Places Without Rounding in Excel

Table of Contents hide

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

Method 1: Using TRUNC Function

Method 2: Using ROUNDDOWN Function

Method 3: Using FLOOR and IF Functions

2. Using Flash Fill Command to Get 2 Decimal Places without Rounding

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

Method 1: Using INT Function

Method 2: Using FLOOR.MATH Function

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

Getting 2 decimal places without rounding for both positive and negative values in Excel can be done by using Excel TRUNC function; using Excel ROUNDDOWN function and combining Excel FLOOR function with Excel IF function.

Method 1: 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. I want to get them up to 2 decimal places without rounding the values.

Here, I’ll use the TRUNC 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:

=TRUNC(C5,2)

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 ROUNDDOWN Function

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

Here, I’ll 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)

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.

Here, I’ll 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, I want to get the latitude value up to 2 decimal places without rounding. So, I input this formula in the cell D5:

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

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.

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. But you have to 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. Suppose 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:

If you don’t find the Flash Fill feature in your Excel sheet, you have to turn it on. 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.

Here, I’ll use the same dataset. First I’ll separate the positive and negative values of the dataset by inserting a row between them. Then, I’ll use the Flash Fill command separately on the positive and negative values to get the latitude values up to 2 decimal places without rounding.

Follow these steps:

Step 1: Separating positive and negative values

I have to separate the positive and negative values of my dataset first. So, I’ll 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 on the 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. So, I type 36.16 in cell D5.

Step 5: 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 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 and you’ll 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.

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

Getting 2 decimal places without rounding for only positive values in Excel can be done by using Excel INT function and using Excel FLOOR.MATH function.

Method 1: Using INT Function

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

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

Here, I’ll multiply the latitude value by 100 to move the decimal point 2 places to the right. Then, I’ll use the INT function to round down the result to the nearest whole number. After that, I’ll divide the rounded-down value by 100 to get the latitude value up to 2 decimal places.

Follow these steps:

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

=INT(C5*100)/100

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. But, 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.

Here, I’ll 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, I want to calculate the latitude value up to 2 decimal places without rounding. So, I input this formula in the cell D5:

=FLOOR.MATH(C5,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 across the cells D5:D10.

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

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.

How to Get 2 Decimal Places Without Rounding in Excel (4 Ways)

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

Method 1: Using TRUNC Function

Method 2: Using ROUNDDOWN Function

Method 3: Using FLOOR and IF Functions

2. Using Flash Fill Command to Get 2 Decimal Places without Rounding

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

Method 1: Using INT Function

Method 2: Using FLOOR.MATH Function

Related Articles

Leave a reply Cancel reply