Find and Delete Duplicate Rows in Excel

Unlocking the ability to find and delete duplicate rows in Excel is not just a time-saver; it’s a game-changer. However, it can be an absolute chore for many people who haven’t learned how to do this quickly in Excel.

One of the many things I love about training is that I get to work with so many different people from various organisations. However, it doesn’t matter what industry or department I work in. The challenges of working with duplicates in large data lists are pretty much the same.

Something I see often is the need to quickly locate and remove duplicate rows of data (excellent for VLOOKUP and XLOOKUP). Most people sort the data and then manually skim through the excessive lines of information, trying to weed out the annoying duplicates. This works, but boy-oh-boy, it can be a time-waster.

Wouldn’t it be easier to have Excel delete the duplicates for you? Then, all you need to do is deal with them.

In this post, I’ll walk you through how to locate duplicates quickly and then, once located, remove them super-quick!

How to Highlight Duplicates in Excel

The worksheet below holds data exported from an in-house system. Unfortunately, the’ Invoice #’ column has duplicate invoice numbers. Using the ‘Conditional Formatting’ feature, I can apply formatting to any cells with duplicate content. All other cells will remain as they are. This is an excellent way to identify duplicates quickly.

Step 1: Select the column that holds the duplicate data.  In this case, it’s the ‘Invoice #’ column.

Step 2: From the Home tab, in the Styles group, click Conditional Formatting. A drop-down menu will appear. Select Highlight Cell Rules from the menu.

Step 3: Select Duplicate Values to identify duplicate or unique records quickly.

Step 4: The dialogue box will display the condition to be matched.  In the example below, the condition is ‘Duplicate’.  This setting will ensure any duplicate cells within the selected column will have the formatting applied to them.  The option box on the right displays the format that will be applied if the condition is met.  There are a range of pre-set formats available on the drop-down arrow, or if you would prefer, you can create your own by selecting Custom Format from the drop-down list.

Step 5: Select your format options and then click OK.  All duplicates will now be formatted and easily identified.

Note: You can also use the Quick Analysis tool to apply Conditional Formatting using default formats quickly.  To do this, select the data range and click the “Quick Analysis” button, which will be displayed beside the selected range.  Select the ‘Duplicate Values’ option.

Identify Duplicate Rows in Excel

In the steps above, we located rows with only duplicate invoice numbers. But what if you want to find duplicates where ALL of the data in a row is exactly the same as another? For example, if you were trying to locate duplicates where the invoice number, date, customer name, product name, and cost price were all exactly the same.

Here’s a quick way to do this.

Step 1: The first thing to do is go to an empty cell to the right of the existing data.  Using the CONCATENATE function, I will pull all the data content into this one cell. These steps will also work using the CONCAT function.

Step 2: In the Function Arguments dialog box, enter each individual cell for the row into the Text boxes.

Note: that as you enter a cell reference into one text box, another will appear. You can have up to 255 references within the function. Once you have added all the cells on the row, click OK. The content of all of the cells will be pulled into one cell.

Step 3: Copy the function down the column and apply conditional formatting to the same range of data to find the duplicates. Only the cells with the same cell content will be formatted.

Delete Duplicate Rows in Excel

Once you have located the duplicates, you usually want to remove them. I see two different methods used repeatedly. I’ll outline both. You find your favourite.

Remove Duplicates with Filter

If your conditional format applies a coloured background or changes the colour of the cell font, you can use the Filter command to quickly filter and display only the duplicates.

Step 1: Select any cell in the data list, and then from the Data tab, click Filter.

Step 2: Click the filter drop-down arrow on the column displaying duplicate formatting. From the drop-down list, select Filter by colour and then select the cell or font colour, depending on your conditional formats.

Step 3: Excel will filter out the unique records and only display the duplicates.

Step 4: Now delete the duplicate rows.  To do this quickly, hold down your CTRL key and click on each of the duplicate row numbers.  Once they are selected from the Home tab, click Delete, Delete Sheet Rows.  All selected rows will be deleted.

Step 5: You will have removed the duplicates and will only have the unique records remaining.  All formatting should now be removed, as you no longer have duplicate values.

Remove Duplicates from the Data Tab

Using “Remove Duplicates”, you can have Excel quickly delete any rows of data where duplicates are found.

Step 1: Select any cell in the data list, and then from the Data tab, select Remove Duplicates.

Step 2: Select one or more columns that contain duplicate values. For example, if the entire row is a duplicate, leave all columns selected. If you want to remove the entire row based on duplicates found in just one column, select that column only.

Step 3: Click OK. Excel will delete any rows where duplicate values are found in the selected columns.

Be aware that Excel will indicate how many records it has found with duplicates. However, you aren’t shown which of the duplicates will be deleted. If you want to view the duplicates prior to deleting, apply conditional formatting first.

Conclusion

There is no need to spend a lot of time locating duplicates. Using the steps above, you will be able to identify duplicates in Excel and deal with them swiftly.

Was this blog helpful? Let me know in the comments below.