Identify Duplicate Rows in Excel: A Practical Guide

To identify entire duplicate rows in Excel, add a helper column and use COUNTIFS to compare each record across its columns; filter the results for TRUE. To spot repeated values in just one column, select that column and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. Decide first which columns define a duplicate, and save a copy before making changes.

What You’ll Need

A workbook open in Microsoft Excel for Microsoft 365 on Windows. The instructions also work in recent desktop versions of Excel; Mac ribbon labels are generally similar.
A table with one row per record and a header row, such as columns for name, date, product, and amount.
Permission to add a temporary helper column. If you plan to remove duplicates later, make a backup copy of the workbook first.

Step-by-Step

Decide what counts as a duplicate. A duplicate can mean the same value in one field, such as an email address, or a record matching across every relevant column. Those are different checks. If two people can share a name, for example, matching names alone does not prove the rows are duplicates. Write down which fields must match before you start. You can also choose whether to compare every column or only the fields that identify a record, such as customer ID and transaction date.
Prepare the data. Make sure each row represents one record, each column has a clear header, and there are no completely blank rows breaking up the table. If the data is important, save a copy with File > Save As before proceeding. Check that values meant to be comparable use the same format: dates should be real dates rather than a mixture of dates and text, and identifiers should not be stored inconsistently as numbers in some rows and text in others. Do not delete or rearrange records yet.
Highlight duplicates in one column, if that is the check you need. Select the cells to examine, excluding the header. For example, select the email addresses in B2:B500. Choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a format, and click OK. Excel highlights repeated values in that selected range, including the first occurrence and later repeats. This is a quick visual check, but it does not tell you whether all the other fields in the rows also match. To remove the formatting later, select the range and choose Home > Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
Add a helper column to compare complete rows. Insert a blank column beside the records and give it a header such as Duplicate row? In this example, the records occupy A2:D100, with headers in row 1, and E2 is the first helper cell. With those ranges and columns, enter =COUNTIFS($A$2:$A$100,A2,$B$2:$B$100,B2,$C$2:$C$100,C2,$D$2:$D$100,D2)>1 in E2. The formula returns TRUE if the combination of values in A2:D2 appears more than once in rows 2 through 100, and FALSE if it appears only once. For example, if row 8 has the same four values as row 3, E8 returns TRUE. Replace the example column letters and row numbers to match your table, adding one range-and-cell pair for every column that should be compared.
Fill the formula down the helper column. Select E2 and use the fill handle, the small square at the lower-right corner of the selected cell, to copy the formula through the last data row. You can also copy E2, select the rest of the helper range, and paste. Check a few results near the top, middle, and bottom of the data. The dollar signs keep the full comparison range fixed, while the references to the current row change as the formula is filled down. If you add more records later, extend the formula’s ranges to include them.
Filter to review matching rows. Click a cell in the data and choose Data > Filter. Use the filter arrow in the helper-column header and select TRUE. Excel now shows rows whose complete selected combinations occur more than once. Compare the visible rows before deciding what to do: a formula can confirm that the chosen fields match, but it cannot decide whether two matching records are genuinely redundant. Clear the filter when you are finished by choosing Data > Clear, or use the filter menu to select all values again.
Choose whether to keep all matches or mark only later copies. The formula above flags every member of a repeated group, including its first occurrence. If you want to keep the first matching record and flag only the later ones, use a running count instead. With the same records in A2:D100 and the first helper cell in E2, enter =COUNTIFS($A$2:A2,A2,$B$2:B2,B2,$C$2:C2,C2,$D$2:D2,D2)>1. It returns FALSE for the first occurrence and TRUE for each subsequent matching row. For example, if rows 3 and 8 match and row 3 appears first, row 3 is FALSE and row 8 is TRUE. Fill the formula down as before. This is useful for reviewing which rows might be removed, but inspect them before deleting anything.
Remove duplicates only if that is your goal. Work on your backup or a copy of the data. Select the table and choose Data > Remove Duplicates. Confirm whether the table has headers, then select the columns Excel should use when deciding that rows are duplicates. Click OK and read Excel’s summary of how many duplicate values were removed and how many unique values remain. Excel keeps the first occurrence and removes later matching rows based on the columns selected. This action changes the data; identifying duplicates with highlighting or a helper formula does not.

Common Mistakes

Checking only one field when you mean to compare whole records. Duplicate Values formatting on an email column identifies repeated email addresses, not necessarily identical rows. Use a helper formula that includes every column relevant to your definition.

Including the header in the comparison. Keep column headings out of the formula’s data ranges. When using Remove Duplicates, make sure the My data has headers option is set correctly; otherwise, Excel may treat a heading as a record.

Removing rows before reviewing them. Remove Duplicates is not merely a highlighting tool. It deletes matching records from the selected range, generally retaining the first one. Keep a backup and verify the selected columns before confirming.

Assuming values that look alike are identical. Extra spaces, different date values displayed in similar ways, or a number stored as text can affect comparisons. Conversely, COUNTIFS does not distinguish between uppercase and lowercase text, and wildcard characters such as an asterisk in criteria may affect matching. Inspect unusual data and test a few records.

Ignoring empty rows or blank records. If every compared field in two rows is blank, the formula can treat those rows as a repeated combination. Filter or remove genuinely empty records separately, or make sure the formula is applied only to actual records.

Tips

Convert a consistent data range to an Excel table with Insert > Table. Tables make it easier to filter records and keep headers visible; formulas entered in a table column can fill down automatically.
Use a unique identifier, such as an order number, when one exists. If the identifier is repeated legitimately, compare the additional fields that distinguish records rather than relying on names alone.
Keep the helper column until you have finished checking the results. It gives you a repeatable way to filter and review matches, and you can remove the temporary column afterward.
For a very large range, use a filter on the helper results rather than scanning highlighted cells manually. Save the workbook before sorting or deleting, and check that you are working with the complete table rather than a partial selection.
On Windows, Ctrl+Z undoes many recent edits; on Mac, use Command+Z. Do not rely on undo as a substitute for a backup, especially after using commands that alter many rows.

Troubleshooting

The formula returns FALSE for rows that look identical. Check that every intended comparison column is included and that the formula ranges cover the full data set. Look for leading or trailing spaces, hidden characters, inconsistent data types, or dates that are stored as text. If useful, compare the cells directly or inspect their number formats. You may need to clean inconsistent values before repeating the check.

Too many rows return TRUE. Confirm that you selected only the fields that should define a duplicate. If you compare only a common value, such as a city or product name, unrelated records with that same value will match. Check whether blank records are included, and look at the exact values in all compared columns.

The helper column shows an error or the formula does not fill correctly. Check that each COUNTIFS range has the same start and end rows, that every opening parenthesis has a matching closing parenthesis, and that the cell references point to the intended columns. In the example, enter the formula in E2 and fill it down; placing it in a different starting row without adjusting the references can produce incorrect results. Excel installations using different regional settings may require semicolons instead of commas as formula separators.

Conditional formatting highlights unexpected cells. Verify that you selected only the data cells in the column, not unrelated cells or the header. Check the conditional formatting rule for the selected range and clear old rules if several formats overlap. Remember that this option compares values only within the selected range; it is not a complete-row comparison.

Remove Duplicates removes records you wanted to keep. Stop and restore the backup or use Undo immediately if the command is still the most recent action. Then repeat the process on a copy, selecting only the columns that define a duplicate. If you need to preserve every record for audit or review, use the helper-column method and do not run Remove Duplicates.

Frequently Asked Questions

Does Excel have a built-in way to highlight duplicate values?
Yes. Select the relevant cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. That checks repeated values in the selection, not whether entire rows match.

How do I identify rows that match across all columns?
Add a helper column with a COUNTIFS formula that compares the current row’s values against the corresponding columns in the data range. Filter the helper column for TRUE to review matching rows.

Does the COUNTIFS formula mark the first occurrence?
The fixed-range formula in this guide marks every row in a repeated group, including the first. The running-count version returns FALSE for the first occurrence and TRUE for later copies.

Can Excel identify duplicates without deleting them?
Yes. Conditional formatting and helper formulas identify or flag matches without removing records. Avoid Data > Remove Duplicates unless you intend to change the data.

Why are repeated names not always duplicates?
A name may belong to more than one person or appear on separate legitimate records. Compare a more reliable identifier or a combination of fields, and review matching rows before taking action.

Does Excel treat uppercase and lowercase text as different?
COUNTIFS comparisons are not case-sensitive, so values such as Taylor and TAYLOR normally match. If capitalization is meaningful for your task, use a case-sensitive checking method rather than relying on this formula alone.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.