Excel Formula to Compare Two Cells in Different Sheets: 4 Methods

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

Simple Formula(Using =, <>) to Compare Two Cells in Different Sheets


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

IF Formula to Compare Two Cells in Different Sheets


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

VLOOKUP Formula to Compare Two Ranges of Cells in Different Sheets


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.

Conditional Formatting to Compare & Highlight Two Cells in Different Sheets

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

Excel VBA to Compare & Highlight Two Cells in Different Sheets

  • 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


<< Go Back To Excel Compare Cells | Compare in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Md. Shamim Reza
Md. Shamim Reza

Md. Shamim Reza, a marine engineer with expertise in Excel and a fervent interest in VBA programming, sees programming as a time-saving tool for data manipulation, file handling, and internet interaction. His diverse skill set encompasses Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning. He holds a B.Sc in Naval Architecture & Marine Engineering from BUET and has transitioned into a content developer role, generating technical content focused on Excel and VBA. Beyond his professional pursuits,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo