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

Screenshot of how to remove duplicates in an Excel spreadsheet

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

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:

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.