How to compare numbers by two decimal places in Excel?
When working with numbers that contain decimal places in Excel - such as prices, measurements, or financial data - you may sometimes need to compare values up to a specific decimal precision rather than their full values. For example, you might want to check whether two numbers are the same when both are truncated to two decimal places, ignoring any digits beyond that point. Doing this manually can be time-consuming and prone to small errors, especially in large datasets.
This tutorial introduces several practical methods to compare numbers by their decimal precision in Excel:
- Compare decimal parts using IF and FLOOR functions
- Compare decimal parts using VBA macro
- Extract decimal parts using Extract Text
Compare decimal parts using IF and FLOOR functions
If you want a quick and straightforward way to compare two numbers by their decimal precision, you can use an Excel formula. The following formula checks whether two numbers are equal when both are floored (rounded down) to two decimal places:
=IF((FLOOR(A1,0.01)-FLOOR(B1,0.01))=0,1,-1)This formula works by flooring both values in cells A1 and B1 to two decimal places, then comparing the results.
- It returns 1 if the two values are the same when truncated to two decimals.
- It returns -1 if they differ.
 |  |  |
Note: In this formula, A1 and B1 represent the two numbers being compared. The parameter 0.01 tells Excel to compare the values up to two decimal places. You can change this value - for example, to 0.001 or 0.1 - if you need more or fewer decimal places.
Tip: If you prefer to round the numbers instead of truncating them, use this formula instead:
=IF((ROUND(A1,2)-ROUND(B1,2))=0,1,-1)In the formula, 2 refers to the number of decimal digits for comparison. Replace with another value as needed for your data.
Advantages: This method is easy for routine needs, fully dynamic, and works without any add-ins or extensions. It's especially suited for users familiar with formula-based solutions and scenarios where you want to quickly apply comparisons on-the-fly.
Limitations: Be aware that formulas relying on floating point arithmetic can sometimes produce unexpected results due to rounding errors, especially in cases involving very precise decimal values. Always check a few output values manually to ensure the logic aligns with your dataset.
Compare decimal parts using VBA macro
Automating decimal comparison across multiple rows is highly effective with VBA macros, especially when handling large datasets or requiring batch operations without manual intervention. This VBA approach extracts the decimal parts and compares them row by row, placing results directly into your worksheet for quick review or further processing.
Applicable scenarios: Ideal for users comfortable with Excel macros, handling repetitive decimal comparisons, or needing customized logic or reporting with minimal worksheet clutter.
1. Open Excel and navigate to Developer > Visual Basic (if the Developer tab is not visible, enable it via Excel options). In the VBA window, click Insert > Module, then paste the following code into the new module:
Sub CompareFlooredDecimals()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim valA As Double, valB As Double
Dim floorA As Double, floorB As Double
Dim resultCol As Long
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
resultCol = 3 ' Output column (C)
For i = 1 To lastRow
valA = ws.Cells(i, 1).Value
valB = ws.Cells(i, 2).Value
floorA = Int(valA * 100) / 100
floorB = Int(valB * 100) / 100
If floorA = floorB Then
ws.Cells(i, resultCol).Value = 1
Else
ws.Cells(i, resultCol).Value = -1
End If
Next i
End Sub2. Click the 
Run button to execute the macro. The results (“1” or “-1”) will appear in column C for each corresponding pair of numbers in columns A and B.
Parameter explanation: This macro compares the decimal parts of numbers in columns 1 (A) and 2 (B), and outputs the results to column 3 (C). Make sure the numbers you want to compare are entered in columns A and B before running the macro. You can modify resultCol = 3 if you want to display the output in a different column. It’s recommended to back up your data before running VBA macros, especially when working with large or important datasets.
Tips: For robust performance, always review the first few rows of results and check for blank cells, non-numeric data, or unusual formatting that may affect macro execution. If you encounter errors, ensure the data range is correct and columns are formatted as numbers.
Advantages: Fully automated, highly customizable, and eliminates repetitive manual work. Suitable for advanced users or when handling hundreds or thousands of rows. In case of different decimal digit lengths or rounding precision, you can easily adapt the code to compare up to a required number of decimal places using the Round function.
Extract decimal parts using Extract Text
If you want to extract certain digits after the decimal point instead of comparing two numbers directly, the Extract Text feature in Kutools for Excel offers a simple and efficient approach. It helps you quickly isolate a set number of decimal digits, which is especially useful when handling non-standard number formats or when you need to display or analyze decimal parts separately.
1. Select the two columns that contain the numbers whose decimal parts you want to extract, then go to Kutools > Text > Extract Text.
2. In the Extract Text dialog box, type .?? in the Text box to extract two digits after the decimal point, and then click Add to create the extraction rule. You can modify the pattern (for example, .??? for three digits) according to your required decimal length.
 | |  |
3. Ensure that only the rule you just added is selected under the Extract list section, and click OK.
4. Choose the starting cell for the extracted results, and click OK to finish the extraction.
Tip: If you want to compare the extracted decimal parts after using Extract Text, enter the following formula in a blank cell and copy it down as needed:
=IF((D1-E1)=0,1,-1)This will display 1 for matching decimal parts and -1 when they differ.
 | |  |
Advantages: Flexible for isolating specific decimal digits, ideal for customized extraction or when dealing with non-standard numeric data formats.
Limitations: Requires installation of Kutools for Excel. Extraction is a separate process and the setup may take slightly longer for first-time users.
Demo: How to compare two numbers by decimal only using Kutools for Excel
Troubleshooting and suggestions: When using any of the above solutions, always double-check that your number formats are consistent (numbers, not text), and avoid mixing thousand separators or symbols within compared columns. Especially when using formulas or VBA, unexpected results may stem from hidden formatting or blank cells - remove extra spaces and ensure your data range matches the formulas or macro logic. Back up your data before applying VBA, and if you encounter formula errors (such as #VALUE!), verify that your cell values are numeric and adjust references as necessary.
Related Articles
- How to remove leading zeros before decimal point in Excel?
- How to convert decimal hours/minutes to time format in Excel?
- How to convert decimal number to binary/octal/hex number or vice versa in Excel?
- How to generate random decimal/integer numbers in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in