Find and Delete Duplicate Rows in Excel

Posted May 6, 2024

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 quickly locate duplicates and then, once located, remove them super-quick!

The steps below cover:

How to Highlight Duplicates in Excel

The worksheet below holds data exported from an in-house system. Unfortunately, the’ Invoice #’ column has a number of 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 quickly identify duplicates.

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

Duplicates in Excel

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.

Duplicate Excel

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.

Highlight duplicates in Excel

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

Excel highlight duplicates

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 then click the “Quick Analysis” button, which will be displayed beside the selected range.  Select ‘Duplicate Values’ option.

Excel delete duplicate rows

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 of the data content into this one cell. These steps will also work using the CONCAT function.

Excel duplicate formula

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 of the cells on the row, click OK. The content of all of the cells will be pulled into one cell.

Delete duplicate rows in Excel

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 duplicates in Excel

Delete Duplicate Rows in Excel

Once you have located the duplicates, you will 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 filter and display only the duplicates quickly.

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 currently 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.

Excel delete duplicates

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.

How to identify duplicates in Excel
Step 5: You will have now 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, only select that column.

Excel remove duplicate rows

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.

Count duplicates in Excel

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? I'm here to empower your journey with Excel, aiming to make your daily tasks more efficient and boost your potential.

Share your thoughts in the Comments below - your insights not only enrich others, they also help me tailor future content to your needs.

And if you're looking to take a step further, join our exclusive '
Insider Group'. As a member, you'll receive Weekly Super-Tips, and early access to in-depth tutorials. Sign up Today!"

Happy Excel-ling!!

Was this blog helpful? Please let us know in the Comments below.

Excel removing duplicates

Finding and removing duplicate data in Excel

Wanting to stop manually skimming through excessive lines of information to weed out duplicates in Excel? Follow this step by step guide to learn how to locate and remove duplicates in your Excel worksheet.

Instructions

Locating duplicates in a single column of data using Conditional Formatting

  1. Select the column that holds the duplicate data.
  2. From the Home tab, in the Styles group click Conditional Formatting. A drop down menu is displayed.
  3. Select Highlight Cell Rules from the menu.
  4. Select Duplicate Values to quickly identify duplicate or unique records.
  5. Leave the option 'Duplicate' in the condition to be matched and select the format you would like from the drop down list.
  6. Click OK. Duplicate data will be displayed with the selected format.

Locating rows where the content of all cells is duplicated

  1. Select an empty cell to the right of the existing data.
  2. On the Formulas tab, Select Text, then CONCATENATE.
  3. In the Function Arguments dialog box enter each individual cell for the row into the Text boxes.
  4. Once you have added all of the cells on the row click OK.
  5. Copy the function down the column and then apply Conditional Formatting to the same range of data to find the duplicates. Only the cells where all cell content is the same will be formatted.

Removing duplicate rows of data

Using Filter

  1. Select any cell in the data list and then from the Data tab click Filter.
  2. Click the filter drop-down arrow on the column that is currently displaying duplicate formatting.
  3. From the drop-down list select Filter by Color and then select the cell colour or font colour depending on your conditional formats.
  4. Delete the duplicate rows.

Using the ‘Remove Duplicates’ button

  1. Select any cell in the data list and then from the Data tab select Remove Duplicates.
  2. Select one or more columns that contain duplicate values.
  3. Click OK.

Notes

Tip: to delete rows quickly hold down your CTRL key and click 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.

Did you try this?

If you found this blog helpful please leave a comment below.

If you enjoyed this post check out the related posts below.

Elevate your Excel game and become a pro with our exclusive Insider Group

Be the first to know about new tutorials, videos, and tips for Microsoft 365 products. Join us now and claim your exclusive bonus, your list of Essential Excel Skills to become proficient in Excel!

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
Skip to Instructions
>