Finding Duplicate Data Across Worksheets

Tzvi has a workbook that has multiple worksheets containing names and addresses. He can use conditional formatting to highlight duplicate data on a single worksheet. He wonders, though, if there is a way to highlight duplicate data appearing on multiple worksheets. For instance, if a customer name appears on the first worksheet and on the fourth worksheet in the workbook, is there a way to highlight them?

There are a couple of ways you can approach this issue. One way, of course, is to simply merge the data from your worksheets into a single worksheet, and then use conditional formatting to highlight duplicates on that one worksheet, as you are already know how to do.

If you don't want to merge the worksheet data, then you could use conditional formatting, but it would take a bit of time. First, let's make some assumptions—your data is contained in Sheet1, Sheet2, Sheet3, and Sheet4. The customer names are in a single column (column A).

The first step is to display each worksheet, in turn, select the cells in column A, and then set a conditional formatting rule that highlights duplicates. This takes care of any duplicates that may exist on the worksheets themselves.

Next, go to Sheet1, select the cells in column A, and define a new conditional formatting rule that uses a formula to determine which cells to format. Here is the formula that should be in the rule:

=COUNTIF(Sheet2!$A:$A,A1)+COUNTIF(Sheet3!$A:$A,A1)+COUNTIF(Sheet4!$A:$A,A1) > 0

This rule checks the contents of column A in Sheet1 against those in the other three worksheets. You then need to set up a similar rule in the other worksheets, making sure that the formula refers to the other worksheets in the workbook.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7837) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.