Learn how to quickly remove duplicate data in Excel across rows, columns, and sheets.
Excel has been a staple in company toolkits for over 40 years. It serves a fundamental purpose and may be responsible for managing everything from your client list to product SKUs. Knowing how to remove duplicates in Excel is critical to ensuring seamless workflows and avoiding tedious (and potentially costly) errors in your business's database.
What is a duplicate in Excel?
A duplicate, also known as duplicate data, occurs when the same information is populated in multiple places within an Excel workbook. A duplicate could appear in multiple cells sequentially, but it may also be hidden further down a sheet, in another column, or in a different sheet within the workbook.
Duplicates are not always bad. For instance, you may have a sheet in your workbook that summarizes other sheets, causing there to be duplicates of values found elsewhere. In this case, you’d want to keep all versions of the data. However, duplicates are not always intentional or desirable, so it’s essential that you are able to find and highlight them for review.
There are also many scenarios in which duplicate data can have a direct impact on your business. For example, if you're using Excel for bookkeeping, duplicate data can lead to improper calculations. Or, if you're using Excel to store email subscribers, duplicate data can lead to the same person receiving your emails more than once, increasing the likelihood of your communications going to the spam folder.
Unintentional duplicates can be caused by incorrect data preparation processes and data entry errors. Without unique identifiers and validation checks, it can be difficult to prevent duplicates in your documents. The good news? Learning how to remove duplicates in Excel is simple.
How to delete duplicates in Excel
What do you do in Excel to remove duplicates? Well, you can't simply take a shortcut and automatically delete all duplicate instances. Before you can remove duplicate data in Excel, you need to be able to find them. This means you need to start by highlighting all of the data and then you can review the duplicates and decide which ones you want to keep or remove.
Method 1: Find and remove duplicates through conditional formatting
1. The first step is to highlight the cells you wish to check by holding your mouse button and dragging your cursor over them. If you want to select an entire sheet, press Command+A on Mac or Ctrl+A on Windows.
2. Excel has a built-in tool to highlight duplicates. Activate this setting by navigating to Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values. This will show a pop-up.
Navigate to Conditional Formatting and select Highlight Cells Rules, then Duplicate Values[1]
3. Once the pop-up appears, leave Duplicate selected in the first box and then choose how you want them formatted in the second box. When you're ready, click OK.
Leave Duplicate selected and choose formatting[1]
4. After highlighting the duplicates, make sure to review the findings before deleting any data. It's best to make a copy of your workbook at this stage before moving forward and deleting duplicates.
5. Under Data find the Remove Duplicates option. This will open a dialog box.
In the Data pop up, select Remove Duplicates[1]
6. In the pop-up, either check or uncheck the columns you wish to remove the duplicates from under Columns. For instance, if you do not want to remove any data from the January column, you would make sure to leave it unchecked.
Check or uncheck the columns you wish to remove the duplicates from under Columns[1]
7. When you're ready, click OK. After all of the duplicates have been removed, Excel will show you the number of duplicate values removed and the number of unique values that remain. These counts may include empty cells, spaces, etc.
Method 2: Find and remove duplicates with a shortcut
1. Press Ctrl+Space on Windows or Command+Space on Mac to select the entire column your cursor is on or Shift+Space to select the entire row.
2. Press Alt on Windows or Option on Mac to display the shortcuts. Press A to move to the Data tab and then press M to open the Remove Duplicates dialog box.
3. If you have not selected all the data within your worksheet, you will see the Remove Duplicates Warning. Press the Tab key to select the Remove Duplicates button and then use your Enter key to continue.
In the Remove Duplicates Warning pop up, press the Tab key to select the Remove Duplicates button, then use your Enter key to continue[2]
4. Press the Tab key to select the OK button and then press the Enter key twice to remove all duplicates in your selection.
Press the Tab key to select the OK button, then press the Enter key twice to remove all duplicates in your selection[3]
Key considerations when removing duplicates in Excel
Remove trailing or leading spaces. Even if two cells are duplicates, Excel can't find them if there are slight differences, like leading or trailing spaces. This means "John" and "John " will both remain in your workbook.
Standardize numbering styles. Numerals and spelled-out numbers are not recognized as duplicate data in Excel. For instance, "One two three" is different from "123." On the other hand, Excel sees "10" and "10.00" as the same number, but the Remove Duplicates tool still treats them as unique values.
Audit your formula ranges. If you set up a formula to check for duplicates in Excel, it is common for the worksheet to grow but the formula is not adjusted over time to check all of the rows. Use the auditing toolbar to make sure your formulas are looking at all of the data you intend to clean.
Improve data quality in your Excel workbooks
Data entry tools like Excel serve an essential purpose for businesses, but managing spreadsheets can be tedious at times. Now that you know how to remove duplicates in Excel, take the time to refresh your memory on the other best practices and how to make the most of the tool for core responsibilities like accounting.