Excel Remove Duplicates Tutorial

Introduction

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.

Prerequisites

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

Screenshot of highlighting cells in Excel to delete duplicates

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.

Screenshot of a selected cells formatting rule for the Excel delete duplicates example

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:

Screenshot of how to remove duplicates in an Excel spreadsheet

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

Screen of an Excel delete duplicates example selecting header or columns targeted for the remove duplicates action

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

Excel remove duplicates example of an alert window showing number of values 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:

=COUNTIF(range, criteria)

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:

Screenshot of an Excel COUNTIF function to find duplicate cells in a spreadsheet

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

Conclusion

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.

An innovative marketing agency specializing in marketing tactics designed to produce positive results.
Office Address:
5541 McNeil Dr
Building A
Austin, TX 78729
(628) 333-3450

Email Us