Excel: Find & Remove Duplicates

    Excel: Find & Remove Duplicates

    Sometimes it is useful to duplicate data, sometimes it just makes it difficult to interpret your data. Using conditional formatting to identify duplicate data and highlight it. You can search the duplicates that way and determine whether you want to delete them.

    1. Choose the cells that you would like to search for duplicates.

    Note: In the Values region of the PivotTable report, Excel does not highlight duplicates.

    2. Click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

    Highlighting Cells

    3. Select the formatting that you want to add to the duplicate values in the box next to the values, and then select OK.

    Duplicate Values

    To Remove Duplicate Values

    The duplicate data will be permanently removed when you use the Remove Duplicates option. Copying the original data to another worksheet is a good idea before you remove the duplicates, so you do not accidentally lose any information.

    1. Choose the set of cells you want to delete that have duplicate values.
    2. Click Data > Remove Duplicates, and then Under Columns, check or uncheck the columns where you want the duplicates to be deleted.
    Remove Duplicates

    For instance, the January column in this worksheet has price information that I want to keep.

    Excel Worksheet

    So, I unchecked January in the Remove Duplicates box.

    Remove Duplicates

    3. Click OK.

    Excel: Find & Remove Duplicates

    Sakshi Sharma
    I am a Managing Partner at Bioinformatics India where I write blogs, look after all the partners, and manages the affiliates associated with the website.

    Get in Touch

    Related Articles

    Get in Touch


    Latest Posts