1. Highlight duplicate values
By going through the previous two chapters, you have established a strong understanding of conditional formatting. In this lesson, we are going to use the skills that you built to apply conditional formatting to solve a common problem with spreadsheets - finding duplicate values!
2. The COUNTIF function
The COUNTIF function takes a range of cells and counts the number of times that a criteria is satisfied in that range. The COUNTIF function is what we will build our custom formula with so we'll spend a little time working with it before we use it for conditional formatting.
3. COUNTIF example
In this example, the COUNTIF formula is returning the number of times the word Apple appears in column A. The syntax of A colon A, used for the criteria, is a way to reference an entire column, since the COUNTIF function will need to look at all of the values in a column to find duplicates.
Using A colon A to evaluate an entire column can be a handy shortcut to use and you will see it later in this course. Keep in mind, though, that it will also look at your column header if you have one, which may or may not be a problem.
Notice that apple is capitalized in row 3 but not in row 5. The COUNTIF function is not case sensitive so it will highlight words as a match if they have the same letters in the same order regardless of how they are capitalized.
4. COUNTIF repeated
We are looking at the same data in this table as in the previous table, but we have repeated the COUNTIF function for every row. When we use COUNTIF for conditional formatting, this is how it will be applied.
A value in the result column is greater than one if the criteria given to it is duplicated within the given range.
We are going to write the conditional formatting custom formula to highlight a cell if the COUNTIF formula results in a number greater than 1. In this example, the highlighting would be triggered for both instances of the word apple since the result is greater than 1 for those rows.
5. Duplicates highlighted
Here is the same data in a spreadsheet with the rule applied. It's highlighting all of the cells that are duplicated.
This formula works the same way if you are looking for duplicate numbers, dates, and so on.
You can also look for values that occur 3 or more times by changing the 1 to a 2 or greater. Most likely, though, you will be using the formula to find duplicates.
6. Let's practice!
Now you will get a chance to apply this trick in the following exercises.