Find and Replace Duplicates in Excel: Safe Methods

To find duplicates in Excel, select the cells and choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. To remove duplicate records, choose Data > Remove Duplicates; to change repeated text, review the matches and use Find & Replace separately. These tools do different jobs, so make a copy of important data before deleting anything.

What Excel Means by a Duplicate

A duplicate can mean a repeated value in one column, such as the same email address appearing twice, or a repeated record in which several fields match. Those are not always the same thing. Two orders might share a customer name but have different order numbers, so deleting rows based only on the name could destroy valid records.

Decide which columns define a duplicate before choosing a method. If every field in a row must match, select all relevant columns when removing duplicates. If a record is considered repeated when its email address matches, use the email column as the comparison field, while understanding that Excel will keep one row and remove the others even if their other fields differ.

Also distinguish finding duplicates from replacing text. Excel’s Find & Replace searches for text and substitutes different text; it does not identify repeated records automatically. For data cleanup, the safer sequence is usually to identify matches, inspect them, and then remove or edit only the entries you intend to change.

Highlight Duplicate Values Without Deleting Them

Conditional formatting is the quickest way to spot repeated values while keeping the data intact. In Excel for Microsoft 365 on Windows, select the range, then choose Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. In the dialog, leave Duplicate selected, choose a format, and click OK. Excel marks values that occur more than once in the selected range.

For example, selecting A2:A100 checks values against the other cells in that range. A value appearing twice is highlighted in both locations, which makes it easier to compare the entries before deciding what to do. The rule updates as cell values change, although it applies only to the range covered by the rule. If new rows are added outside that range, extend the range or use an Excel table so the formatting can expand with the data.

To remove or adjust the highlighting later, select the affected cells and choose Home > Conditional Formatting > Manage Rules. Check the Applies to range so the rule is neither missing new records nor formatting unrelated cells. Conditional formatting is visual only: it does not change values, merge records, or delete anything.

Review Matches Before Taking Action

Highlighting is useful when a match needs human judgment. Sort or filter the relevant column to bring repeated values together, then compare neighboring fields such as dates, account numbers, or transaction IDs. A repeated name may be expected; a repeated invoice number may signal an accidental duplicate. Do not assume every repeated value is an error.

For a temporary view, use Data > Filter and the column’s drop-down to narrow the list. Filtering does not remove hidden rows, but take care when selecting data after a filter is applied: confirm the operation will affect the intended records. If you need an audit trail, add a status or review column instead of immediately deleting rows.

Remove Duplicate Rows with Excel’s Built-In Tool

Before using Remove Duplicates, save a copy of the workbook or duplicate the worksheet. Then click inside the data and choose Data > Remove Duplicates. If the data has column headings, select My data has headers. In the list of columns, tick the fields that define a duplicate and clear the rest. Click OK to let Excel remove matching rows and report how many duplicates it removed and how many unique values remain.

For example, if a contact list contains Name, Email, and City, selecting only Email treats records with the same email as duplicates even if the names or cities differ. Selecting all three columns removes a row only when all three values match. This distinction matters: the tool compares the columns you select, not an informal idea of which records look similar.

Excel retains one occurrence and removes the other matching rows. Which occurrence remains can depend on the current row order, so sort first if you need to keep the newest, oldest, or otherwise preferred record. Remove Duplicates changes the selected data; it is not a reversible view or a formula. Use Ctrl+Z immediately if the result is wrong, but do not rely on Undo after closing the file or performing many later actions.

If Excel asks whether to expand the selection, include the full table when each row’s fields belong together. Choosing to continue with only the selected column can remove values from that column while leaving neighboring cells in place, misaligning records. When working with an Excel table, the tool can use the table’s columns; check the selected fields carefully before confirming.

Use a Formula to Flag Repeated Entries

A helper formula lets you label duplicates without deleting them. With values in A2:A6 and the first result in B2, enter =COUNTIF($A$2:A2,A2) and fill down. If A2:A6 contains North, South, North, East, South, the results are 1, 1, 2, 1, 2. The first occurrence receives 1; later occurrences receive 2 or higher. Filter column B for values greater than 1 to review only later repeats.

The expanding range in the formula is intentional: as it is copied downward, it counts from the first data row through the current row. To flag every occurrence, including the first, use a whole-range count. With values in A2:A100 and the formula entered beside A2, use =COUNTIF($A$2:$A$100,A2)>1. It returns TRUE when the value appears more than once in the full range and FALSE otherwise. Fill it down to check each row.

For duplicates defined by a combination of two columns, use a count of both fields. With names in A2:A100 and departments in B2:B100, enter this in C2 and fill down: =COUNTIFS($A$2:A2,A2,$B$2:B2,B2). A result above 1 marks a repeated name-and-department pair; the same name in a different department is not counted as the same pair. These formulas identify matches but do not delete rows.

COUNTIF and COUNTIFS are generally case-insensitive, so entries such as ABC and abc may be treated as matching. Wildcard characters such as an asterisk can also affect criteria matching. If capitalization or literal wildcard characters are meaningful in your data, test the formula on a small sample and choose a more exact comparison approach before removing records.

Clean Inconsistent Text Before Comparing

Some apparent non-matches are caused by extra spaces or inconsistent entry. For example, Excel may treat a name with a trailing space as different from the same name without one. A helper column can remove ordinary leading and trailing spaces. With the original text in A2, enter =TRIM(A2) in B2 and fill down; a cell containing the name with extra ordinary spaces returns the cleaned name.

Review the cleaned results before replacing the source data. TRIM also reduces repeated internal ordinary spaces to one, which may or may not be desirable. It does not remove every unusual whitespace character, such as some nonbreaking spaces copied from websites. If matches still fail after trimming, compare the underlying text and consider where the data came from. Do not overwrite original values until you are satisfied the cleanup is appropriate.

When to Use Find & Replace

Find & Replace is for changing text, not deduplicating rows. On Windows, press Ctrl+H or choose Home > Find & Select > Replace. Enter the text to find and the replacement, then use Find Next to inspect a match or Replace to change one occurrence. Replace All applies the change throughout the selected scope, so use it only after confirming the search term and scope.

For instance, replacing an old department label with a new one can standardize a column, but replacing a common word might also alter longer labels or notes. Check the options in the dialog, including whether to match entire cell contents and whether the search is case-sensitive. Confirm whether Excel is searching the current selection, the active worksheet, or a broader workbook scope before using Replace All. On Mac, use the Find and Replace command from Excel’s menus; keyboard shortcuts can differ by keyboard settings.

Find & Replace can make duplicate checking more reliable when inconsistent spellings are the cause, but it does not decide which row to keep. Standardize values first, then run conditional formatting, a formula, or Remove Duplicates based on the correct identifying fields.

Power Query for Repeatable Cleanup

If you receive a refreshed file regularly, Power Query can make deduplication part of a repeatable import process. Convert the source range to a table, then choose Data > From Table/Range to open the Power Query Editor. Select the columns that define a duplicate and choose Home > Remove Rows > Remove Duplicates. Use Close & Load to return the result to Excel.

Power Query creates a query result rather than directly editing the original source table. When the source is updated, refresh the query to produce a new result. As with the worksheet tool, the selected columns determine what counts as a duplicate. Check the result and keep the source data available, especially when the import is used for reporting or recordkeeping.

Which Method Should You Choose?

Highlight duplicates: Best when you want a quick visual check and may need to inspect each match.
Remove Duplicates: Best for a one-time cleanup after you have chosen the comparison columns and saved a copy.
COUNTIF or COUNTIFS: Best when you want an explainable flag, need to filter repeated entries, or want to preserve the original rows.
Find & Replace: Best for correcting repeated text or standardizing labels, not for deleting duplicate records.
Power Query: Best when the same import-and-cleanup steps need to be repeated after new source data arrives.

For a short list, highlighting and reviewing may be safest. For a large list that must be cleaned consistently, a formula or Power Query offers more visibility than deleting rows immediately. Whatever method you choose, define the duplicate key first and keep an untouched copy until the result has been checked.

Frequently Asked Questions

Does Remove Duplicates keep the first or last row?

It keeps one occurrence and removes the other matches; it does not provide a direct keep-first-versus-keep-last setting. Sort the data into the order you want before running the command, then verify the remaining record.

Can Excel highlight duplicates across two columns?

The built-in Duplicate Values rule checks the selected cells as values; it does not define a multi-column record key. For pairs of fields, use a COUNTIFS helper formula or select the relevant columns in Remove Duplicates.

Does Remove Duplicates delete the entire row?

It removes duplicate records from the selected range or table. Include all related columns so the remaining values stay aligned as complete records, and check the range-selection prompt before proceeding.

Can I undo duplicate removal?

Use Ctrl+Z on Windows immediately after the operation, or the Undo command on Mac. Because later actions or closing the workbook can limit recovery, save a copy before removing data.

Why are two entries that look the same not treated as duplicates?

They may contain leading or trailing spaces, hidden characters, different punctuation, or other text differences. Compare the cell contents, clean a copy of the values, and rerun the check rather than assuming the displayed text tells the whole story.

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.