How to Find Duplicates in Excel
Spreadsheets are often the best place to store and manipulate data. Most spreadsheet tools, like Excel, have many ways to analyze, filter, and transform said data. One common task is to identify duplicate data, like the same pizza place listed twice in a list of your favorite places. Once you’ve found any duplicates, you can either remove them or count them.
In this article, you’ll learn:
- How to highlight duplicates in Excel
- How to find duplicate values in Excel by using formula like COUNTIF or VLOOKUP
The popular methods of finding duplicates in Excel are ordered from easiest to most difficult. Let’s begin by taking a look at the first (and easiest!) method.
How to Highlight Duplicates in Excel
One of the most popular ways to identify duplicate data is to highlight them. Highlighting also happens to be the easiest of the three approaches to find duplicates in Excel and it provides a visual aspect other duplication identification methods lack. So, for all the visual learners out there, let’s get started e-highlighting with the simple steps outlined below.
- Select the cell range in which you want to highlight duplicates
- Ensure you’re in the Home tab (on the upper left), then select the Conditional Formatting dropdown
- Opt for Highlight Cells Rules and click on Duplicate Values…
As shown above, the default settings include a Classic Style while 2 and 3-Color Scale, Data Bar, or Icon Sets are additional options. You may opt to keep the default Format only unique or duplicate values and subsequent duplicate when it comes to choosing which values in the selected range you want.
Light Red Fill with Dark Red Text is the default Format with setting. This is where you determine the text and fill color for your highlighting. There are other suggested options, and Excel also allows for a Custom Format… Once you’re satisfied with the settings, click OK and you’re done!
A similar method of highlighting duplicates in Excel is available in Google Sheets and other spreadsheet tools. And, though a well-liked method, there are advantages to instead using an Excel formula to identify duplicates.
How to Find Duplicate Values in Excel Using Formulas
Another way data analysts can check for duplicates is via Excel formula. While the thought of a page-long formula including various spreadsheet cells and mathematical symbols is daunting, the formulas used to check for duplicates in Excel are actually quite simple. In fact, they’re a great way to dip your toe into other Excel formulas, such as =CONCATENATE, which we describe in Advanced Excel Skills and Formulas to Impress Your Boss.
COUNTIF to Find Duplicates
The duplicate-checking formula uses =COUNTIF to “count” which cells contain data that appears more than once throughout the spreadsheet. Resulting values can either be “TRUE” (indicating duplicate data) or “FALSE” (showing non-duplicate data).
You may wish to begin by adding a heading like “Count” (or something similar) to a blank column, though this is optional. Then, do the following:
- Copy and paste this formula into the first cell of a blank column:
=COUNTIF(A:A,A1)>1 - Change the A in the formula to coincide with the letter of the cell column you wish to find duplicates
- Drag the cell’s contents down to the cells below
That’s it; any duplicate data will be identified in the new column as “TRUE” while non-duplicate data is indicated as “FALSE.” Next, you can sort by your count column and see all of the duplicates (or non-dupes) bunched together. In contrast to the visual method, this is a quick way to not only identify but remove, duplicates.
There are tweaks you can make to this general formula if you wish to customize the value results. For instance, you may want duplicate data to be identified as “Duplicate” instead of the default “TRUE” or “Unique” instead of “FALSE.” Or, remove the >1 and you’ll see the number of copies, which could be more than two.
Those customizations aside, we still haven’t covered all the ways you can find duplicates in Excel.
How to Find Duplicate Values in Excel Using VLOOKUP
The final manner of finding duplicates in Excel applies if you have two data columns. Another formula, the method uses =VLOOKUP to compare two separate columns for shared data and displays the commonalities in a third column.
In a third, blank column, add some sort of heading such as “Vlookup.” Then, get started with the following steps:
- Copy and paste
=VLOOKUP(B2,$A$2:$A$14 ,1,FALSE)into the second cell of the third, blank column - Adjust the cell letters and numbers to fit your data
- Drag the original cell down to the rest of the rows
With the VLOOKUP formula, data unique to columns A and B are shown as “#N/A” while duplicates appear as themselves. And this concludes the most popular ways to find duplicates in Excel. What you do next is up to you, though we have a few suggestions. First, you may want to remove any duplicate data. Then, if your data contains location information, you can always map it for better visualization.
Make A Map with Your Data
Finally, make use of our Excel mapping tool to copy and paste your data from your spreadsheet directly to a custom interactive map like the one below, no coding required.
View Make a Map of My Location and Favorite Places in a full screen map
You can change the map’s base style and markers, group by multiple data columns, and more. Get started today.
