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.
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. Applying Excel Formula 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
2. Inserting Excel 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")
3. Using VLOOKUP Formula to Compare Two Ranges in Different Excel 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")
4. Comparing & Marking Two Cells in Different Sheets with Conditional Formatting Tool
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.
- Finally, you will see the matching cells highlighted as follows.
5. Applying 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.
- 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.
Download Practice Workbook
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.
