When you work with Excel spreadsheets, you're likely to encounter duplicates in your data at some point. There are many potential causes of duplicates-- for example, you might have exported data from different sources or consolidated multiple spreadsheets. Regardless of the cause, it's time-consuming to find and remove duplicates manually. In this article, we'll show you how to minimize the work involved in cleaning up your data by using Excel to remove duplicates.
If you're planning to follow along with the examples we present in this tutorial, you'll need to have Microsoft Excel installed on your computer, Basic knowledge of Excel is recommended to get the most out of this article.
Microsoft Excel can be downloaded on Windows, macOS, Android or IOS. It's best to use a supported version of Excel, preferably 2013 or newer. To determine what version you're running, navigate to the "About" section of your Excel application.
In some cases, you may want to verify which cells in your spreadsheet contain duplicates before you attempt to modify any data. Excel offers custom highlighting rules that allow you to highlight duplicates by formatting the affected cells.
If you'd like to highlight the duplicate cells, you'll need to select all the cells by pressing CTRL+A, or command+A on a Mac, on your keyboard. You can also click the small "Select All" square button in the upper left-hand corner of the current sheet.
Once all of the cells are selected, you can navigate to the Home tab, click the Conditional Formatting dropdown button and select "Duplicate Values".
A modal window allowing you to enter a new formatting rule should pop up at this point. Here, you can create a rule that will format the duplicate values in your sheet.
In order to delete duplicate values, you'll first have to select the range of cells that you'd like to check and modify. Click the column letter at the top of the sheet to select an entire column, or click a row number to select an entire row. You can also left-click and drag your mouse over a custom range of cells.
Go to the Data tab located at the upper middle part of the Excel menu, and click the "Remove Duplicates" button:
My data has headers. This preserves the header data by having Excel de-select the header row from the range.
You can also use an Excel formula to find and remove duplicates. Choose an empty column and row and input a formula using the COUNTIF() function to filter for a specific condition:
=COUNTIF(range, criteria)Using an absolute reference for a cell range in the formula will help us identify and remove duplicate cells.
In Excel, an absolute reference uses the dollar sign as an alias for the relative position of cells. For example, if you have 16 cells in one column, the range would be expressed as =COUNTIF(A1:A16,A1); however, you can lock the range to (A1:A16) using an absolute reference. Simply press F4, or use the Command+T shortcut key in macOS, while you're still editing the formula in the cell.
After locking the range, the formula should look like this: =COUNTIF($A$1:$A$16,D4). A number will now appear in the cell location for the formula that counts the duplicates. Just drag the formula down to have it duplicated for each row or column.
Any formula cell that contains a number of two or greater indicates that the row has duplicate values. The screenshot below shows a typical result of the COUNTIF function:
The last method that can be used to remove duplicate values in Excel involves an advanced filter search. Here's how to use it:
First, make sure the Data tab is selected in the Excel menu options. Click on the "Advanced" button and a dialog box will pop up.
In the pop-up window, you should see a radio button that lets you choose between the Filter the list, in place or Copy to another location options. If you use the latter, you can just click on the data needed for the listed range.
Select the range where you want to remove duplicates on it. The range selection will look something like Sheet1!$A$1:$A$16, with the values varying based on the columns and rows you select.
Leave the input field for the "Criteria" range blank.
Click the location where you want to copy the filtered list. The location will look something like Sheet1!$E$1.
Check the box that indicates "Unique" records only, then click on OK.
At this point, your duplicates should be filtered out and removed.
If you find yourself dealing with duplicate data in your Excel spreadsheet, there's no need to take a manual approach to the task. You can get the job done quickly with some useful Excel features and functions. In this article, we looked at a few different ways to remove duplicates in Excel. With these handy tools at your fingertips, you'll be ready to clean up the data in your own Excel worksheets.
