Highlight duplicate values

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:

=COUNTIF(data,A1)>1

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.

Example

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.

=COUNTIF($E$8:$H$13,E8)>3

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.
Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar