Highlight Cells If There Are More Than 3 Duplicates in Excel

Example 1 – Using the Conditional Formatting Feature to Highlight Cells from Multiple Rows If There Are More Than 3 Duplicates

This is the sample dataset.

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

STEPS:

Select B5:D10.
In the Home tab, go to Conditional Formatting and select New Rule.

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

In the New Formatting Rule dialog box, select the 6th Rule Type (as shown below in 1).
In Format values where this formula is true, enter the formula:

=COUNTIF($B$5:$D$10, B5)>3

Click Format.

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

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. It will format the cells which appear more than 3 times.

In the Format Cells dialog box, select Fill.
Choose a color to highlight the cells, here Green.
Click OK.

Click OK.

The cells which appear more than 3 times are highlighted in Green.

Example 2 – Highlight Cells When There Are More Than 3 Duplicates with the Excel COUNTIF Function

2.1 From Individual Rows

Consider the 8th row.

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

STEPS:

Select B8:F8.
Go to Home ➤ Conditional Formatting ➤ New Rule.
In the dialog box, select the 6th Rule Type.
Enter the formula:

=COUNTIF($B$8:$F$8,B8)>3

Click Format.

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

In the Format Cells dialog box, select Fill.
Choose a color to highlight the cells, here Green.
Click OK.

You’ll see the highlighted cells in the 8th row.

2.2 From Individual Columns

Consider column C.

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

STEPS:

Select C5:C10.
Go to Home ➤ Conditional Formatting ➤ New Rule.
In the dialog box, select the 6th Rule Type.
Enter the formula:

=COUNTIF($C$5:$C$10,C5)>3

Choose Green to highlight the cells and click OK.

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

This is the output.

Example 3 – Highlight Future Duplicates in the Selected Range

Consider column B.

Highlight Future Duplicates in the Selected Range

STEPS:

Select column B.
Go to Home ➤ Conditional Formatting ➤ New Rule.
In the dialog box, select the 6th Rule Type.
In Format values where this formula is true, enter the formula:

=COUNTIF($B:$B,$B1)>3

Choose Green to highlight the cells and click OK.

Highlight Future Duplicates in the Selected Range

This is the output.

Enter Simon in B11 and press Enter.
The cell is highlighted.

Highlight Future Duplicates in the Selected Range

Highlight Rows If There Are More Than 3 Duplicates in Excel

The 5th, 7th, 8th and 10th rows are identical.

Highlight Rows If There Are More Than 3 Duplicates in Excel

STEPS:

Select B5:D10.
Go to Home ➤ Conditional Formatting ➤ New Rule.
In the dialog box, select the 6th Rule Type.
Enter the formula:

=COUNTIFS($B$5:$B$10,$B5,$C$5:$C$10,$C5,$D$5:$D$10,$D5)>3

Choose Green to highlight the cells and click OK.

Highlight Rows If There Are More Than 3 Duplicates in Excel

There are 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. The formula will format cells with more than 3 duplicates.

This is the output.

Download Practice Workbook

Download the following workbook.

Highlight Cells for Duplicates.xlsx

<< Go Back to Duplicates in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

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

Example 1 – Using the Conditional Formatting Feature to Highlight Cells from Multiple Rows If There Are More Than 3 Duplicates

Example 2 – Highlight Cells When There Are More Than 3 Duplicates with the Excel COUNTIF Function

2.1 From Individual Rows

2.2 From Individual Columns

Example 3 – Highlight Future Duplicates in the Selected Range

Highlight Rows If There Are More Than 3 Duplicates in Excel

Related Articles

Leave a reply Cancel reply