KutoolsforOffice — One Suite. Five Tools. Get More Done.

How to find duplicate or unique values between two worksheets in Excel

AuthorSunLast modified

When working with multiple worksheets in Excel, you may need to compare data to find duplicate values, unique values, or differences between two sheets. Sometimes you only want to check whether a value exists in another worksheet, while other times you need to compare corresponding cells row by row.

In this tutorial, we’ll walk through several practical ways to compare two worksheets in Excel. You’ll learn how to highlight duplicate or unique values, mark matching results with formulas, extract matching data into a separate list, and compare corresponding cells between worksheets.

Compare whether values exist in another worksheet

This type of comparison checks whether each value in one worksheet can be found anywhere in another worksheet. The matching value does not need to be in the same row or the same cell position.

Mark values as Duplicate or Unique with a helper column

If you want a clear result beside each value, use a helper column. This method returns Duplicate if the value exists in the other worksheet, and Unique if it does not.

  1. Suppose the values you want to check are in Sheet1!A2:A10, and the comparison list is in Sheet2!A:A.
  2. In Sheet1, enter the following formula in B2: 
    =IF(COUNTIF(Sheet2!A:A,A2)>0,"Duplicate","Unique")
  3. Drag the fill handle down to apply the formula to the other cells.

Each value in Sheet1 is now labeled as Duplicate if it exists in Sheet2, or Unique if it does not.

Mark values as duplicate or unique with a helper column

📝 Note:

You can also use MATCH, VLOOKUP, or XLOOKUP to check whether a value exists in another worksheet. COUNTIF is usually simpler when you only need to know whether the value appears or not.

Highlight duplicate or unique values between two worksheets

If you only need to visually identify duplicate or unique values, Conditional Formatting with COUNTIF is a good choice.

  1. Select the range in the first worksheet, for example Sheet1!A2:A10.
  2. Click Home > Conditional Formatting > New Rule.
  3. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
  4. Enter one of the following formulas based on the result you want:
    • To highlight duplicate values, enter this formula: 
      =COUNTIF(Sheet2!A:A,A2)>0
    • To highlight unique values instead, use this formula: 
      =COUNTIF(Sheet2!A:A,A2)=0
  5. Click Format, choose a fill color, and click OK.
    Create a new conditional formatting rule
  6. Click OK again to apply the rule.

The matching cells will now be highlighted automatically.

Highlight duplicate values between two worksheets

Select and highlight same or different values between two worksheets

Kutools for Excel allows you to quickly find, select, and highlight duplicate or unique values between two worksheets. Since the matching or different cells can be selected directly, it is also easier to copy, delete, format, or edit the results in bulk.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Integrated with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...
  1. After installing Kutools for Excel, click Kutools > Select > Select Same & Different Cells.
  2. In the Select Same & Different Cells dialog box:
    • In the Find values in box, select the range where you want to select or highlight duplicate or unique values.
    • In the According to box, select the comparison range in the second worksheet.
  3. Select Single cell under Based on if you want to compare values regardless of their positions.
  4. Select Same Values to find duplicate values, or select Different Values to find unique values.
  5. Check Fill backcolor if you want to highlight the results, then click OK.
    Select Same & Different Cells dialog box

The duplicate or unique values will now be selected and highlighted in the first worksheet.

Select and highlight same or different values between worksheets

Kutools for Excel - Packed with over 300 essential tools for Excel. Make Excel tasks faster, easier, and more efficient. Download now!

Extract duplicate or unique values into a separate list

If you want to extract duplicate or unique values into a separate list instead of marking the original data, you can use the FILTER function.

📝 Note:

The FILTER function is only available in Excel for Microsoft 365 and Excel 2021 or later versions.

  1. Click a blank cell where you want to output the result.
  2. To extract values from Sheet1 that also exist in Sheet2, enter this formula: 
    =FILTER(Sheet1!A2:A10,COUNTIF(Sheet2!A:A,Sheet1!A2:A10)>0)
  3. Press Enter to return the duplicate values.
    Extract duplicate values into a separate list

💡 Tip:

To extract unique values that do not exist in the second worksheet, use this formula:

=FILTER(Sheet1!A2:A10,COUNTIF(Sheet2!A:A,Sheet1!A2:A10)=0)

Compare corresponding cells between two worksheets

This type of comparison compares two worksheets cell by cell to check whether the values in the same positions match or differ. For example, Sheet1!A2 is compared only with Sheet2!A2.

Mark corresponding cells as Match or Different with a helper column

If you want a readable result beside each row, use an IF formula to return Match or Different.

  1. In Sheet1, click a blank cell beside the first row you want to compare.
  2. Enter the following formula: 
    =IF(A2=Sheet2!A2,"Match","Different")
  3. Drag the fill handle down to compare the other corresponding cells.
    Mark corresponding cells as Match or Different

📝 Note:

If you only need TRUE or FALSE results, you can use this shorter formula:

=A2=Sheet2!A2

Highlight matching or different corresponding cells

You can also use Conditional Formatting to compare corresponding cells between two worksheets and highlight either matching cells or different cells.

  1. Select the range in the first worksheet, for example Sheet1!A2:A10.
  2. Click Home > Conditional Formatting > New Rule.
  3. In the New Formatting Rule dialog box, select Use a formula to determine which cells to format.
  4. Enter one of the following formulas based on the result you want:
    • To highlight cells that are different from the corresponding cells in another worksheet, enter: 
      =A2<>Sheet2!A2
    • To highlight cells that match the corresponding cells in another worksheet, enter: 
      =A2=Sheet2!A2
  5. Click Format, choose a fill color, and click OK.
    Create a conditional formatting rule to compare corresponding cells
  6. Click OK again to apply the rule.

Excel will now highlight the matching or different cells in the selected range based on the formula you used.

Highlight matching or different corresponding cells between two worksheets

Select and highlight matching or different corresponding cells

Kutools for Excel can compare two worksheets cell by cell and quickly identify matching or different cells in the same positions. Compared with formulas or Conditional Formatting, this method can directly select or highlight the results for further editing, formatting, or deletion.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Integrated with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...
  1. After installing Kutools for Excel, click Kutools > Compare Cells.
  2. In the Compare Cells dialog box:
    • In the Find values in (Range A) box, select the range where you want to find matching or different cells.
    • In the According to (Range B) box, select the corresponding comparison range in the second worksheet.
  3. Under the Find section, select one of the following options based on the result you want:
    • Same cells: Find matching corresponding cells.
    • Different cells: Find different corresponding cells.
  4. Under Processing of results, specify whether to fill the background color or font color for the results, and then click OK.
    Compare Cells dialog box in Kutools for Excel

The matching or different corresponding cells will now be selected and highlighted in the first worksheet.

Select and highlight matching or different corresponding cells between two worksheets

Frequently Asked Questions

What is the difference between comparing values and comparing corresponding cells?

Comparing values checks whether a value from one worksheet exists anywhere in another worksheet, regardless of its position. Comparing corresponding cells checks whether cells in the same positions, such as A2 and Sheet2!A2, are identical.

Will formulas update automatically if the worksheet data changes?

Yes. Formula-based methods such as COUNTIF, IF, and FILTER update automatically when the source data changes.

Can I highlight duplicate values between two worksheets automatically?

Yes. You can use Conditional Formatting together with a COUNTIF formula to automatically highlight duplicate or unique values between two worksheets.

What is the difference between Select Same & Different Cells and Compare Cells in Kutools for Excel?

Select Same & Different Cells compares whether values exist in another range regardless of their positions. Compare Cells compares cells in the same positions row by row or cell by cell.

Why are my corresponding cells marked as different even though they look the same?

This can happen if the cells contain extra spaces, hidden characters, different number formats, or different text cases.

Can I compare worksheets in different workbooks?

Yes. Most formulas, Conditional Formatting rules, and Kutools features can compare ranges located in different workbooks.

Can I compare entire worksheets of different workbooks and highlight all differences automatically?

Yes. Kutools for Excel provides a Compare Worksheets feature that can compare two worksheets at once and automatically highlight different cells, cells existing only in the main worksheet, and cells existing only in the lookup worksheet.

Compare Worksheets dialog box in Kutools for Excel

Conclusion

Comparing data between worksheets can help you quickly identify duplicate values, unique values, matching cells, and differences between two datasets. Depending on what you need, you may want to mark the results with helper columns, highlight them visually, select them for further editing, or extract them into a separate list.

It is also important to distinguish between comparing whether values exist in another worksheet and comparing corresponding cells cell by cell, because these two types of comparisons serve different purposes and produce different results.

By choosing the right comparison approach for your situation, you can review worksheet differences more accurately and work with large datasets more efficiently.

I hope you found this tutorial helpful. If you’d like to explore more Excel tips and practical solutions, please click here to browse our full collection of tutorials.