Trying to find duplicates in an Excel column? Follow these simple steps.
Excel remains a staple in most tech stacks, but it can sometimes be hard to find and utilize its almost endless list of tools and features. For novice and occasional users, this means that common errors can add up quickly, creating big problems for your business. One of the most frequent oversights is failing to prevent duplicates, and then not knowing how to check for them when they do silently occur. Luckily, learning how to find duplicates in Excel can be a straightforward task.
What is a duplicate in Excel?
Duplicate data occurs whenever the same information is populated across two or more objects. For instance, if two different cells in your worksheet contain the number 10 or the name John Doe, those are examples of duplicate data. Duplicates can occur across rows, columns, and sheets. Sometimes this duplicate data is needed, but other times it can be unintentional and can get in the way of your calculations and workflows.
As an example, if your business uses Excel workbooks to keep track of your customer list, having a customer on the list twice can lead to them receiving extra marketing materials, such as brochures and emails. On the other hand, if your sales team is trying to keep up with a customer's communications and orders, duplicate entries can make it difficult to keep that information organized.
Of course, duplicate entries cause the most trouble when you're trying to handle calculations, especially if they are monetary in nature. While an extra $10 sale entry in your workbook is probably within your margin of error, an extra $100 or $1,000 entry can cause a major headache.
Human error and a range of other things, such as insufficient data validation, can lead to duplicates in your Excel spreadsheets. Finding duplicates in Excel can be tough, especially when working in a large workbook; you can't simply manually scan the sheets trying to find duplicates in an Excel column. Instead, you need to learn how to identify duplicates in Excel the proper way, using built-in tools that will first highlight those duplicates and then help you remove them where needed.
How to identify duplicates in Excel
It's easy enough to identify duplicates in Excel if you know how to use the available tools of this powerful spreadsheet software. Here's what you need to do.
Step 1: Select the cells you want to check
Using Excel to find duplicates is as easy as activating one of its many pre-existing features. This process begins by selecting the cells you want to check.
If you want to check an entire sheet, use Command-A on a Mac or Ctrl+A on Windows. Otherwise, click the square in the upper left corner of the sheet to select all of its data.
Source: Lifewire[1]
Step 2: Turn on the highlighting tool
Go to Home > Conditional Formatting > Highlight Cells Rules.
Source: Microsoft[2]
Step 3: Choose your formatting
Select Duplicate Values in the sub-menu and a dialog box will open. Leave "Duplicate" in the first box. In the second box, choose the formatting you want to apply to the duplicate values. When you're ready, click OK.
Source: Microsoft[2]
Common mistakes when finding duplicates in Excel
Are you wondering how so many duplicates found their way into your Excel workbook? Struggling to get rid of data that is clearly duplicated from another area? Here are some common mistakes and considerations that may be getting in your way.
Check for trailing and leading spaces. Even if two entries look identical to you, Excel will consider them unique if they have different spacing. Trailing spaces are especially pesky since it can be impossible to see them without clicking into the cell and checking for them yourself.
Standardize your number formats. "Ten" and "10" are unique entries as far as Excel is concerned, which is one reason why it's important to standardize your number formats. Generally, Excel will treat "10" and "10.00" as the same figure, but not when it comes to the tool that removes duplicates for you.
Set up data validation. You can avoid future duplicates by setting up Data Validation, which can ensure all data entered into any given row or column is unique from any other entry. This is especially helpful when handling non-numerical information where duplicates are bound to be unwanted (like email addresses).
Doing more with your spreadsheets
Now that you know how to find duplicates in Excel, do you wonder what else you could be doing to speed through your work day? Check out the newest tools for spreadsheet management, data entry, and productivity to get some inspiration and save time on your next task.