Summary

To highlight duplicate values in two or more columns, you can use conditional formatting with on a formula based on the COUNTIF and AND functions.

In the example shown, the formula used to highlight duplicate values is:

=AND(COUNTIF(range1,B5),COUNTIF(range2,B5))

Both ranges were selected at the same when the rule was created.

Generic formula

=AND(COUNTIF(range1,A1),COUNTIF(range2,A1))

Explanation 

This formula uses two named ranges, "range1" (B5:B12) and "range2" (D5:D10).

The core of this formula is the COUNTIF function, which returns a count of each value in both range inside the AND function:

COUNTIF(range1,B5) // count in range1
COUNTIF(range2,B5) // count in range2

COUNTIF will either return zero (evaluated as FALSE) or a positive number (evaluated as TRUE) for each value in both ranges.

If both counts are positive (i.e. non-zero), the AND function will return TRUE and trigger the conditional format.

Related formulas

Excel formula: Highlight duplicate values

Highlight duplicate values

COUNTIF simply counts the number of times each value appears in the range. When the count is more than 1, the formula returns TRUE and triggers the rule. When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the...
Excel formula: Highlight duplicate rows

Highlight duplicate rows

In the formula, COUNTIFS counts the number of times each value in a cell appears in its "parent" column. By definition, each value must appear at least once, so when the count > 1, the value must be a duplicate. The references are carefully locked so the formula will return true only when all 3...
Excel formula: Range contains duplicates

Range contains duplicates

In this example, the goal is to test if a given range contains duplicate values and return TRUE if duplicates exist and FALSE if not. This is essentially a counting problem and the solution is based on the COUNTIF function , which counts values in a range that meet supplied criteria. The formula...

Related functions

Excel COUNTIF function

COUNTIF Function

The Excel COUNTIF function returns the count of cells in a range that meet a single condition. The generic syntax is COUNTIF(range, criteria), where "range" contains the cells to count, and "criteria" is a condition that must be true for a cell to be counted. COUNTIF can be used to count...
Excel AND function

AND Function

The Excel AND function is a logical function used to test multiple conditions at the same time. AND returns TRUE only if all the conditions are met. If any conditions are not met, the AND function returns FALSE. The AND function is commonly used with other...

Related videos

How to apply conditional formatting with a formula

In this video, we'll look at how to use a formula to apply conditional formatting. Formulas allow you to make powerful and flexible conditional formatting rules that highlight just the data you want. Let's take a look. Excel provides a large number of conditional formatting presets, but there are...

Conditional formatting based on a different cell

In this video, we'll look at how to apply conditional formatting to one cell based on values in another, using a formula. Let's take a look. The easiest way to apply conditional formatting is to apply rules directly to the cells you want to format. For example, if I want to highlight the average...

How to highlight rows with conditional formatting

Using conditional formatting, It's easy to highlight cells that match a certain condition. However, it's a little trickier to highlight entire rows in a list that contains multiple columns. In this video, we'll show you how to use a formula with conditional formatting to highlight an entire row in...

How to build a search box with conditional formatting

In this video, we'll look at a way to create a search box that highlights rows in a table, by using conditional formatting, and a formula that checks several columns at once. This is a great alternative to filtering, because you can see the information you're looking for highlighted in context. Let...
Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Formulas

Functions

Videos

Articles

Training