The COUNTIF function in MS Excel can be used quite easily to return TRUE and resultantly highlight the cells that contain duplicate values or values that occur specific times.
To highlight the cells that contain duplicate values we just need to use Excel COUNTIF function. We can use a very easy formula that will return TRUE when a range of cells contain text or number occurring specific times. The formula with an example is discussed below.
Formula or Syntax:
How the function works
When we apply the Conditional Formatting to highlight cells, MS Excel uses the active cell from the selection to evaluate the formula. The formula then uses the COUNTIF function to identify the cells that have duplicate values. After that, the SEARCH function returns the cells that have duplicate values and these cells are resultantly highlighted.
Here we will apply the COUNTIF formula in Excel Worksheet and understand how to highlight cells that contain duplicate values step by step,
- In the example, we have text in a range of cells E8 till H13.
Figure 1. Text range
- Now we want to highlight the cells that appear more than 3 times. So we will use the following formula with Conditional Formatting.
Figure 2. Generating Conditional Formatting Rule
Figure 3. Applying COUNTIF Formula
- The cells with text appearing more than 3 times got highlighted.
Figure 4. The result of Conditional Formatting
Notes on usage of Conditional Formatting
- While using Conditional formatting, it’s very important that the formula should be entered relative to the active cell from the selection.