How to Find, Highlight & Remove Duplicates in Excel

Sometimes you may want that you will observe which rows are duplicate in the spreadsheet. Then you can manually delete those rows. It is easy to mark the duplicate rows in a column. For a long list it will take time to delete the duplicate ones manually. But for a small list it works nice. Whatever, the technique is here.

Highlighting Duplicate Rows: Using Conditional Formatting

At first, you will see how to use conditional formatting feature of Excel to highlight duplicates.

Here’s how to do:

  1. Select the table column or range of cells where you want to check duplicate values. In our example we have used a table and clicked the column letter A to select the table.
  2. Choose Home ⇒ Conditional Formatting⇒Highlight Cells Rules⇒ Duplicate Values. The “Duplicate Values” dialog box will appear in the screen. Choose whether you want to highlight the duplicate ones or unique ones from the options. You can also select colors to highlight duplicate ones. We have selected ‘Duplicate’ and “Green Fill with Dark Green Text”.
  3. Click OK. Excel highlight the duplicate rows with different background and text color, as shown in the image below. If you add a new row in the table and it is duplicate of one of previous rows, Excel automatically will highlight it.
Data clean-up techniques in Excel: Highlighting duplicate rows

Duplicate Values dialog box.

Note: You can select more than one column to highlight the duplicate rows. But there is a little technical problem with this selection. You should be aware of this problem. For example, if a text appears under one column and in another column, Excel will highlight this text as a duplicate though this text is not actually a duplicate text.

Read More: Finding out the number of duplicate rows using COUNTIF formula

Tip: It may happen that your table contains huge data. And you want to see the duplicate rows at the top of the column. Excel provides the technique to sort the cell based on colors. In our example, click the table header “Name”, it will show you some options: Sort A to Z, Sort Z to A, Sort by Color. Select Sort by Color and then Filter by Cell Color. Your output will look like the following image.
Highlighting duplicate rows in Excel

You can filter the cells according to color. For a large table, it is helpful to filter the duplicate rows.

Highlighting duplicate rows

Arranged duplicate values top of the column

This method looks at actual values, not formatted values. For example, 05/12/2013 and 05 Dec 2013 – these two have the same/actual value 05/12/2013, but they are displayed differently when the second one is applied “Long Date” format. In same way $500.50 is different from 500.50 when considered display values, though the first one is just the formatted product of 500.50 value. Observe the following two images. You will get ideas about actual values and formatted values.

Removing Duplicate Rows

Long date format used. Cell contains 5/12/2013 but cell display 5 Dec 2013.

Removing Duplicate Rows

Currency format is used. Cell contains 500.5 but the cell display £500.50 due to using Currency Format

Identifying duplicate rows using COUNTIF function

Say you have a column with duplicate rows. You just want to find out which rows are duplicate and how many times they are duplicated. You can find out the duplicate ones with COUNTIF formula. We are going to show how to use COUNTIF formula and how to find out the duplicate ones using this formula. Now this system, I mean using COUNTIF formula works with the values cells contain, not the formatted values.

COUNTIF Function Syntax: COUNTIF (range, criteria)

The range is the range of cells from where you want to find your values.

Read More: How to Remove Duplicate Rows in Excel Table

The criteria can be expressed in many ways. It can be number, expression, cell reference, or text to be fulfilled.

An Example of COUNTIF formula: COUNTIF(A1:D5, ">250"): how you will read this? Simple: Count in the value if it is greater than 250 in the cell range A1 to D5.

Observe the following image. We have applied this formula (COUNTIF(A1:D5, ">250") ) in cell E7 and E7 displays the total numbers that are greater than 250 in the cell range A1 to D5.

COUNTIF Formula

COUNTIF(A1:D5, “>250”) formula we are applying in Cell E7.

Click here to know more about COUNTIF() function.

We shall apply COUNTIF formula to find out the duplicate rows in a column or table. In our example, we have used a column.
First of all, let’s see the inside view of our example file. See the image below.

Using COUNTIF formula to find out the duplicate rows.

Using COUNTIF formula to find out the duplicate rows. Inside data of our example.

  1. Click B1 cell to select it. Write this formula in this cell: "=COUNTIF (A:A, A1)". A:A means the column A and criteria A1 means: the value of cell A1. In one sentence the whole command is: Count in the value if the value is equal to A1 in column A.
  2. We shall now copy this formula, =COUNTIF (A:A, A1), into cell B2 to B23 using relative cell references. To do this, select cell B1. A square box will be shown in the bottom-right corner of cell B1, it is called Fill Handle. Click Fill Handle, hold it and grab until you reach cell B23.
  3. Release the mouse button and you will get the following image.
Finding out duplicates using COUNTIF formula.

See the image. Some values are greater than 1. 4 is displaying after Accounting, it means Accounting has been duplicated for 4 times in column A.

Download Working File

Download the working file from the link below:

Highlighting-duplicate-rows.xlsx

duplicate-value-ex.xlsx

Read More…

How to Compare Two Columns in Excel For Finding Differences

How to Find Unique Values in Excel & Detect Duplicates


Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

We will be happy to hear your thoughts

      Leave a reply