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 versions
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.
Find duplicates in Excel
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.
Excel: highlight duplicates
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.
Delete duplicates in Excel
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:
- A dialog box will pop up, and you'll need to check the box that says
My data has headers. This preserves the header data by having Excel de-select the header row from the range.
- Next, select the columns that you'd like to check for duplicates in the modal window and click "OK". You'll see another modal window pop up-- this one will show how many duplicate values were found and removed.
Use an Excel formula to remove duplicates
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:
Using an absolute reference for a cell range in the formula will help us identify and remove duplicate cells.
Absolute reference in Excel
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.
Count unique values with an Excel formula
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:
Advanced filters in Excel
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 placeor
Copy to another locationoptions. 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
Check the box that indicates "Unique" records only, then click on
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.