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.
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.
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.
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.