Home > Microsoft Office > Microsoft Office How To

How To Highlight Duplicates In Excel

Duplicates are common in Excel. Here is how you can highlight and remove them.
Last Updated on July 15, 2024
How To Highlight Duplicates In Excel
You can trust PC Guide: Our team of experts use a combination of independent consumer research, in-depth testing where appropriate - which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

You’ve probably heard of the term “duplicate data”, but what does it mean exactly?

Duplicate data refers to identical information being stored multiple times within a database or spreadsheet. This can cause confusion when it comes to sorting, filtering, and searching.

To prevent duplicate data from causing problems, you should always check for duplicates before performing any actions. If you want to remove duplicates from a column, you can use the Remove Duplicates function.

If you have an Excel table that contains duplicate values, you can sort the columns so that the duplicates are at the top. Then you can filter out the rows with the duplicates by using the Filter command.

What Is Microsoft Excel?

Microsoft Excel is one of the most popular programs used to create spreadsheets. It allows users to organize their work into tables and charts, as well as perform many other functions.

In this article, we’ll show you how to identify duplicates in Excel and how to highlight them.

1

How To Find Duplicates In Excel

Before you start looking for duplicates, you should make sure your data has been sorted properly.

Step

1

Select Cells

The first step is to select all the cells in the range that you wish to search for duplicates.

How To Find Duplicates In Excel

Step

2

Open Advanced Filter

Next, go back to the original cell where you entered the data. Now, press Ctrl+Shift+Enter to open the Advanced Filter dialog box, or head to Sort and Filter, and select Advanced Filter.

How To Find Duplicates In Excel

Step

3

Choose Contains

In the drop-down menu on the right side of the window, choose “Contains” and then click OK.

Step

4

Duplicates

Now, enter the name of the column containing the duplicates in the Search field. Click on the operator button next to the field and select “Is equal to”.

Now, type the name of the column without duplicates in the Value field. Click OK.

Step

5

Return To Worksheet

Click OK to close the dialog box and return to the worksheet.

Step

6

Move Duplicates

You should see a list of duplicates in the selected column. Highlight the row numbers of each duplicate and drag them to another location on the worksheet.

This will move the duplicates to a new location.

2

How To Identify Duplicates In Excel

When working with large amounts of data, it’s often difficult to spot duplicates. Fortunately, there are several ways to find duplicates in Excel.

Step

1

Sort & Filter

First, you can use the Sort & Filter commands. These tools allow you to quickly sort and filter through your data.

The Sort command sorts the data based on specific criteria. For example, if you wanted to sort the names of people in alphabetical order, you would use the A-Z option.

The Filter command filters the data based on a set of conditions. For example, if the data includes two different types of people (e.g., male and female ), you could use the Filter command to only display the names of males.

How To Identify Duplicates In Excel

Step

2

Auto Filter Function

You can also use the Auto Filter function. This tool automatically highlights duplicates in a column.

Go to ‘Data’, then go to ‘Special’. Select ‘AutoFilters’, and select the column where you want to apply the filter.

If you don’t have any duplicates in the column, the filter won’t appear. However, if you do have duplicates, they will be highlighted.

Step

3

Remove Duplicates Command

Finally, you can use the Remove Duplicates command. This tool removes duplicate rows from a table or chart.

Start by going to ‘Data’, and then go to ‘Tools’. Select ‘Remove Duplicates’ and then select the columns where you want to remove duplicates. Press ‘OK’ to confirm the operation.

How To Identify Duplicates In Excel

3

How To Find Duplicate Records In Excel

Sometimes, when you work with large amounts of data in Microsoft Excel, you may come across duplicate records.

For example, suppose you had a table of employee information. The table included the first name, last name, address, phone number, salary, department, and so forth for all employees at your company.

However, one of the employees has the same first name as another employee. How would you know?

Fortunately, there is an easy way to identify duplicates.

Step

1

Select Entire Table

First, open the table in Microsoft Excel. Then select the entire table by clicking anywhere inside the table area.

Step

2

Copy Table

Next, press Ctrl+D. This will copy the entire table into the clipboard.

Step

3

Paste

Then, paste the copied data back into the original table.

In the pasted data, you will see that some cells contain identical data.

These are your duplicate records, so you can keep track of them.

Final Thoughts

Duplicates can often be created in Excel, but it is simple to highlight and remove them. We hope this article has provided you with a useful guide on how to do this.

Andrew is one of three co-founders of BGFG, the parent company of PC Guide. A keen gamer and PC enthusiast, Andrew dabbles in a bit of writing sometimes - when he gets the chance to!