Country or region and language

    Choose your country or region and your language.

    Suggested for you

      All countries and regions

        Choose your character

        It shows in the top bar and on your profile. Change it any time.

        How to remove duplicates in Excel

        Learn how to remove duplicates in Excel, keep the first matching row, choose the right columns, and check conflicting records before deleting useful data.

        A person practicing on a laptop in a quiet workspace

        Here’s how to remove duplicates in Excel: select your full data range, choose Data > Remove Duplicates, confirm whether the range has headers, select the columns that define a duplicate, and click OK. Excel keeps the first occurrence in the current row order and removes later matches from the selected range. Copy your worksheet first because Remove Duplicates changes the source data.

        Duplicate removal with one copy kept

        Remove Duplicates keeps one copy of each matching record, using the columns you check as the matching rule. The menu paths and formulas in this tutorial use Microsoft 365 Excel for Windows, with English function names and comma argument separators.

        The important distinction is between the selected range and the checked columns. Your selected range should include every field belonging to each record. The checked columns tell Excel which values to compare, but Excel removes the matching record across the selected range, not just from the checked columns.

        For example, an order number can appear on several valid order lines. Comparing only order numbers would treat those separate lines as duplicates. Before you delete anything, decide what makes one record different from another.

        An order export and a duplicate check

        Check the order number and line number together to find repeated order lines before deleting any records. In the fictional export below, SO101 has two legitimate lines, but its desk line also appears twice.

        Paste the following tab-separated data into cell A1 of a blank worksheet. The amounts are fictional order-line amounts; their currency doesn't affect duplicate matching.

        Order ID	Line	Item	Amount
        SO101	1	Desk	2400
        SO101	2	Lamp	800
        SO101	1	Desk	2400
        SO102	1	Desk	2400
        SO103	1	Stool	1600

        Before changing the data, right-click the worksheet tab and select Move or Copy. Select Create a copy, then click OK. Keep one worksheet as your review copy and use the other for removal.

        On the review copy, type Match count in E1. Enter the following formula in E2, then copy E2 into E3:E6.

        =COUNTIFS($A$2:$A$6,A2,$B$2:$B$6,B2)

        Practice what this post teaches

        COUNTIFS counts rows that meet several conditions together. Here, a row must match both the current Order ID and the current Line. The dollar signs create absolute cell references, which keep the comparison ranges fixed when you copy the formula. The references A2 and B2 change for each row.

        Cell	Match count
        E2	2
        E3	1
        E4	2
        E5	1
        E6	1

        The count includes the current row. A result of 1 means the order-line pair appears once; a result of 2 means the pair appears twice. Both occurrences receive 2, so the formula doesn't tell you which occurrence to remove.

        Removal steps and the checked result

        Compare all four source columns to remove the exact repeated record while keeping the legitimate second line of SO101. Use the working copy, leaving the review copy unchanged.

        Follow these steps in Microsoft 365 Excel for Windows:

        • Select A1:D6, including the headers and all five source records. Don't include the Match count helper column.
        • On the Data tab, select Remove Duplicates.
        • Select My data has headers so Excel treats the first row as column names rather than a record.
        • Leave Order ID, Line, Item, and Amount checked.
        • Click OK and check the completion message.

        Excel should report one duplicate removed and four unique records left. The source data should now look like the following result.

        Order ID	Line	Item	Amount
        SO101	1	Desk	2400
        SO101	2	Lamp	800
        SO102	1	Desk	2400
        SO103	1	Stool	1600

        The earlier SO101 desk record stays, and the later identical record disappears. The lamp line stays because its line number and item differ. SO102 also stays, even though its item and amount match the desk line, because its Order ID differs.

        Try choosing the comparison columns yourself with Remove duplicates, then check the surviving records rather than relying only on the completion message.

        A conflicting amount and a narrower key

        Selecting only Order ID and Line treats each order-line pair as one record even when the amounts differ. A key is a column or combination of columns used to identify a record; Order ID plus Line is a composite key because it uses two columns.

        Make a fresh working copy of the original five-record review worksheet. Change D4 from 2400 to 2600. Keep D2 at 2400. Use the original data for this change: in the already-cleaned four-record result, row 4 belongs to a different order.

        The two records with the repeated key now look like the following. The row numbers identify their locations on the fresh copy.

        Worksheet row	Order ID	Line	Item	Amount
        2	SO101	1	Desk	2400
        4	SO101	1	Desk	2600

        The COUNTIFS results stay unchanged because the formula compares only Order ID and Line. Inspecting the flagged rows now reveals a conflict rather than an exact duplicate.

        Comparison choices for the conflicting-amount example
        Checked columnsMatching ruleResult or risk
        Order ID, Line, Item, AmountEvery source field must match.No records are removed because the two desk amounts differ.
        Order ID and LineThe order-line pair must match.The later 2600 record is removed; the earlier 2400 record stays.
        Order ID onlyThe order number must match.Both the valid lamp line and the later desk record are removed.

        To test the narrower key, select A1:D6, open Remove Duplicates, and leave only Order ID and Line checked. Excel removes the later desk record. The four surviving records are exactly the same as the main example's output, even though the deleted record held a different amount.

        If 2600 was a correction, the narrower matching rule silently discards the corrected amount. Remove Duplicates can't decide which conflicting amount is correct. Check the source and establish which version to keep before deleting either record; the first row isn't automatically the latest or most accurate.

        Selection mistakes and recovery checks

        Check the selected range, comparison columns, and surviving order lines whenever duplicate removal produces an unexpected result. A plausible-looking output can still be missing valid information.

        Use these checks before accepting the result:

        • Check the full record range. Selecting only the repeated-value column can separate order numbers from their related items and amounts. Include all source fields in the selected range.
        • Check the matching rule. Selecting only Order ID removes SO101 line 2 even though the lamp is a valid separate order line.
        • Check the header setting. My data has headers should be selected for this example because row 1 contains field names.
        • Keep the helper on the review copy. Formulas beside a changed source range recalculate and aren't a reliable record of the original duplicate check.
        • Compare the result with the untouched review copy. In the main example, both SO101 line 1 and SO101 line 2 must survive.

        If the main example doesn't leave four records, use Undo immediately and review the selection and checked columns before trying again. Keep the original review copy even after the result looks correct, so you can explain which records were removed and why.

        The practical rule is simple: inspect repeated keys first, resolve conflicting values, and remove only records that meet your chosen duplicate definition. Duplicate removal is a matching operation, not a decision about which business record is true.

        Common questions

        How do I remove duplicates in Excel but keep the first occurrence? Select the full source range, open Data > Remove Duplicates, and check the columns that define a match. Excel keeps the first matching record in the current row order, so changing the order before removal can change which record survives.

        How do I check for duplicates across two columns? Use COUNTIFS to count records that match both columns, as shown in the order-line example. A count above 1 identifies a repeated combination, but you still need to compare the other fields for conflicting values.

        How can I identify duplicates without deleting them? Add the Match count helper column and leave the source records unchanged. Counts above 1 flag repeated keys for review without removing either occurrence.

        Why does Excel keep a row with the wrong amount? Remove Duplicates keeps the first matching record, not the record with the correct amount. If Amount isn't checked, Excel doesn't use the amount to decide whether two records match.

        Why can repeated order numbers represent different records? One order can contain several items, each with its own line number. Compare Order ID and Line together to distinguish those records, then inspect the other fields before deleting repeated order-line pairs.

        Practice what this post teaches

        Your Work Gym account

        Save your progress and pick up on another device. Your browser's progress comes with you.

        or

        You can keep practicing without an account.

        Help and feedback

        Ask a question, tell us what is not working, or share an idea. We reply by email.

        What is it about?

        We use it only to reply to you.