'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.
How to remove duplicates in Excel
For most of us, getting organized is a hassle. I'll have a mountain of information and numbers in my head from grocery budgets to calendarizing who's doing what and when, and one of the ways I begin getting my mind organized is, surprisingly, by opening an Excel workbook.
Also: What is the new Bing? Here's everything you need to know
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 and your work.
How to remove duplicates in Excel
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.
Method 1: Removing duplicates with a shortcut
This option from Excel makes it easy to remove duplicate entries with just a few clicks.
1. Select the cells that may have duplicate values
Select only the cells that you want to check for duplicate values.
2. Go to Data, then click on Remove Duplicates
Go to the Data tab in your toolbar at the top of the screen, then click on Remove Duplicates.
3. Select which columns to check for duplicates
A popup will appear prompting you to select the columns that you'd like for Excel to check for duplicates.
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.
4. Click OK and see how many duplicates were removed
Click OK to remove any duplicates and then an Alert will tell you how many duplicates were removed and how many unique values remain.
But 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.
Method 2: How to find (and remove) 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. This is particularly useful if you need to keep some duplicate data in some parts of your spreadsheet but not others.
Also: How to find files faster in Google Drive
1. Select the cells that may have duplicate values
The conditional formatting will apply to the selected cells only, so choose the ones you need to check for duplicates.
2. Click on Conditional Formatting
From the Home tab on your toolbar at the top of the screen, click on Conditional Formatting. Have your cursor hover over the option to Highlight Cells Rules and then select Duplicate Values from the menu.
3. Customize how you want to format the duplicates
Essentially, we're making Excel highlight the duplicate values in our selected cells, so that they can be easily spotted as you go through the spreadsheet. The New Formatting Rule is where you'll choose how those duplicates will look like.
4. Click OK and watch the duplicates appear
The Excel spreadsheet will now show which values are duplicates, so you can go through them and remove them if necessary.
The duplicate values appear in red, letting you go over them to edit or remove them.
FAQ
Do I need a formula to remove duplicates in Excel?
A formula isn't necessary to remove duplicates though there are ways to remove them using a formula. Excel has made it easier for users to do so with a shortcut button under Data that will automatically remove duplicates.
How do I find duplicates in Excel without removing them?
The best way to do this is to use the Method 2 outlined above: By using Conditional Formatting to highlight cells that follow a formatting rule. When you're choosing how to format these cells, make sure that the formatting stands out and is different as the one your table follows.