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.

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

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:

• 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:

• 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:

• Subsequently, choose the Green color to highlight the cells and press OK.

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

Read More: How to Highlight Duplicates in Two Columns Using Excel Formula

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:

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

Read More: Highlight Duplicates across Multiple Worksheets in Excel

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:

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

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.

Related Articles

• How to Highlight Duplicates but Keep One in Excel
• How to Highlight Duplicates in Excel with Different Colors
• How to Highlight Duplicates in Two Columns in Excel
• How to Highlight Duplicates in Multiple Columns in Excel
• How to Highlight Duplicate Rows in Excel
• [Fix:] Highlight Duplicates in Excel Not Working

<< Go Back to Highlight Duplicates in Excel | Duplicates in Excel | Learn Excel