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.
Go to the Data in your toolbar at the top of the screen, then click To withdraw Doubloons.
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.
Click OK to remove all duplicates, then an alert will tell you how many duplicates have been removed and how many unique values remain.
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.
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.
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.
The Excel spreadsheet will now show duplicate values, so you can browse and delete them if necessary.
Duplicate values appear in red, allowing you to browse to edit or delete them. Maria Diaz/ZDNET
FAQs
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.
