Note: Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight duplicate values. However, if you want more flexibility, you can highlight duplicates with your own formula, as explained in this article.
If you want to highlight cells that contain duplicates in a set of data, you can use a simple formula that returns TRUE when a value appears more than once.
For example, if you want to highlight duplicates in the range B4:G11, you can use this formula:
Note: with conditional formatting, it's important that the formula be entered relative to the "active cell" in the selection, which is assumed to be B4 in this case.
How this formula works
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 rule is created. In this case, the range we are using in COUNTIF is locked with an absolute address, but B4 is fully relative. So, the rule is evaluated for each cell in the range, with B4 changing and $B$4:$G$11 remaining unchanged.
A variable number of duplicates + named ranges
Instead of hard-coding the number 1 into the formula you can reference a cell to make the number of duplicates variable.
You can extend this idea and make the formula easier to read by using named ranges. For example, if you name G2 "dups", and the range B4:G11 "data", you can rewrite the formula like so:
You can then change the value in G2 to anything you like and the conditional formatting rule will respond instantly, highlighting cell that contain values greater than or equal to the number you put in the named range "dups".
COUNTIF is a function to count cells that meet a single criteria. COUNTIF can be used to count cells with dates, numbers, and text that match specific criteria. The COUNTIF function supports logical operators (>,...
In this video, we look at how to make conditional formatting variable, so that you can change how formatting is applied without editing a rule. This elegant technique helps you build killer spreadsheets that are fun to use.
In this Exceljet tip, we show you how to highlight entire rows in a list using conditional formatting with a formula. This is a handy way to visually expose information you are interested in without having to apply a filter. We also show you how to...
In this video, we use conditional formatting to create an interactive search box to highlight data. This is a nice alternative to filtering, because the information you're looking is highlighted in context, and the search box checks multiple...
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.