Highlight Cells If There Are More Than 3 Duplicates in Excel (3 Examples)

Sometimes, we have more than one cell in our Excel worksheet which contains the same cell values. Either it’s due to a mistake in the data entry or there are multiple cells with the same data. Whatever the reason is, we may need to Highlight those cells. In this article, we will show you the effective ways to Highlight Cells If There Are More Than 3 Duplicates in Excel with some examples.


Download Practice Workbook

Download the following workbook to practice by yourself.


3 Examples to Highlight Cells If There Are More Than 3 Duplicates in Excel

1. Conditional Formatting Feature to Highlight Cells from Multiple Rows If There Are More Than 3 Duplicates

Excel provides many different Features and we use them to carry out various operations. One of such kinds is the Conditional Formatting feature. This feature is very helpful to us whenever we need to format the cells based on certain criteria. In our first method, we’ll use this feature to Highlight Cells from Multiple Rows If There Are More Than 3 Duplicates in Excel. To illustrate, we will use the below dataset as an example. Therefore, follow the steps below to perform the task.

Use Conditional Formatting Feature to Highlight Cells from Multiple Rows If There Are More Than 3 Duplicates in Excel

STEPS:

  • First, select the range B5:D10.
  • Next, select New Rule from the Conditional Formatting drop-down under the Home tab.

Use Conditional Formatting Feature to Highlight Cells from Multiple Rows If There Are More Than 3 Duplicates in Excel

  • As a result, the New Formatting Rule dialog box will pop out.
  • There, select the 6th Rule Type, marked as 1 in the following picture.
  • Then, in Format values where this formula is true box (marked as 2), type the formula:
=COUNTIF($B$5:$D$10, B5)>3
  • After that, press Format (marked as 3).

Use Conditional Formatting Feature to Highlight Cells from Multiple Rows If There Are More Than 3 Duplicates in Excel

Here, the COUNTIF function counts the total number of cells based on criteria. $B$5:$D$10 is the criteria range and B5 is the criteria. Then, it’ll format the cells which appear more than 3 times as we applied Greater Than 3.

  • Consequently, the Format Cells dialog box will appear.
  • Under the Fill tab, select any color to highlight the cells with and press OK.
  • In this example, select the Green color.

  • Subsequently, it’ll take you to the previous dialog box and press OK.
  • Finally, you’ll see the cells which appear more than 3 times in Green colors.


2. Highlight Cells When There Are More Than 3 Duplicates with Excel COUNTIF Function

In this method, we’ll use the Excel COUNTIF function to Highlight Cells. This function counts the number of cells based on a condition.


2.1 From Individual Row

Here, we’ll show how to highlight cells from Individual Row at first. In the below dataset, we’ll work with the 8th row. So, follow the process to carry out the operation.

Highlight Cells When There Are More Than 3 Duplicates with Excel COUNTIF Function

STEPS:

  • Firstly, select the range B8:F8.
  • Next, select Home Conditional FormattingNew Rule.
  • Consequently, a dialog box will pop out.
  • There, select the 6th Rule Type.
  • Then, type the formula:
=COUNTIF($B$8:$F$8,B8)>3
  • After that, press Format.

Highlight Cells When There Are More Than 3 Duplicates with Excel COUNTIF Function

  • As a result, the Format Cells dialog box will pop out.
  • Under the Fill tab, select the Green color and press OK.

  • Eventually, you’ll see the highlighted cells in the 8th row.


2.2 From Individual Column

Now, we’ll show how to Highlight cells from Individual Column. For instance, we’ll use column C as an example in the below dataset. Hence, learn the following steps to perform the task.

Highlight Cells When There Are More Than 3 Duplicates with Excel COUNTIF Function

STEPS:

  • First of all, select C5:C10.
  • Then, under the Home tab, select New Rule from the Conditional Formatting drop-down.
  • Consequently, a dialog box will emerge and select the 6th Rule Type.
  • Afterward, type the formula:
=COUNTIF($C$5:$C$10,C5)>3
  • Subsequently, choose the Green color to highlight the cells and press OK.

Highlight Cells When There Are More Than 3 Duplicates with Excel COUNTIF Function

  • At last, it’ll return the highlighted cells in column C.


3. Highlight Future Duplicates in the Selected Range

Additionally, we can also highlight Future Duplicate cells. That means, if we input any repetitive cell values in the future, Excel will highlight those cells by itself. In the following dataset, we’ll show how to carry out the operation with column B. Therefore, learn the process below.

Highlight Future Duplicates in the Selected Range

STEPS:

  • Select entire column B at first.
  • Then, go to Home Conditional Formatting New Rule.
  • As a result, a dialog box will pop out. Here, select the 6th Rule Type.
  • Afterward, in Format values where this formula is true box, type the formula:
=COUNTIF($B:$B,$B1)>3
  • Next, select the Green color from Format and press OK.

Highlight Future Duplicates in the Selected Range

  • Lastly, it’ll return the green-colored cells which appear more than times.

  • Now, type Simon in cell B11 and press Enter.
  • Consequently, you’ll see that the cell is highlighted.

Highlight Future Duplicates in the Selected Range


Highlight Rows If There Are More Than 3 Duplicates in Excel

Moreover, we can have duplicate rows in our Excel worksheet and we may also need to highlight them for different reasons. To perform the task, we’ll apply the COUNTIFS function in the Conditional Formatting feature. This function helps us to count the number of cells based on multiple criteria. For example, in the below dataset, we have 5th, 7th, 8th and 10th rows that are identical to each other. So, follow along to know how to highlight the duplicate rows.

Highlight Rows If There Are More Than 3 Duplicates in Excel

STEPS:

  • Firstly, select the range B5:D10.
  • Then, select Home Conditional Formatting New Rule.
  • In the pop-up dialog box, select the 6th Rule Type.
  • Subsequently, type the formula:
=COUNTIFS($B$5:$B$10,$B5,$C$5:$C$10,$C5,$D$5:$D$10,$D5)>3
  • After that, select the Green color and press OK.

Highlight Rows If There Are More Than 3 Duplicates in Excel

Here, we have 3 ranges in the argument section: $B$5:$B$10, $C$5:$C$10, and $D$5:$D$10. The criteria are $B5, $C5, and $D5 respectively. And it’ll format only if there are more than 3 duplicates.

  • In the end, it’ll return the duplicate rows in highlighted colors.


Conclusion

Henceforth, you will be able to Highlight Cells if There Are More Than 3 Duplicates in Excel with the above-described methods. Keep using them and let us know if you have any more ways to do the task. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Aung

Aung

I'm Aung. Recently I've earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo