/>
X
Why you can trust ZDNet ZDNet independently tests and researches products to bring you our best recommendations and advice. When you buy through our links, we may earn a commission. Our process

'ZDNet Recommends': What exactly does it mean?

ZDNet's recommendations are based on many hours of testing, research, and comparison shopping. We gather data from the best available sources, including vendor and retailer listings as well as other relevant and independent reviews sites. And we pore over customer reviews to find out what matters to real people who already own and use the products and services we’re assessing.

When you click through from our site to a retailer and buy a product or service, we may earn affiliate commissions. This helps support our work, but does not affect what we cover or how, and it does not affect the price you pay. Neither ZDNet nor the author are compensated for these independent reviews. Indeed, we follow strict guidelines that ensure our editorial content is never influenced by advertisers.

ZDNet's editorial team writes on behalf of you, our reader. Our goal is to deliver the most accurate information and the most knowledgeable advice possible in order to help you make smarter buying decisions on tech gear and a wide array of products and services. Our editors thoroughly review and fact-check every article to ensure that our content meets the highest standards. If we have made an error or published misleading information, we will correct or clarify the article. If you see inaccuracies in our content, please report the mistake via this form.

Close

How to remove duplicates in Excel

If you've ever had a data dump into a spreadsheet result in a mess of duplicate values, we'll go over how to remove those on this how-to.
maria-diaz
Written by Maria Diaz, Staff Writer on

For most, getting organized is a hassle. Personally, I'll have a mountain of information and numbers in my mind and will feel the anxiety bubbling up, wondering where to begin to get it out. Opening an Excel workbook is typically the first step into doing that for me.

Now that we got how nerdy I really am out of the way, organizing data involves more than just inputting into a spreadsheet. Dumping data can result in duplicate values, typically when the 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 cover how to remove those duplicates in Excel so you can streamline your spreadsheet.

How to remove duplicates in Excel

How to remove duplicates in Excel
Image: Maria Diaz / ZDNet

There are a few ways to remove duplicates in Excel. We'll outline how to remove duplicates with a shortcut and how to find them with conditional formatting.

Removing duplicates with a shortcut

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

Step 1: Select the cells that may have duplicate values.

Select the cells to remove duplicates
Image: Maria Diaz / ZDNet

Step 2: Go to Data, then click on Remove Duplicates.

Go to Data then Remove Duplicates
Image: Maria Diaz / ZDNet

Step 3: A popup appears to select which columns to check for duplicates.

select which columns you want to check and remove duplicates from
Image: Maria Diaz / ZDNet

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

Step 4: Excel will remove duplicate values and will advise how many were removed and how many unique values remain.

Excel will remove duplicate values and will advise how many were removed
Image: Maria Diaz / ZDNet

What if you want to see your duplicates to determine if you actually need them removed?

This is where the magic of conditional formatting comes in. 

How to find duplicates through Conditional Formatting

Instead of automatically removing all duplicate entries, you can use Conditional Formatting to highlight them and then determine which ones to remove.

Step 1: Select the cells that may have duplicate values. 

Select Cells to remove duplicates
Image: Maria Diaz / ZDNet

Step 2: Go to Home, then Conditional Formatting. Hover over Highlight Cells Rules and select Duplicate Values.

Going to conditional formatting and hovering over Highlight Cells Rules reveals a menu
Image: Maria Diaz / ZDNet

Step 3: Use the New Formatting Rule box, you can customize how you want them formatted, then click OK.

Update formatting and click OK
Image: Maria Diaz / ZDNet

Step 4: The spreadsheet will show which values are duplicates, so you can go in and remove them if need be.

Duplicate values are formatted to appear in red

The duplicate values appear in red, letting you go over and editing or removing them.

Image: Maria Diaz / ZDNet

Do I have to use a formula to remove duplicates in Excel?

Though there are ways to remove duplicates using a formula, Excel has made it easier for users to do so with a shortcut button under Data that will automatically remove duplicates.



Related

How to remove bloatware from Windows 10
bloated-start.png

How to remove bloatware from Windows 10

Windows 10
How to get Microsoft Office for free
zd-microsoft-office.jpg

How to get Microsoft Office for free

Microsoft
Microsoft to roll out native Teams app optimized for Apple silicon in the coming months
teamsoptimizedapplesilicon

Microsoft to roll out native Teams app optimized for Apple silicon in the coming months

Collaboration