How to Remove Duplicates in Excel (5 Steps With Pictures)

Download Now: 50 Excel Hacks [Free Guide]
Rebecca Riserbato
Rebecca Riserbato

Updated:

Published:

Duplicate data is costly, it skews reports and leads to wasted marketing resources. Here’s how to remove duplicates in Excel spreadsheets:

Marketer analyzing duplicate data on Excel

Download Now: 50+ Excel Hacks [Free Guide]Let’s run through it with an example!

Note: If you want to see duplicate data before removing them, scroll down to learn how to find and highlight duplicates in Excel. Also, make a copy of your worksheet so you still have your original data if you delete something important.

50 Excel Hacks To Help You Master Excel

Tips to help you tackle Excel, including a guided template, GIF demonstrations, and a graph generator.

  • 50 Excel Hacks
  • A Guided Template
  • GIF Visuals
  • Excel Graph Generators
Learn more

    Download Free

    All fields are required.

    You're all set!

    Click this link to access this resource at any time.

    How to Remove Duplicates in Excel

    1. Select the cells you wish to remove duplicates from.

    Click on a cell and hold down the left mouse button. Then drag the cursor over the other cells you want to select.

    How to Remove Duplicates in Excel: to remove duplicates in excel, select all the cells you want

    Or, use the Shift + arrow keyboard shortcut to select the range you want.

    2. Click on the “Data” tab at the top.

    click on dataHow to Remove Duplicates in Excel: t, step 2 to remove duplicates in excel

    3. Click “Remove Duplicates” to reveal a pop-up.

    How to Remove Duplicates in Excel: tstep 3 to remove duplicates in excel, click on "remove duplicates"You should see a pop-up that looks like this:

    How to Remove Duplicates in Excel: ta popup to remove duplicates in excel

    4. Uncheck any columns with data you want to keep. 

    How to Remove Duplicates in Excel: tuncheck colums with data you don't want to delete in excel

    5. Click OK to delete the duplicates.

    How to Remove Duplicates in Excel: tclick ok to delete duplicates

    Excel will keep the first occurrence of the value by default.

    How to Highlight Duplicates in Excel

    Sometimes, you want to see the duplicate data before you delete it. Here’s how to find duplicate data in Excel:

    1. Select the cells you wish to check for duplicates.

    Click on a cell and hold down the left mouse button. Then drag the cursor over the other cells you want to select.

    How to Highlight Duplicates in Excelto highlight duplicates in excel, select all the cells you want

    Or, use the Shift + arrow keyboard shortcut to select the range you want.

    2. Click “Conditional Formatting” from the home tab to reveal a drop-down.

    First, navigate to the home tab if you’re on a different tab.

    How to Highlight Duplicates in Excelclick conditional formatting to find duplicates in excel

    Click on “Conditional Formatting” to reveal a dropdown.

    click on Conditional Formatting to find duplicates in excel

    3. Hover over “Highlight Cell Rules”

    How to Highlight Duplicates in Excelhover over highligh cell rules to find duplicates in excel

    4. Choose “Duplicate Values” from the options.

    choose duplicate values to highlight duplicate values

    5. Pick the formatting option you want and click OK.

    How to Highlight Duplicates in Excelchoose the formatting style you want to highlight your duplicate excel data with

    You’ll find all duplicates highlighted in the formatting style you choose.

    Now you know how to find and remove duplicates in Excel! 

    Want more Excel automation hacks? Excel templates are a good place to start. They come pre-formatted with formulas that help you automate projections and more.

    Editor's note: This post was originally published in [Month Year] and has been updated for comprehensiveness.

    50 Excel Hacks To Help You Master Excel

    Tips to help you tackle Excel, including a guided template, GIF demonstrations, and a graph generator.

    • 50 Excel Hacks
    • A Guided Template
    • GIF Visuals
    • Excel Graph Generators
    Learn more

      Download Free

      All fields are required.

      You're all set!

      Click this link to access this resource at any time.

       

       

      Topics: Excel

      Related Articles

      50 hacks to help you tackle Excel, including a guided template, GIF demonstrations, and a graph generator

        Marketing software that helps you drive revenue, save time and resources, and measure and optimize your investments — all on one easy-to-use platform

        START FREE OR GET A DEMO