How to remove duplicates in Excel

How to remove duplicates in Excel

Image: Andrei Popov via iStock/Getty Images Plus

For most of us, getting organized is a chore. I’ll have a mountain of information and numbers in my head, from grocery budgets to scheduling who does what and when, and one of the ways I’m starting to organize my mind is, surprisingly, by open an Excel workbook.

Also: What is the new Bing? Here’s everything you need to know

Now that we’ve figured out how nerdy I really am, organizing data involves more than just typing into a spreadsheet. Dumping data can result in duplicate values, usually when information is imported from a different file or when a large amount of information is entered over a long period of time.

Either way, we’ll explain how to remove these duplicates in Excel so you can streamline your spreadsheet and work.

How to remove duplicates in Excel

There are several ways to remove duplicates in Excel. We will explain how to remove duplicates with a shortcut and how to find them with conditional formatting.

Method 1: Remove duplicates with a shortcut

This Excel option makes it easy to remove duplicate entries with just a few clicks.

Select only the cells you want to check for duplicate values.

Select cells to remove duplicates

Maria Diaz/ZDNET

Go to the Data in your toolbar at the top of the screen, then click To withdraw Doubloons.

Go to Data then Remove Duplicates

Maria Diaz/ZDNET

A pop-up window will appear prompting you to select the columns you want Excel to check for duplicates.

If your table has headers, check them at the top. We want to check all columns for duplicates, so we’ll leave them all selected.

select the columns you want to check and remove duplicates

Maria Diaz/ZDNET

Click OK to remove all duplicates, then an alert will tell you how many duplicates have been removed and how many unique values ​​remain.

Excel will remove duplicate values ​​and show how many have been removed

Maria Diaz/ZDNET

But what if you want to see your duplicates to determine if you really need to delete them? This is where the magic of conditional formatting comes in.

Method 2: How to find (and remove) duplicates via Conditional formatting

Instead of automatically removing all duplicate entries, you can use conditional formatting to highlight them and then determine which ones to remove. This is especially useful if you need to keep duplicate data in some parts of your spreadsheet but not others.

Also: How to Find Files Faster in Google Drive

Conditional formatting will only apply to selected cells, so choose the ones you need to check for duplicates.

Select Cells to remove duplicates

Maria Diaz/ZDNET

Of Home tab on your toolbar at the top of the screen, click Conditional formatting. Place your cursor over the option to Highlight Cell Rulers then select Duplicate values from the menu.

Going to conditional formatting and hovering over the cell highlight rules reveals a menu

Maria Diaz/ZDNET

Essentially, we make Excel highlight duplicate values ​​in our selected cells, so they can be easily spotted as you scroll through the spreadsheet. The new formatting rule is where you will choose what these duplicates will look like.

Update the formatting and click OK

Maria Diaz/ZDNET

The Excel spreadsheet will now show duplicate values, so you can browse and delete them if necessary.

Duplicate values ​​are formatted to appear in red

Duplicate values ​​appear in red, allowing you to browse to edit or delete them.

Maria Diaz/ZDNET


Do I need a formula to remove duplicates in Excel?

A formula is not needed to remove duplicates, although there are ways to remove them using a formula. Excel has made it easier for users with a shortcut button under Data which will automatically remove duplicates.

How to find duplicates in Excel without deleting them?

The best way to do this is to use method 2 described above: using conditional formatting to highlight cells that follow a formatting rule. When choosing how to format these cells, make sure the formatting stands out and is different from what your table follows.


Leave a Reply

Your email address will not be published. Required fields are marked *