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

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

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

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

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

2.1 From Individual Row

2.2 From Individual Column

3. Highlight Future Duplicates in the Selected Range

Highlight Rows If There Are More Than 3 Duplicates in Excel

Conclusion

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.

STEPS:

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

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).

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.

STEPS:

Firstly, select the range B8:F8.
Next, select Home ➤ Conditional Formatting ➤ New 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.

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.

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.

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.

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.

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

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

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.

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.

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.

Download Practice Workbook

Download the following workbook to practice by yourself.

Highlight Cells for Duplicates.xlsx

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.

2 Comments

RUBY Apr 5, 2023 at 11:49 PM

I keep getting “Youve entered too few arguments for this function” and i cannot get around it 🙁
- Reply
  Lutfor Rahman Shimanto Apr 6, 2023 at 11:42 AM
  
  Hello Ruby,
  
  Greetings from our website. You have encountered a warning message from the Microsoft Excel window. The error message “You’ve entered too few arguments for this function” means not providing enough arguments or inputs for a particular formula or function to work correctly. To resolve this error, check the formula or function you’re using and ensure you’ve provided all the required arguments.
  
  This article explains how we can highlight cells with more than three duplicates. But, if you need help understanding this post, you can follow another article on the ExcelDemy website link given below.
  How to Do Conditional Formatting in Excel [Ultimate Guide]
  
  Later go through this article to better understand and highlight if there are more than three duplicates. Good luck.
  
  Regards
  Lutfor Rahman Shimanto

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