Method 1 – Applying Excel Formula to Compare Two Cells in Different Sheets
- Enter the following formula in cell D5 in Sheet1. Then use the fill handle icon to apply the formula to the cells below. This formula checks whether the respective cells from the two sheets are the same.
=C5=Sheet2!C5- You can apply the following formula in cell D5 in Sheet2 to check whether the respective cells are different.
=C5<>Sheet1!C5
Method 2 – Inserting Excel IF Formula to Compare Two Cells in Different Sheets
- Apply the following formula in cell D5 in Sheet1. It will check if the respective cells between the two sheets match each other.
=IF(C5=Sheet2!C5,"Match","No Match")- You can use the following formula in cell D5 in Sheet2 to get the same result.
=IF(C5<>Sheet1!C5,"No Match","Match")
Method 3 – Using VLOOKUP Formula to Compare Two Ranges in Different Excel Sheets
- Enter the following formula in cell D5 in Sheet1. This formula checks if the data in range C5:C14 are also present in the respective range in Sheet2. The ISNA function in the formula returns True if the VLOOKUP function returns #N/A. Otherwise, it returns False.
=IF(ISNA(VLOOKUP(C5,Sheet2!$C$5:$C$14,1,FALSE)),"No Match","Match")- You can use the following formula in cell D5 in Sheet2 to do the same.
=IF(ISNA(VLOOKUP(C5,Sheet1!$C$5:$C$14,1,FALSE)),"No Match","Match")
Method 4 – Comparing & Marking Two Cells in Different Sheets with Conditional Formatting Tool
Steps
- Select the desired cells (C5:C14) in Sheet1. Select Conditional Formatting >> New Rule from the Home tab. This will open a new dialog box.
- Choose to Use a formula to determine which cells to format as the rule type. Then enter the following formula in the field for Format values where this formula is true:.
=C5=Sheet2!C5- Select Format to open the Format Cells dialog box.
- Choose the desired color from the Fill tab and select the OK button.
- You will see a preview of what the cells will look like. Hit the OK button again.
- You will see the matching cells highlighted as follows.
Method 5 – Applying VBA to Compare & Highlight Two Cells in Different Sheets
Steps
- Press ALT+F11 to open the Microsoft Visual Basic for Applications window. Select Insert >> Module to open a new blank module, as shown in the following picture.
- Copy the following code.
Sub CompareCellsBetweenSheets()
Dim Names As Range
For Each Names In Worksheets("Sheet1").Range("C5:C14")
If Names = Worksheets("Sheet2").Cells(Names.Row, Names.Column) Then
Names.Interior.Color = vbGreen
End If
Next Names
End Sub- Paste the copied code on the blank module as shown below. Press F5 to run the code.
- You will see the matching cells in Sheet1 highlighted as follows.
Things to Remember
- Here the formulas use cell references of cells in different sheets in the same workbook. For cells in different sheets of different workbooks, you need to open the workbooks and use the respective cell references.
- Conditional formatting does not work for cells in different workbooks.
- The VBA code is applicable for cells in sheets in the same workbook. You need to change the sheet names according to your worksheets.
Download Practice Workbook
