Duplicates creep into spreadsheets when lists are pasted together, customer exports overlap or the same information is entered twice. Excel has a Remove Duplicates command that can tidy a table quickly. The danger is choosing the wrong columns: two people with the same surname aren't necessarily duplicates, and two sales records with the same product might represent separate orders.
Before deleting anything, decide what makes a record genuinely unique. Then back up the data, select the correct range and preview potential duplicates. Excel's built-in tools are useful, but they cannot decide your business rules for you.
Back up before removing anything
Save another copy of the workbook or duplicate the worksheet before beginning.
Microsoft warns that Remove Duplicates permanently deletes duplicate values from the selected data range. Undo may help immediately, but it is not a sound replacement for a saved copy.
A backup matters particularly when the workbook was supplied by a colleague or contains records used for accounting.
Decide what counts as a duplicate
In a customer table, an email address or unique customer ID might identify one person. In an order table, each order number might identify one transaction.
Two customers can share a postcode or surname without being the same customer.
Write down the rule before clicking Remove Duplicates. Otherwise, the result may look tidy while important records are gone.
Check your column headings
A clean table should have a header row showing the meaning of each column.
Look for merged cells, blank column headings and separate notes mixed into the middle of the data.
If the table is irregular, fix its structure first or work on a clean copy. Excel needs a clear rectangular range.
Highlight possible duplicates first
Select the cells you want to inspect, then use Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.
Choose a formatting style so repeated values become visible.
This is a useful review stage. Highlighting duplicate surnames or amounts shows repetition, not necessarily erroneous records.
Use the Data tab
Select the whole table or an appropriate cell within a well-defined data range.
Choose Data > Remove Duplicates. Excel opens a dialog listing the columns it can use to compare rows.
Check that the correct range is selected before proceeding. Removing duplicates from a single column can break the relationship between fields if used carelessly.
Indicate whether the table has headers
If the first row contains column names, select My data has headers when the dialog provides that option.
Then the dialog will show meaningful names rather than Column A or Column B, making the criteria easier to check.
If the first row is real data, don't identify it as a header or it may be left out of the comparison.
Choose the comparison columns carefully
Excel treats rows as duplicates when the selected comparison columns contain matching values.
For identical full rows, select every relevant column. For customers identified by unique ID, the ID column alone may be appropriate.
Avoid selecting only a common value such as 'Manchester' or 'Paid', because thousands of valid rows can share it.
Understand which row is kept
Remove Duplicates keeps a matching instance and removes subsequent matches according to the command's behaviour.
If you need to retain the most recent update, sort or prepare the data carefully first, then confirm the output.
Never assume Excel can infer whether an old or new customer address is the correct one.
Review the result message
After running the command, Excel reports how many duplicate values were removed and how many unique values remain.
Compare the counts with your expectation. A dramatic fall from 5,000 orders to 200 rows should make you stop and investigate.
Check a sample of known records against the backed-up copy before treating the cleaned file as final.
Remove duplicates using one key
If a reliable unique identifier exists, such as an invoice number, use it to establish duplication.
But verify that the field is genuinely unique. Some exports reuse reference numbers across stores or years.
Where uniqueness depends on a combination of fields, select that combination rather than forcing one column to carry all the meaning.
Compare several columns together
A booking may be unique based on a customer ID, date and venue. Two rows sharing all three could be accidental duplicates.
Select those columns in the Remove Duplicates dialog, excluding fields that change for administrative reasons only when your rules support it.
The comparison is exact according to Excel's matching behaviour, not a judgement about near-matching names.
Hidden spaces can confuse comparisons
A name containing an extra space or a postcode stored in a different format may look the same to a person but behave differently in formulas or exports.
Check and normalise data carefully before cleaning. Functions such as TRIM and CLEAN may help with particular types of unwanted characters.
Keep the original data available so you can verify transformations didn't change meaningful content.
Duplicates in one column aren't whole duplicate rows
Suppose a sheet has the same product code on 50 lines because each line is a separate sale.
Removing duplicates based only on product code would destroy valid transaction records.
You may want a distinct product list instead. Make that as a separate output rather than deleting order history.
Use UNIQUE for a non-destructive list
In suitable modern Excel versions, the UNIQUE function can return distinct values into another range without deleting the source.
This is particularly useful for creating a quick list of customer regions, category names or products.
Check your Excel version's support and consider whether you need unique full rows or a single column.
Power Query for repeatable cleanup
If you receive a similar export every week, manual clicking becomes repetitive and error-prone.
Power Query can transform and remove duplicates according to a defined sequence of steps, allowing the procedure to be rerun on refreshed data.
For business-critical work, document the matching rules and test them against known examples.
What about case differences?
Excel's duplicate handling may treat text comparisons without case sensitivity in many situations.
If uppercase and lowercase codes are meaningfully different in your system, verify this carefully before using the built-in command.
Specialised formulas or Power Query techniques may be needed for case-sensitive matching.
Check formulas and references afterwards
Removing rows changes their positions. Structured references and formulas generally adapt in normal Excel ways, but custom workbook logic can depend on layout.
Review totals, filters and any charts linked to the cleaned range.
A final spot check is worth a few minutes if the workbook informs decisions or financial reports.
Don't clean the only copy of an import
Retain the untouched export with its date, then save the cleaned result separately.
If the import is questioned later, you'll be able to explain precisely which duplicate rule was applied.
This is good spreadsheet practice even when the cleaning process is quick.
The safe workflow
Save a backup, decide the exact duplicate key, select the full table and use Data > Remove Duplicates with the right columns.
Verify the counts and compare examples against the original. The real skill isn't finding the Remove Duplicates button; it is knowing which rows are genuinely safe to discard.