Duplicates in Excel might be confusing for you at times. So, highlighting and removing duplicates in Excel is essential to make your data analysis easier. If you are experiencing this problem and are tired of receiving errors in Excel due to duplication, here is a simple breakdown on how to find, highlight, or remove duplicates in Excel.
Why Should You Use Conditional Formatting to Highlight Duplicates in Excel?
We will use conditional formatting to highlight duplicates in Excel, but why? Cannot we use another approach? So, the answer is that conditional formatting allows you to identify duplicates not only in Excel but also anytime fresh data is entered into your worksheet. So you don’t have to mark duplicates separately. Any time you add new data to Excel, it will be highlighted automatically.
Highlighting Duplicates in Excel: Step By Step
- Firstly, click and drag your mouse to select the row or column of data you want to check for duplicates in.
- Go to the “Home” tab on the Excel ribbon, then select “Conditional Formatting” from the Styles section.
- A dropdown menu will appear. Place your cursor over “Highlight Cells Rules”, then choose “Duplicate Values” from the dropdown menu.
- A dialog box will appear. Excel will highlight duplicate values by default. You can choose any color code to highlight duplicates in Excel. You can choose the format (e.g., light red fill with dark red text, yellow fill, green fill, etc.) or create a custom format.
- Once you’ve selected your preferred formatting, click “OK.” Excel will instantly highlight the duplicate values in the selected range.
Pro Uses: Highlighting Duplicates Using the COUNTIF Function
Sometimes you want to identify duplicates in the first occurrences of rows and columns, and sometimes in other places. You cannot use conditional formatting to highlight duplicates in Excel based on conditions. In this case, you can use the COUNTIF function to highlight duplicate rows, columns, or cells based on your criteria and specific conditions.
1st Case: Highlighting Duplicates Without 1st Occurrences
If you want to highlight the second, third, fourth, and all subsequent occurrences but not the first, follow these simple steps.
As with the previous method, open Conditional Formatting, click on “New Rule,” select “Use a formula to determine which cells to format,” and then enter the mentioned formula.
=COUNTIF($A$1:$A1,$A1)>1
Now, click Format and select the color from the Fill section to highlight duplicates.
Click OK to set the color and again OK to apply the formula.
As you can see in the image, the duplicates are highlighted except for the first occurrence.
2nd Case: To Highlight Other Subsequent Records
If you want to highlight further subsequent records, such as the third, fourth, and so on, use this formula.
=COUNTIF($A$1:$A1,$A1)>=3=COUNTIF($A$1:$A1,$A1)>=4 And if you want to only highlight 2nd or third instances, you can use the equal to sign in the formula like that.
=COUNTIF($A$2:$A2,$A2)=3 or =COUNTIF($A$2:$A2,$A2)=33rd Case: Highlighting Multiple Columns
If you want to highlight duplicates in numerous columns or across a range of cells, then you can modify your COUNTIF formula. For example, if you want to highlight duplicates in numerous columns, including the first occurrence, your formula would look like this.
=COUNTIF($A$1:$D$8,A1)>1 
4th Case: Highlighting Multiple Columns Without First Occurrences
If you want to highlight duplicates in multiple columns without first appearing in the first column and in all other columns, simply change the formula slightly.
For only the first column:
=COUNTIF($A$1:$A1,$A1)>1For all subsequent columns:
=COUNTIF(A$1:$A$8,B1)+COUNTIF(B$1:B1,B1)>15th Case: Highlighting Duplicate Rows Based on One Column
If your data table has too many columns and you want to highlight duplicate rows based on a single column’s duplicate values, you may simply change your formula.
Including first occurrences:
=COUNTIF($A$1:$A$8, $A1)>1Except for first occurrences:
=COUNTIF($A$1:$A1, $A1)>16th Case: Highlighting Entire Rows
In the previous use case, duplicates in rows were highlighted based on certain columns; however, if you want to highlight complete duplicate rows based on several columns, use this formula.
Including First occurrences:
=COUNTIFS($A$1$A1, $A1, $B$1:$B1, $B1)>1Excluding First Occurrences:
=COUNTIFS($A$1:$A$8, $A1, $B$1:$B$8, $B1)>1
7th Case: Highlighting Consecutive Duplicate Cells in Excel
If you want to highlight duplicated cells in Excel that are next to each other, as first in A1 and second in A2, it is also possible.
With the first occurrences:
=OR($A1=$A2, $A2=$A3)Without first occurrences:
=$A1=$A2Removing Duplicates in Excel
Now, after finding and highlighting all the duplicates in Excel using specific conditions, it’s time to remove them. If you want to remove duplicates in Excel, follow these steps.
- First, select the row or column in which you want to remove duplicates.
- After selection, go to Data and then click “Remove Duplicates”. A dialog box will appear here.
- If you selected a table with multiple columns, you’ll see checkboxes for each column. Tick the columns you want Excel to compare for duplicates.
- If you tick all columns, Excel will only remove completely identical rows. If you tick just one column, Excel will remove duplicates based only on that column.
- Once your columns are selected, click “OK.” After that, Excel will show a message just like that.
Note: Before removing duplicates in Excel, you should copy your original data because the duplicates will be permanently deleted. To avoid accidentally removing essential data, make a copy of your original data.
Troubleshooting: Why Isn’t Excel Highlighting Duplicates?
Sometimes Excel fails to detect and highlight duplicates. There may be various causes that you should consider and address.
The first reason could be that your data sheet has spaces and hidden characters in your spreadsheet. This is a common issue when you copy data from external sources. In this scenario, you can use the TRIM and CLERAN functions to remove unnecessary whitespace and hide stuff from your spreadsheet.
The second reason could be that some of the duplicate values are represented as text in Excel. In this scenario, you can use the VALUE and TEXT functions to standardize values sorted as text in Excel.
The third cause could be that your data is presented in both upper and lowercase. Excel is case sensitive; therefore, if your data has various entries, use the UPPER, LOWER, and PROPER functions in Excel. The UPPER function converts all text to uppercase, the LOWER function to lowercase, and PROPER to capitalize the initial character of each word.
Pro Tips to Enhance Your Duplicate Search
- Always keep a copy of your original data because deleting duplicates will result in their permanent deletion.
- Use the TRIM and CLEAN functions in Excel before highlighting duplicates to avoid errors and difficulties.
- Make sure that all of your text values are in the same format. If half of your data is in uppercase and half in lowercase, Excel will not indicate duplicates.
- Always use the COUNTIF function in Excel to highlight duplicates based on specific conditions.
- Excel also allows you to highlight unique values, which can be important for data cleansing tasks. Follow the same methods for conditional formatting, except in the dialog box, pick “Unique” rather than “Duplicate.”
- You can use filters on your data to see only the highlighted duplicates. To find duplicates, sort by color or filter by cell color.
Alternatives to Conditional Formatting for Detecting Duplicates
While Conditional Formatting is great for visual inspection, sometimes you may need other methods:
- Pivot Tables: Summarize and count occurrences.
- COUNTIF / COUNTIFS Formulas: Create helper columns to mark duplicates.
- Power Query: For advanced users, Power Query can help clean and transform data efficiently.
Highlight Duplicates in Excel: Wrap Up
Finding and managing duplicate values doesn’t have to be difficult. With tools like conditional formatting, you can easily highlight duplicates in Excel and keep your data clean. Whether you use built-in features or formulas like COUNTIF, Excel makes it simple to spot and fix repeated entries.
So, the next time you need Excel to find duplicates, you’ll know exactly what to do. A quick Excel duplicate values highlight can make all the difference in accurate and error-free data.
