Skip to main content

How to remove duplicates in Excel

You can improve your spreadsheet's readability by removing unnecessary duplicate values. If you know you don't need them, why not use Microsoft Excel's tools to find and remove them? If you want to get rid of them, we can show you how. Here's how to remove duplicates in Excel.

Related Videos




10 minutes

What You Need

  • A PC

  • Microsoft Excel

Before removing those duplicates: Here's how to find them

Before deleting those duplicate values from your Excel spreadsheet outright, you'll need to find them first. Here's how to do that:

Step 1: Highlight all the cells you want Excel to scan for duplicates. Then select the Home tab from the Ribbon Menu at the top of the screen.

how to remove duplicates in excel find screenshot 1

Step 2: On the Home tab, click on Conditional Formatting. From the menu that appears select Highlight Cells Rules.

how to remove duplicates in excel find screenshot 2

Step 3: Then choose Duplicate Values. In the dialog box that appears, click on the drop-down menu after the phrase Values With and choose your desired color formatting for the cells that are deemed to be duplicates.

how to remove duplicates in excel find screenshot 3

Step 4: Click OK. All of the duplicates from the selected cells should have been detected and marked with your chosen color formatting. You should now be able to easily see which values in your spreadsheet are duplicates.

How to remove duplicates in Excel

Now that you know which values in your spreadsheets are duplicates, you can remove them. Here's how:

Note: Removing duplicates in Excel usually involves permanently deleting them, so you may want to make another copy of the original version of this data before proceeding with removing the duplicates.

Step 1: Highlight the group of cells that contains the duplicate values you'd like to delete.

Step 2: Click on the Data tab on the Ribbon Menu at the top of the screen. Then navigate to the Data Tools section of the Data tab menu.

how to remove duplicates in excel screenshot 1

Step 3: In the Data Tools section, click on the Remove Duplicates button. (It looks like three blue and white cells with a red X.)

Step 4: In the Remove Duplicates dialog box that appears, and in the Columns section, check any boxes next to the spreadsheet columns where you'd like the duplicates to be deleted. Uncheck the ones you'd like left alone.

how to remove duplicates in excel screenshot 2

Step 5: Then click OK.

Excel will then scan the selected columns for duplicates and remove them. When it's done, a dialog box will pop up notifying you how many duplicates were removed and how many unique values were left alone. It may also say that it couldn't find any duplicate values to remove. If that's the case, scroll to the next section.

how to remove duplicates in excel screenshot 3

Troubleshooting: What if Excel doesn't remove all the duplicates?

Sometimes, Excel doesn't remove all of the duplicates that are visible to us in a spreadsheet. This is often because Excel's Remove Duplicates tool may consider two duplicate values to actually be unique values instead, usually because of some underlying difference in formatting or another not-so-visible-to-us difference. If you're running into this issue, here are a couple of possible fixes you may want to consider:

  • Make sure all values are formatted the same. The Remove Duplicates tool doesn't work on numbers written in different formats. Just because the Conditional Formatting tool was able to correctly identify and highlight the duplicate values, that doesn't mean the Remove Duplicates tool will do the same. The Conditional Formatting tool will overlook the formatting issues as long as the number values are the same. The Remove Duplicates tool will not overlook formatting issues and will consider formatting differences as indicators that two duplicate values are actually unique. For the Remove Duplicates tool, even though 5 and 5.00 and $5 all have the same numerical value, they are still unique (not duplicate) values because they are formatted differently. So be sure to format all of your number values the same way before running the tool again.

  • Look for extra spaces in your values' cells. If your duplicate values have extra spaces in their cells, those spaces will also cause the Remove Duplicates tool to consider them to be unique values. Removing these spaces can help the Remove Duplicates tool to better detect the duplicate values you want to remove.

Editors' Recommendations

How to use Bing Image Creator to generate AI images for free
Bing Image Creator generated a realistic, yet artistic image of a hand drawing a hand.

Bing search made a giant leap forward in popularity and gained new conversational abilities when Microsoft added OpenAI's GPT-4 technology with the new ChatGPT-based Bing Chat tab. Now. another mode of operation is available with Bing Image Creator, which turns your written description into a picture.

According to Microsoft's blog post, Bing Image Creator uses a more advanced version of OpenAI's Dall-E. That means it can produce high-quality, photorealistic digital pictures, drawings, and paintings for you based on the text prompts you supply.
How to get access to Bing Image Creator
There are two ways to use Bing Image Creator. The simplest is to go to, which brings up Image Creator in preview right in your browser. This is available to everyone, and is a good place to try it out, even on mobile.

Read more
How to use DALL-E 2 to generate AI images
DALL-E 2DALL-E 2 Image on OpenAI.

As AI image generation skyrockets in popularity, many people are looking for easy ways to do it, such as how to use DALL-E 2 to generate AI images on your own computer. DALL-E (yes, named after WALL-E, as well as Salvador Dali) and its successor DALL-E 2 brought their own brand of AI image generation to the internet, developed by OpenAI, the creator of ChatGPT. but access to the software has been highly restricted.

Read more
Bing Chat: how to use Microsoft’s own version of ChatGPT
Bing Chat shown on a laptop.

Microsoft has added AI to its Edge browser and Bing search engine, and it's powered by the same advanced technology that OpenAI used to create ChatGPT. It's also available in mobile apps, enabling AI interaction by voice.

Here's how to sign up and use Bing Chat today.
How to get Bing Chat

Read more