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
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 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 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 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.
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 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.
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
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
- How to Set Decimal Places in Excel with Formula
- How to Roundup a Formula Result in Excel
- How to Add ROUND Formula to Multiple Cells in Excel
- How to Round to Nearest Whole Number in Excel
- How to Round Down to Nearest Whole Number in Excel
- Round to Nearest 5 or 9 in Excel
- How to Round Numbers to the Nearest Multiple of 5 in Excel
- Excel VBA: Round to Nearest 5
- Round Down to Nearest 10 in Excel
- How to Round to Nearest 100 in Excel
- How to Round Numbers to Nearest 10000 in Excel
- How to Round a Formula with SUM in Excel
- Round off Formula in Excel Invoice
- How to Round up Decimals in Excel
- How to Round Up to 2 Decimal Places in Excel
- How to Round Percentages in Excel
- How to Set Rounding Precision in Excel
- How to Round Time in Excel
- How to Round Time to Nearest Minute in Excel
- Round Time to Nearest 5 Minutes in Excel
- How to Round Time to Nearest 15 Minutes in Excel
- Rounding Time in Excel to Nearest Hour
- Rounding Time to Nearest Quarter Hour in Excel
- Rounding to Nearest Dollar in Excel
- How to Round to Nearest 10 Cents in Excel
- How to Round Off to Nearest 50 Cents in Excel
- How to Round Excel Data to Make Summations Correct
- How to Stop Rounding in Excel
- How to Stop Excel from Rounding Large Numbers
- How to Remove Decimals in Excel with Rounding
- How to Remove Decimals without Rounding in Excel
- How to Stop Excel from Rounding Up Decimals
- How to Round a Multiplication Formula in Excel
- How to Round Numbers in Excel Without Formula