What Is Highlight Duplicate Values In Excel?
The highlight duplicates in Excel is an option that enables one to identify and accentuate values occurring more than once in a chosen range. And we can use the Conditional Formatting feature to point out the duplicates in the desired format.
Users can highlight duplicates in massive datasets to quickly reduce data redundancy and improve data quality.
For example, the table below shows a list of fruits in column A.
And the requirement is to accentuate fruits repeating in column A. Then, we can use the Conditional Formatting Excel feature to find and highlight duplicates in Excel sheet.
In the above find and highlight duplicates in Excel example, we must select the cell range we wish to review for duplicate values. And then, choose the Duplicate Values option under the Highlight Cells Rules option in the Conditional Formatting feature in the Home tab to open the Duplicate Values window.
Next, the first field in the Duplicate Values window should be Duplicate. And we can set the required highlighting format in the second field using its drop-down button and list.
Further, once we select the required format in the Duplicate Values window, the options automatically highlight duplicates in Excel sheet.
Finally, we can click OK in the Duplicate Values window to exit from it and achieve the desired outcome.
Table of contents
Key Takeaways
- The highlight duplicates in Excelis a method that helps us point out the duplicate values in a select data range.
- Users can highlight duplicates in a worksheet when they need to locate and remove repetitive data in large datasets in one go.
- We can use the Duplicate Values option in the Conditional Formatting function in the Home tab to accentuate duplicate values in Excel.
- We can use a custom formula to highlight duplicates in Excel, based on inbuilt functions such as COUNTIF, in the New Rule option in the Conditional Formatting feature.
How To Highlight Duplicate Values In Excel?
The procedure to automatically highlight duplicates in Excel is as follows:
#1 – Choose the required cell range, where we must check and accentuate repeating values.
#2 – Choose the Home tab – select the Conditional Formatting feature – click the Highlight Cells Rules right arrow – choose the Duplicate Values option.
[Alternatively, we can use the shortcut keys Alt + H + L + H + D.]
#3 – The above step will open the Duplicate Values window.
#4 – The first field in the Duplicate Values window must be Duplicate.
Next, click the second field drop-down button to choose the required highlighting format.
#5 – The duplicate values in the chosen range will automatically get highlighted in the specified format. And click OK in the Duplicate Values window to exit it.
On the other hand, if we must point out duplicates in a chosen range based on a condition. Then, we can use the appropriate formula to highlight duplicates in Excel feature, Conditional Formatting, using inbuilt functions such as COUNTIF Excel function.
Examples
Check out the following highlight duplicates in Excel examples to use the option effectively.
Example #1 – Highlight Current Duplicates In The Selected Excel Range
The following table shows the top ten wealthiest cities in the US and their states.
The requirement is to point out the states in column B data range, having more than one richest city.
Then, we can highlight duplicates in Excel column B data range using the following steps:
Step 1: Choose the cell range B2:B11. And then select the Home tab – Conditional Formatting feature – Highlight Cells Rules right arrow – Duplicate Values option.
The Duplicate Values window opens, with the first field set as Duplicate.
Step 2: Click the second field drop-down button to choose the required highlighting format from the available drop-down list.
Step 3: Once we choose the required format in the Duplicate Values window, the duplicates in the chosen range get highlighted in the specified format.
Step 4: Click OK in the Duplicate Values window to close it and complete the highlight duplicates in Excel column B data range action.
Thus, we see the repeating US states, CA and IL, highlighted in the chosen Light Red Fill format in the selected range.
Example #2 – Highlight Future Duplicates In The Selected Range
The table below shows the grocery list for August 2023.
The task is to highlight the duplicates in the list in column A data range. And then accentuate the duplicate values if added to column A data in the future.
Then, the steps are as follows:
Step 1: Choose the cell range A2:A11. And then, select the Home tab – Conditional Formatting feature – Highlight Cells Rules right arrow – Duplicate Values option.
Step 2: The Duplicate Values window opens with the first field set as Duplicate. And we shall set the second field as Custom Format.
Step 3: The Excel Format Cells window will open.
Click the Font tab to open it. Set the font style as Bold in the Font style field and choose the required font color in the Color field using its drop-down button and list.
Click the Fill tab to open it and set the required cell color using the Background Color option.
And click OK.
Step 4: While the current duplicates in column A get highlighted in the specified custom format, we must click OK in the Duplicate Values window to close it.
Step 5: We shall now add new data to the existing grocery items list as the future values.
Select cell A12 and enter the required grocery item.
However, the entered value is a duplicate. So, when we press Enter, the new value gets highlighted in the specified custom format.
Next, choose cell A13, enter the required value, and press Enter.
In the above scenario, the entered value is unique. And hence, it does not get formatted.
Finally, choose cell A14, enter the required value, and press Enter.
Again, as the entered future value duplicates an existing value, pressing Enter will display the duplicate value in the chosen custom format.
Step 6: Choose cells A12:A14 and set the required border setting as the previous data using the Border option in the Home tab.
Hence, the final list of the current and future grocery items list, with the duplicates highlighted in a custom format, will appear as follows:
Example #3 – Remove Duplicates From The Selected Range
The following table lists the top US tech companies.
But the list contains duplicate values, and the requirement is to remove them from the list.
Then, here is how to use the Excel Remove Duplicates option to achieve the required output.
Step 1: Choose the cell range A2:A16. And then select Data – Remove Duplicates.
[Alternatively, choose the required range and apply the keyboard excel shortcut, Alt + A + M.]
The Remove Duplicates window will open, showing the columns in the current worksheet.
In this example, the current sheet contains only one column. And hence, the Remove Duplicates window shows only one column selected.
Step 2: Click OK in the Remove Duplicates window.
Finally, Excel shows a message stating the total duplicates found and removed in the chosen range. Also, it shows the count of the remaining unique values, which display as the final list in the worksheet.
Click OK in the message box to view the following output.
The Cautions Governing Duplicate Values
The cautions to consider while working on duplicate values are as follows:
- Ensure you choose the required data range instead of the entire column or columns to avoid incorrect cells getting highlighted for duplication.
- Ensure you select the correct column header in the Remove Duplicates window to eliminate the duplicates from the required column range.
Important Things To Note
- The keyboard shortcut to use the Duplicate Values option to highlight duplicates in Excel is Alt + H + L + H + D.
- The keyboard shortcut to use the Remove Duplicates option to delete the duplicate values from a chosen range is Alt + A + M.
- If the existing highlighting formats in the second field in the Duplicate Values window do not meet your requirements, set the second field as Custom Format. The Format Cells window will open, where you can set the required custom format to highlight duplicates in the chosen range.
Frequently Asked Questions (FAQs)
We can highlight duplicates in two columns in Excel using the following steps explained with an example.
The following table lists the top US states by population and area in columns A and B.
The requirement is to highlight the duplicates in the columns A and B data ranges.
Then, the steps are as follows:
Step 1: Choose the cell range A2:B11. And then select the Home tab – Conditional Formatting feature – Highlight Cells Rules right arrow – Duplicate Values option.
The Duplicate Values window opens. While the first field value in the window is Duplicate, the first format option in the second field drop-down list appears chosen in the second field. And we shall use the same to highlight the duplicates in the chosen range.
And as the second field value is already set, the duplicates in the chosen range appear accentuated in the specified format.
Step 2: Click OK in the Duplicate Values window to close it and achieve the required outcome.
The values in each column are unique. However, when we apply the Duplicate Values option on the selected range, Excel checks for duplicate values between the two columns’ data ranges. And then, the duplicates get highlighted in the specified format.
We can highlight duplicates in Excel with different colors using the following steps:
1. Choose the range where we wish to highlight duplicates in different colors.
2. Select the Home tab – Conditional Formatting feature – Highlight Cells Rules right arrow – Equal To option.
3. The Equal To window will open. Please enter the value we require to review for duplicity from the chosen range in the first field. And then, set the required highlighting format in the second field using its drop-down button and list.
4. Once we choose the format in the Equal To window, we can view all the occurrences of the specified value highlighted in the chosen format in the selected range.
5. Click OK to exit the Equal To window.
Likewise, we can repeat steps 1 to 5 to check every other value for duplicity and highlight the duplicates in a different color format.
Please note that if the entered value is unique, the above steps will highlight its one occurrence in the selected range.
We can get rid of highlight duplicates in Excel using the following steps:
1. Select the range containing the highlighted duplicate values in the active worksheet.
2. Choose the Home tab – Conditional Formatting feature – Clear Rules right arrow – Clear Rules from Selected Cells option.
Download Template
This article must be helpful to understand the Highlight Duplicates In Excel, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Highlight Duplicates In Excel. We learn to highlight current and future duplicates and remove them in Excel with examples. You can learn more from the following articles –
Leave a Reply