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
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.
Step 2: Go to Data, then click on Remove Duplicates.
Step 3: A popup appears to select which columns 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.
Step 4: Excel will remove duplicate values and will advise how many were removed and how many unique values remain.
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.
Step 2: Go to Home, then Conditional Formatting. Hover over Highlight Cells Rules and select Duplicate Values.
Step 3: Use the New Formatting Rule box, you can customize how you want them formatted, then click OK.
Step 4: The spreadsheet will show which values are duplicates, so you can go in and remove them if need be.
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.
