Decide which records count as duplicates
You cannot simply delete a row because a name appears twice in a list. Two people may share a name, or the same person may have applied on different dates. Decide what the task requires: comparing names alone, names with contact details, or order numbers.
Excel’s Remove Duplicates compares the columns you select. When you select several columns, it checks their combined values; a match removes the entire row within the selected range. Data in columns you did not choose for comparison can disappear with that row. [2]
For example, comparing applicant name and application date identifies records where both fields match. Comparing only the name could remove rows with different dates. This example illustrates the comparison rule; in an actual file, first examine fields such as application numbers that identify individual records.
This method is available in Windows desktop Excel for Microsoft 365 and Excel 2024, 2021, 2019, and 2016. Mac and web versions may have different screens or features, so check the menus for your version. [1]
Highlight duplicate values with Conditional Formatting first
Before deleting values, locate potential duplicates. Select the cell range and go to Home → Conditional Formatting → Highlight Cells Rules → Duplicate Values. Choose the format and confirm to inspect the duplicates. This duplicate highlighting feature cannot be applied to the Values area of a PivotTable report. [1]
This step adds a visual mark to cell values for review. A colored cell does not mean its row has been deleted or that all highlighted values are unnecessary for your work. If you highlighted the name column, read each matching row’s date and contact details too.
Record which column and range you highlighted. Pick several candidate rows and read the application number and date as well as the name. If distinct records are highlighted, note why, decide whether they really should be removed, then move on.
Copy the original and check the data range
Remove Duplicates actually deletes data. Before running it, copy the original range to another worksheet or workbook. Give the copy a clear name such as “Before cleanup” and make your changes in a separate working copy so you can compare records again. [1]
Check whether the data is organized consistently into rows and columns. Review the range so hidden rows or columns and blank rows in the middle do not cause you to miss needed information. Microsoft’s data-cleaning guidance also starts with an original backup, consistent data types in each column, and a table with visible rows and columns. [3]
If a name and contact details form one record, include the related columns together in the range. Remove Duplicates affects only the selected range; values outside it do not move. Check that no related data has been left out. [2]
Select the comparison columns in Remove Duplicates
Select the entire working range, then open Data → Remove Duplicates. In the column list, select the fields to use for comparison and clear those you do not need. To compare a name and contact details together, choose both columns and confirm. Read the range and comparison criteria one last time. [1][2]
When a value occurs more than once, Remove Duplicates keeps its first occurrence in the list and removes later matching records. It does not automatically keep the most recent data. If the retained row matters, review the order and contents first. To identify the latest record, also establish what its date means. [2]
After confirmation, Excel displays the counts of duplicate values removed and unique values remaining. Blank cells and spaces can affect these counts, so the numbers alone do not prove the result is right. If more records disappear than expected, undo before making further edits and recheck the comparison columns and range. [1][2]
Review unmatched values and the rows that remain
If values look identical but behave differently, inspect leading or trailing spaces, formatting, and imported characters. External data can contain invisible characters. Microsoft explains that these can affect sorting, filtering, and searching, and describes cleaning methods using TRIM, CLEAN, and SUBSTITUTE. [3]
Before removing spaces, decide whether each space is actually an error. Company names and addresses can contain necessary spaces, so clean values in a separate column and compare them rather than overwriting the original. Leading zeros in codes that look like numbers may also matter. Check several values before and after any change. [3]
Finally, compare row counts and representative records before and after cleanup, and check that the dates, amounts, and contact details you intended to keep are correct. Record the comparison columns and the rule for choosing retained rows so you can reuse them when receiving the same file again. Separating highlighting, deletion, and final review helps preserve the original while you clean the data.