How to Find Duplicates in Excel
First, let’s learn how to highlight duplicates in Excel with an example. Suppose you need to find duplicate product codes in two columns, labeled “Segment 1” and “Segment 2”.
- Step 1: Select the codes in “Segment 1”. To also select codes in “Segment 2” without losing your first selection, hold down the Ctrl key and click on the second column.
- Step 2: Click on the ‘Home’ tab at the top of Excel. Then, under the ‘Styles’ section, click ‘Conditional Formatting’. Choose ‘Highlight Cells Rules’, then ‘Duplicate Values’.
- Step 3: In the dialog box, you can choose in the drop-down:
- Duplicate: In Excel, duplicates are entries found more than once in a selected range. For example, the number “123” in both “Segment 1” and “Segment 2,” or multiple times in a column, is a duplicate.
- Unique: Unique values are those that appear only once within the entire selection. This means the value does not have any match or repeat anywhere else in the columns you’re comparing. For example, if “456” is in “Segment 1” but not in “Segment 2”, and it appears only once in “Segment 1”, then “456” is unique.
This is how to find and highlight duplicates in Excel.
💡 This method looks for any number that appears more than once in either column or across both columns. It will highlight these numbers to show they repeat. It does not check if a number from the first column is also in the second column only. For example, if a number shows up three times in the first column, it will be highlighted, even if it doesn’t appear in the second column at all.
❗ This technique does not work to find duplicates in a Pivot Table. If you need to find duplicates in a pivot table, you can try copying the data from the pivot table into a new worksheet. Then, use the same method described here to highlight duplicates in the new sheet.
How to Find Duplicate Rows in Excel
Sometimes you need to check if entire rows in a table are duplicates of each other. Here’s an easy way to do that without checking each cell one by one:
1. Create a Helper Column
- First, we’ll make a new column that combines all the information from each row into one cell. This makes it easier to compare whole rows.
- We will use the Excel CONCAT function to join the data from each row into one single cell. In the first cell of your new column, type:
=CONCAT(A4:C4)
- Drag this formula down to fill the column.
2. Highlight Duplicate Rows
- Select the column you just filled. Go to the ‘Home’ tab, click ‘Conditional Formatting’, then ‘Highlight Cells Rules’, and select ‘Duplicate Values’. This will color any cell that has data appearing more than once.
3. Hide the Helper Column:
- To keep your sheet tidy, you might want to hide the combined data in the helper column. Click on the column header to select it, press Ctrl + 1 to open the format cells window, and under ‘Custom’, type ;;; (three semicolons). This makes the text in the cells invisible.
- You can also make the column narrower so it takes less space.
Now, any duplicate rows will be highlighted, and your table will look cleaner without visible helper data.
Featured Bundle
Black Belt Excel Bundle
Using Excel Formulas to Check for Duplicate Values
Instead of just coloring cells with duplicates, you might want to display “True” if a value is duplicated, or “False” if it’s not, or even show how many times each entry appears.
Finding How Many Times a Value Appears
Let’s start with using a formula to find duplicates within a single column.
To determine the number of times an entry occurs in a list, use Excel’s COUNTIF Function.
Enter this formula in a new column next to your data:
= COUNTIF($B$4:$B$18, B4)Here, $B$4:$B$18 is the range where you’re looking for duplicates, and B4 is the cell you’re checking. Adjust the range according to your data.
How to Identify Duplicates with True or False
To find out if a value is a duplicate, modify the formula to show “True” if a value appears more than once, and “False” otherwise.
=COUNTIF($B$4:$B$18, B4) > 1This will return “True” for duplicates and “False” for unique entries.
Using an Excel Formula to Identify Duplicate Rows in a Table
To use a formula to locate duplicate rows of data in a table, combine the previous examples of using a “helper column”, the CONCAT Function, and the COUNTIF Functions.
Begin by creating a “helper column” that concatenates all values from a single row.
=CONCAT(B4:D4)
Next, use the COUNTIF Function to determine if each value exists more than one time in the list of helper results.
=COUNTIF($E$4:$E$18, E4) > 1
NOTE: These two steps could be combined into a single formula, but it’s often easier to work each step as separate formulas to keep track of the logic more easily.
Using Symbols to Represent True and False in Excel
Let’s make this interesting by displaying emojis (i.e., symbols) in place of the “true” and “false” results.
Wrap the COUNTIF Function inside an IF Function like so…
=IF(COUNTIF($E$4:$E$18) > 1,Now, for the [True] argument, we’ll press Win+period (Win+.) to bring up the Windows Emoji Library and select a symbol we wish to reflect a “hit” when we have a duplicate entry.
We’ll use a symbol for “true” and display nothing for “false”. Make sure you place the Emoji in a set of quotation marks.
To put a bit of polish on this, we’ll hide the “helper column” (Column E) and color the symbols red using a red font color for the cells in Column F.
Highlight Entire Row Based on a Condition
Suppose you wish to highlight the entire row of duplicate information based on the results of the previously created IF – COUNTIF formula.
Since we have already identified the duplicate rows using the formulas in Column F, we’ll check to see if a result in Column F is an Emoji symbol and if it is, color the entire row of the table in column B thru D.
- Select the table of original values (B4 thru D18).
- Select Home (tab) – Styles (group) – Conditional Formatting – New Rule.
- For the Rule Type, select “Use a formula to determine which cells to format”, and enter the following formula (making sure to lock the column reference but not the row reference):
- Set the formatting to apply a light red cell fill.
=$F4= "⛔"
The results are as follows:
Featured Course
Master Excel’s Essential Modern Functions
Generate a List of Duplicates from a Table
Instead of flagging or highlighting duplicates in a table, suppose you need to generate a separate list of values that are duplicates.
Using the “helper column” of concatenated data, we can use the FILTER Function to reduce the list of source data using the COUNTIF Function as the filter criteria, only keeping entries that occur more than 1 time.
=FILTER(B4:D18, COUNTIF(E4:E18,E4:E18) > 1, "")
Although this DID return a list of the duplicate entries, it would be nice to see them sorted in a way where each duplicate is next to its corresponding duplicate entry. This can be done by wrapping the entire formula inside a SORT Function.
=SORT(FILTER(B4:D18, COUNTIF(E4:E18,E4:E18) > 1, "") )
And, if you don’t need to see the duplicates represented for each instance, you can reduce the list by incorporating a UNIQUE Function.
=SORT(UNIQUE(FILTER(B4:D18, COUNTIF(E4:E18,E4:E18) > 1, "") ) )
How to Remove Duplicates in Excel
To remove duplicates in Excel, there are many easy ways to do this. For step-by-step instructions on each method, check our complete guide here.
Download the Workbook
Enhance your learning experience by downloading our practice workbook. Practice the techniques discussed in real-time and master how to find and highlight duplicates in Excel with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.
Featured Bundle
Power Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
