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”.

Fragment of Excel worksheet with header HIGHLIGHT DUPLICATES and data in 2 columns: Segment 1 in column A and Segment 2 in column C. Some values are appear in both columns.
  • 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.
Fragment of Excel worksheet with header HIGHLIGHT DUPLICATES and data in 2 columns selected: Segment 1 in column A and Segment 2 in column C.
  • 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’.
Fragment of the Excel ribbon with Home tab selected and Conditional Formatting button highlighted and expanded. In the expanded Conditional Formatting menu Highlight Cells Rules is highlighted and expanded, with an arrow pointing to highlighted 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.

Fragment of Excel worksheet with header HIGHLIGHT DUPLICATES and data in 2 columns: Segment 1 in column A and Segment 2 in column C. Values that appear in both columns are highlighted with light red fill with dark red text.

💡 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:

Fragment of Excel worksheet with header FIND DUPLICATE ROWS and data in three columns: Product, Customer, Quantity. Two rows with identical values are highlighted.

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)
Fragment of an Excel worksheet with data from columns A to C is concatenated in column D using the CONCAT function visible in the formula bar.
  • Drag this formula down to fill the column.
Fragment of Excel worksheet with data in three columns: Product, Customer, Quantity, and a fourth column, with concatenated values from the previous three columns, selected.

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.
Fragment of Excel worksheet with data in three columns: Product, Customer, Quantity, and a fourth column, with concatenated values from the previous three columns. Repeated values in the fourth column (column D) are highlighted with light red fill with dark red text.

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.
Fragment of the Format Cells dialog box with the Number tab selected and highlighted. Custom Number formatting defined as three semicolons.
  • You can also make the column narrower so it takes less space.
Fragment of Excel worksheet with data in three columns (A to C): Product, Customer, Quantity. Where the same values in rows are repeated, the next cell in column D is highlighted with light red fill.

Now, any duplicate rows will be highlighted, and your table will look cleaner without visible helper data.

Featured Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

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.

Fragment of Excel worksheet with data in three columns (B to D): Product, Customer, Quantity. In column A, a COUNTIF formula is referencing column B (Product).

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) > 1

This will return “True” for duplicates and “False” for unique entries.

Fragment of Excel worksheet with data in three columns (B to D): Product, Customer, Quantity. In column A, a COUNTIF formula is referencing column B (Product) and returning TRUE/FALSE values.

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)
Fragment of an Excel worksheet with data from columns B to D is concatenated in column E using the CONCAT function visible in the formula bar.

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
Fragment of an Excel worksheet with data from columns B to D concatenated in column E. In column F, a COUNTIF formula checks the values in column E against each other, returning TRUE if duplicates found, and FALSE if not.

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.

Fragment of an Excel worksheet with data from columns B to D concatenated in column E. In column F, a COUNTIF formula checking the values in column E against each other, returning TRUE if duplicates found, and FALSE if not, is being modified to return a "no entry" emoji if TRUE. Windows emoji keyboard and shortcut key combination: Windows key plus full stop key.

We’ll use a symbol for “true” and display nothing for “false”.  Make sure you place the Emoji in a set of quotation marks.

Fragment of an Excel worksheet with data from columns B to D concatenated in column E. In column F, a COUNTIF formula checks the values in column E against each other, returning "no entry" emoji if duplicates found.

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.

Fragment of an Excel worksheet with data in columns B, C, D: Product, Customer, Quantity. Column E is hidden, in column F a red "no entry" emoji indicates duplicated rows.

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.

  1. Select the table of original values (B4 thru D18).
  2. Select Home (tab) – Styles (group) – Conditional Formatting – New Rule.
  3. 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):
  4. Set the formatting to apply a light red cell fill.
=$F4= "⛔"
Edit Formatting Rule dialog box with Rule Type "Use a formula to determine which cells to format" selected.

The results are as follows:

Fragment of an Excel worksheet with data in columns B, C, D: Product, Customer, Quantity. Column E is hidden, in column F a red "no entry" emoji indicates duplicated rows and the entire duplicated row is highlighted with light red fill.

Featured Course

Master NEW Excel Functions in Office 365 & Office 2021

Ready to supercharge your Excel skills? This course will help you master the new set of functions introduced to Excel for Office 365. You’ll create professional-grade reports in a fraction of the time it used to take you.
Learn More
Excel new functions course cover

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, "")
Fragment of an Excel worksheet with data from columns B to D (Product, Customer, Quantity) concatenated in column E. 
Cells G4 to I7 contain a range returning duplicate rows using the FILTER function in conjunction with COUNTIF, which checks the values in column E against each other.

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, "") )
Fragment of an Excel worksheet with data from columns B to D (Product, Customer, Quantity) concatenated in column E. 
Cells G4:I7 contain a sorted range returning duplicate rows using the SORT and FILTER functions in conjunction with COUNTIF, which checks the values in column E against each other.

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, "") ) )
Fragment of an Excel worksheet with data from columns B to D (Product, Customer, Quantity) concatenated in column E. 
Cells G4:I5 contain a sorted range returning duplicate rows using the SORT, UNIQUE and FILTER functions in conjunction with COUNTIF, which checks the values in column E against each other.

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.

Excel Download Practice file

Featured Course

Power Excel Bundle

10x your productivity in Excel 💪. This bundle includes Master Excel Power Query course & Master Power Pivot and DAX course. It’s Excel’s Ultimate Power Tools in ONE convenient (cost savings) bundle. Learnings apply to Power BI as well.
Learn More
Power Excel Bundle course cover

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.