In this article, we will learn how to highlight duplicates in Excel, find duplicates, and remove the duplicates in Excel. This option is quite basic but still very useful. There are different ways to highlight duplicates. Here, we mainly used conditional formatting with different formulas to cover the different situations of highlighting duplicates. We also used formulas to find duplicates and then highlight those duplicates.
This feature is quite important and useful while working on a dataset. If you highlight the duplicates, then you can easily identify the duplicate value and simply avoid those values, you can also get a clearer visuality of the dataset, better visualization, and you can analyze data in a better way as well.
Highlight Duplicates in Excel: 7 Ways
Here we will learn how to highlight duplicates in Excel using different formulas in conditional formatting for different situations.
1. Highlight Duplicates Using Conditional Formatting
- Initially, select range C5:E10 and go to Home tab >> Conditional Formatting >> Duplicate Values to select duplicates.
- Now, the Duplicate Values dialog box will pop up.
- Select Duplicates and select the color from the Format cells that contain:
- Last, the final output will be like below.
2. Highlight duplicates Along Rows
- In the beginning, select the rows from 5 to 10 and go to the Home tab >> Conditional Formatting >> New Rule… to highlight duplicates.
- Then, the Edit Formatting Rule dialog box pops up, and you enter the formula in Edit the Rule Description.
=COUNTIFS($C$5:$C$10, $C5, $D$5:$D$10, $D5)>1- After that, change the format color accordingly.
- Finally, the rows are highlighted below.
3. Highlight Duplicates Along Columns
- First, select a column by clicking on the column bar and go to the Home tab >> Conditional Formatting >> Duplicate Values… to get the duplicates.
- Now, the duplicate values dialog box will pop up, and select Duplicate from the “Format cells that contain” option along with selecting the color.
- Now, the final output will be similar to the one below.
4. Highlight Duplicates for a Specific Range
This example is quite similar to the previous one, but instead of selecting any column, select a specific range. Here, select range C5:E10.
5. Highlight Duplicates Except for the First Value
- First, select the range B5:E10, and select New Rules from the Conditional Formatting option in Toolbar.
- Then, write the formula in the Edit the Rule Description box and click OK to highlight duplicates.
=COUNTIF($C$5:$C5,$C5)>1
- Last, the output will be similar to that below.
6. Highlight Duplicates of an Entire row Based on a Column
- In the beginning, select the range and open the Edit Formatting Rule dialog box using the same process already shown and write the formula in Edit the Rule Description
=COUNTIF($C$5:$C$10, $C5)>1
- Here is the final output after executing the full process.
7. Highlight Duplicates Across Multiple Sheets
- Initially, select cell F5 and enter the VLOOKUP function and the IFERROR function to get the duplicate value from another sheet, and drag down the Fill handle to execute the full process.
=IFERROR(VLOOKUP(C5,'Dataset 2'!$C$5:$C$10,1,0),"")
- Now, open the Edit Formatting Rule from Conditional Formatting and write another formula to highlight the duplicate values.
=C5=F5
Here is the final output from highlighting duplicates.
Here is the final output from highlighting duplicates.
How to Find Duplicates in Excel: 2 Ways
We can also find duplicates using different formulas. Here we will show how to find duplicates using different formulas.
1. Combining IF and COUNTIFS Functions to Find Duplicates
In this example, we will use the IF function and the COUNTIFS function to find duplicates.
- Initially, select cell F5 and enter the formula to get the duplicate values.
- Then drag down the fill handle until cell F10 completes the process.
=IF(COUNTIFS($C$5:$C$10,$C5,$D$5:$D$10,$D5,$E$5:$E$10,$E5)>1,"Duplicate","Unique")Formula Breakdown
COUNTIFS($C$5:$C$10,$C5,$D$5:$D$10,$D5,$E$5:$E$10,$E5)>1
- Here, the COUNTIFS function returns the value as True or False. If there is a duplicate, then this part will return True; otherwise, it will return False.
IF(COUNTIFS($C$5:$C$10,$C5,$D$5:$D$10,$D5,$E$5:$E$10,$E5)>1,”Duplicate”,”Unique”)
- The formula returns the value Duplicate if there is any duplicate value; otherwise, it will return the value as Unique.
2. Using COUNTIFS Function to Find Duplicates
Now, we will use The COUNTIFS function to find the duplicates here.
- First, select cell F5 and enter the formula to get the duplicate values.
=COUNTIFS($C$5:$C$10,$C5,$D$5:$D$10,$D5)>1
How to Remove Duplicates in Excel: 3 Ways
Here we will remove duplicates in Excel using formulas and options from the Toolbar.
1. Using Remove Duplicates Command from Toolbar
- Initially, select range C5:E10 and go to Home tab >> Data Tools >> Remove Duplicates from the toolbar.
- Then, Remove Duplicates dialog box will pop up, and select the cells as per requirement.
- Last, click on OK to complete the process.
- The duplicate values are removed as below.
2. Using the UNIQUE Function
- To get the unique values, only enter the UNIQUE function in cell G5 and get the final output as an array.
=UNIQUE(C5:E10)
3. Applying Advanced Filter Options
- First, select the range B5:D10 and go to Data >> Sort & Filter >> Advanced from the toolbar.
- Then, the Advanced Filter dialog box will pop up as below.
- Select the List Range and Criteria Range as B5:D10 and check on the Unique Records Only in the dialog box.
- Now, finally, click OK to complete the process.
- Here, the output will be similar to that below.
Things to Remember
- You cannot remove any data while they merge any cells in that column or row. First, you need to unmerge the cells. Then you can remove the duplicates.
- You can use the COUNTIF function instead of the COUNTIFS function if you are working on a single column or row.
Frequently Asked Questions
Q1: What is the shortcut key for highlighting duplicates in Excel?
Ans: The shortcut key for highlighting duplicates in Excel is “Alt+H+L+H+D”.
Q2: How do I highlight in Excel but not delete duplicates?
Ans: If you want to highlight duplicates without deleting any duplicates, then select Data >> Data Tools >> Remove Duplicates from the toolbar. Here, only duplicates will be removed.
Q3: What is the fastest way to highlight in Excel?
Ans: Select any cell in the worksheet and then press CTRL + A to highlight all the cells in a single sheet.
Download Practice Workbook
You may download the workbook below for practice.
Conclusion
Here, we learned how to highlight duplicates in Excel using different formulas and different options from the toolbar. Here we also learned how to find duplicates with formulas and how to remove duplicates in Excel. We covered every solution to highlight duplicates in Excel in this article. Hopefully, you can solve the problem shown in this article. Please let us know in the comment section if there are any queries or suggestions.
