The following tutorial of how to highlight duplicate values in Excel using the built-in conditional formatting feature. Finding duplicate entries in Excel can be done in a few different ways. we will learn today "How to Highlight Duplicate Values in Excel" using Conditional Formatting.
Watch the video to learn more about the Find Duplicate Entries in Excel -> https://youtu.be/GlOZtWIGu6M
Steps to Highlight Duplicate Values
1 - Select the Range: Click and drag to select the range of cells where you want to find and highlight duplicates
2 - From the Home tab, in the Styles group, click Conditional Formatting.
3 - Choose Duplicate Values: From the dropdown menu of “Conditional Formatting”, Hover your mouse on “Highlight Cell Rules" And Click on Duplicate Values…
4 - Set Formatting:
While clicking on “Duplicate Values…” the following box will appear.
- Make sure the Duplicate is selected in the first dropdown.
- Choose a format, You can either proceed with the default formatting setting (i.e. “Light Red Fill with Dark Red Text”) from the second dropdown.
- Then Click OK.
Note: select Unique from the first drop-down list to highlight the unique names.
How to identify duplicates in Excel With Formula
The COUNTIF function in Excel allows you to detect duplicates. Watch the video to learn more about the COUNTIF function in Excel -> https://youtu.be/GlOZtWIGu6M
The COUNTIF() is executed first as it is the innermost function.
Here's a formula to find duplicates in Excel including first occurrences (where B2 is the topmost cell in the example):
1 - Select a column next to the column where you want to check for duplicates
2 - Enter the COUNTIF Formula as below
As you can see in the screenshot above, the formula returns 2 for duplicates. it will be 1 for a unique.
Here's what this formula does:
- $B$2:$B$7 is the range where you want to check for duplicates (adjust this range based on your data).
- B2 is the cell you're checking for duplicates within that range.
Now, select C2 and drag the fill handle to copy the formula down to other cells: (remember to lock that range with the $ sign)
Steps to Return TRUE or FALSE for Duplicates
For a duplicate formula to return something more meaningful like TRUE and FALSE
As you can see in the screenshot below, C2 will show TRUE; otherwise, it will show FALSE.
Duplicate values in a sheet can cause a lot of trouble and take a lot of time to cleanse. This article gives a detailed explanation of how to identify, filter, and remove duplicate values.
If you have any other questions or need further examples, let us know and upload the question in our forum tapgen.xyz
Learn MS Excel with the world's largest web developer site. Not sure where to begin? Learn MS Excel with Tapgen step-by-step tutorial.
=================================