Excel Formula to Compare Two Cells in Different Sheets (3 Examples)

This article illustrates how to use excel formula to compare two cells in different sheets. Now and then, you might need to compare data in different worksheets in excel. This article will help you to do that. The following picture highlights the purpose of this article.

Excel Formula to Compare Two Cells in Different Sheets


Download Practice Workbook

You can download the practice workbook from the download button below.


3 Formulas to Compare Two Cells in Different Excel Sheets

Imagine you have the following dataset in Sheet1 and Sheet2 containing the top 10 baby girl names in the USA in 2020 and 2021 respectively. Now you want to compare the names from the two sheets to see if the ranking has changed. Then the following 3 formulas might be helpful to do that.


1. Simple Formula Using Equal to and Inequality Signs to Compare Two Cells in Different Sheets

To create a simple formula, execute the following steps.

  • 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
  • Alternatively, 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

Read More: Compare Two Cells in Excel and Return TRUE or FALSE (5 Quick Ways)


2. IF Formula to Compare Two Cells in Different Sheets

You can use the formula with the IF function 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")
  • Alternatively, 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

Read More: How to Compare Text in Excel and Highlight Differences (8 Quick Ways)


3. VLOOKUP Formula to Compare Two Ranges of Cells in Different Sheets

You can also use the VLOOKUP function to compare two ranges of cells in two different 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")
  • Alternatively, 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


2 More Methods to Compare Two Cells in Different Excel Sheets

Here I am going to show you two more methods to compare two cells in different excel sheets.

1. Conditional Formatting to Compare & Highlight Two Cells in Different Sheets

You can compare and highlight two cells in different sheets using conditional formatting. Follow the steps below to be able to do that.

๐Ÿ“Œ Steps

  • First, select the desired cells (C5:C14) in Sheet1. Then select Conditional Formatting >> New Rule from the Home tab. This will open a new dialog box.

  • Now 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
  • Next select Format to open the Format Cells dialog box.

  • Now choose the desired color from the Fill tab and select the OK button.

  • After that, you will see a preview of what the cells will look like. Then hit the OK button again.

Conditional Formatting to Compare & Highlight Two Cells in Different Sheets

  • Finally, you will see the matching cells highlighted as follows.

Read More: Compare Two Cells Using Conditional Formatting in Excel (3 Methods)


2. Excel VBA to Compare & Highlight Two Cells in Different Sheets

You can also use Excel VBA to compare and highlight two cells in different sheets. Follow the steps below to be able to do that.

๐Ÿ“Œ Steps

  • First, press ALT+F11 to open the Microsoft Visual Basic for Applications window. Then select Insert >> Module to open a new blank module as shown in the following picture.

  • After that 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
  • Now paste the copied code on the blank module as shown below. Then press F5 to run the code.

Excel VBA to Compare & Highlight Two Cells in Different Sheets

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

Conclusion

Now you know how to use Excel formula to compare two cells on different sheets. Please use the comment section below. You can also visit our blog to read more on excel. Stay with us and keep learning.


Related Articles

Md. Shamim Reza

Md. Shamim Reza

Hello there! This is Md. Shamim Reza. Working as an Excel & VBA Content Developer at ExcelDemy. We try to find simple & easy solutions to the problems that Excel users face every day. Our goal is to gather knowledge, find innovative solutions through them and make those solutions available for everybody. Stay with us & keep learning.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo