You can use formulas and built-in rules to highlight differences between two lists. You might try that route to compare two completely different data sets, but COUNTIFS() is easy and will get the job done. In this article, we’ll combine COUNTIFS() with a filter and a conditional format–you choose which best suits your needs.

I’m using Excel 2016 (desktop) on a Windows 10 system, but you can use both techniques in earlier versions. You can work with your own data or download the demonstration .xls and .xlsx files. The browser edition will support conditional formatting rules, but you can’t apply a custom rule in the browser.

Note: this is the third of three articles in a series. For the previous articles, see How to use Excel’s conditional formatting to compare lists and How to use Excel formulas to compare multi-column lists.

The example data

The two data sets shown in Figure A share only one record, rows 5 and 13. The other records share common values in two columns, but not all three. In this case, we don’t care about repetitive data within the same column. We care only when those values combine to create an entire duplicate record. You could, however, adjust the COUNTIFS() function to find subsets instead of an entire record.

Figure A

We’ll expose the duplicates between the two data sets.

To quickly expose record duplicates, we’ll first add a COUNTIFS() function that returns 1 if there’s a duplicate. The COUNTIFS() function uses the following syntax to specify multiple criteria to determine which values in a range to count:

COUNTIFS(countrange1, criteria1, [countrange2, criteria2]...)

This example includes all three columns for a full-record comparison, but you could compare partial records by including only the columns you want to compare. In addition, if the data sets are on different sheets, be sure to include the sheet identifier.

SEE: 20 quick Windows tips for power users (free PDF) (TechRepublic)

COUNTIFS() and filter

In this first example, we’ll combine the results of a COUNTIFSIO function with a filter. To get started, enter the following function in E3 and copy to the remaining rows in the same data set:

=COUNTIFS($B$11:$B$15,B3,$C$11:$C$15,C3,$D$11:$D$15,D3)

As you can see in Figure B, only one record returns 1–the record in row 5. Notice that the functions in E3:E7 are referencing corresponding columns in rows 11 through 15 and comparing those values to the values in the first data set. That’s possible because the criteria in each argument set is a relative reference.

Figure B

One record occurs in both data sets.

In such a simple data set, it’s easy to spot the duplicate, but it won’t be so easy when the sheet is filled with lots of records. To complete the task, you can use a filter to display only the duplicate record in the first data set as follows:

  1. Select the cell above the COUNTIFS() functions, E2.
  2. Click the Data tab and then click Filter in the Sort & Filter group. Doing so will add filter dropdowns to columns B through E.
  3. From the dropdown in cell E2, uncheck (Select All) and check 1, as shown in Figure C.
  4. Click OK to see the filtered set shown in Figure D.

Figure C

Filter by the value 1, which denotes a duplicate record.

Figure D

The filtered data set.

If you’re like me, you probably find a filter less than satisfying; you only see the duplicate in one data set. You could add a second set of COUNTIFS() and filter the second set too, but you see only the duplicates. If you want to distinguish duplicates without filtering, try conditional formatting.

SEE: Top Windows 10 questions: How to install, secure, upgrade, get it for free (ZDNet)

Conditional formatting and COUNTIFS()

By combining conditional formatting and the same COUNTIFS() functions, you can achieve a bigger picture. The first data set is easy, but first remove the filter you added in the last section. Then, do the following:

  1. Select B3:D7.
  2. Click the Home tab, click Conditional Formatting in the Styles group, and choose New Rule from the dropdown list.
  3. Choose the Use a formula to determine which cells to format option.
  4. Enter =$E3=1 in the Formula control.
  5. Click Format, click the Fill tab, choose a color, and click OK. Figure E shows the format and the rule.
  6. Click OK to return to the sheet shown in Figure F.

Figure E

Enter a rule and choose a format.

Figure F

The new rule highlights the only duplicate, row 5.

Next, add the following COUNTIFS() functions to the second data set:

=COUNTIFS($B$3:$B$7,B11,$C$3:$C$7,C11,$D$3:$D$7,D11)

Now, repeat the steps above, but select B11:D15 in step 1, and enter the formula =$E11=1 in step 4. Figure G shows the results. In our example, the duplicate rows are in the same position within the data set, but the solution works regardless of their positions. It’s a unique solution for a unique situation.

Figure G

Conditional formatting highlights both duplicate records without filtering.

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.