Categories
Device

How to remove duplicate rows in excel

  • Excel Tips
  • Excel Functions
  • Excel Formulas
  • Word Tips
  • Outlook Tips

How to remove duplicate rows in Excel ?

Most of you may meet a difficulty that there are many duplicate rows in your worksheet, and you need to remove them and just keep the unique rows. In this case, how can you get the duplicate rows removed quickly? This article will introduce some easy ways to remove duplicate rows in Excel for you.

  • Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
  • More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
  • Merge Tools : Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
  • Split Tools : Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
  • Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color ; Send Personalized Emails to Multiple Recipients in Bulk.
  • Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment.
  • More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.

Remove duplicate rows in Excel with Remove Duplicates function

Amazing! Using Efficient Tabs in Excel Like Chrome, Firefox and Safari!
Save 50% of your time, and reduce thousands of mouse clicks for you every day!

Supposing you have the following range which contains some duplicate rows, to remove duplicate rows from a worksheet in Excel, you can according to following steps:

How to remove duplicate rows in excel

1. Select the range you want to remove duplicate rows. If you want to delete all duplicate rows in the worksheet, just hold down Ctrl + A key to select the entire sheet.

2. On Data tab, click Remove Duplicates in the Data Tools group.

How to remove duplicate rows in excel

3. In the Remove Duplicates dialog box, leave all the checkboxes checked under Columns list box, and if your data contains headers, please check My data has headers option. See screenshot:

How to remove duplicate rows in excel

4. Then click OK, a prompt box will pop out to tell you the removing result, and all identical rows are removed except for the first identical row. See screenshot:

How to remove duplicate rows in excel

Note: With this function, you can also remove rows with the same values in certain columns. To do this, you just need to check the columns that you want to remove the duplicate values in the Remove Duplicates dialog box in step 3.

Remove duplicate rows in Excel with Kutools for Excel

If you have Kutools for Excel, with its Select Duplicate & Unique Cells utility, you can select the duplicate rows first, and then delete them at once.

Kutools for Excel includes more than 300 handy Excel tools. Free to try with no limitation in 30 days. Get it Now.

If you have installed Kutools for Excel, please do as following steps:

1. Select the range that you want to remove the duplicate rows.

2. Click Kutools > Select Tools > Select Duplicate & Unique Cells, see screenshot:

How to remove duplicate rows in excel

3. In the Select Duplicate & Unique Cells dialog box, select Duplicates (Except 1 st one) or All duplicates (Including 1 st one) as you need. And then check Select entire rows option. See screenshot:

How to remove duplicate rows in excel

4. Then click OK, all the duplicate rows are selected as following screenshots:

How to remove duplicate rows in excel How to remove duplicate rows in excel
Select duplicate rows except first one Select duplicate rows including first one

5. And then put the cursor to the selected row, right click and choose Delete from the context menu, see screenshot:

How to remove duplicate rows in excel

6. And you will get the following results:

How to remove duplicate rows in excel

When you are working with spreadsheets in Microsoft Excel and accidentally copy rows, or if you are making a composite spreadsheet of several others, you will encounter duplicate rows which you need to delete. This can be a very mindless, repetitive, time consuming task, but there are several tricks that make it simpler.

Getting Started

Today we will talk about a few handy methods for identifying and deleting duplicate rows in Excel. If you don’t have any files with duplicate rows now, feel free to download our handy resource with several duplicate rows created for this tutorial. Once you have downloaded and opened the resource, or opened your own document, you are ready to proceed.

How to remove duplicate rows in excel

Option 1 – Remove Duplicates in Excel

If you are using Microsoft Office Suite you will have a bit of an advantage because there is a built in feature for finding and deleting duplicates.

Begin by selecting the cells you want to target for your search. In this case, we will select the entire table by pressing “Control” and “A” at the same time (Ctrl + A).

How to remove duplicate rows in excel

Once you have successfully selected the table, you will need to click on the “Data” tab on the top of the screen and then select the “Remove Duplicates” function as shown below.

How to remove duplicate rows in excel

Once you have clicked on it, a small dialog box will appear. You will notice that the first row has automatically been deselected. The reason for this is that the “My data has headers” box is ticked.

How to remove duplicate rows in excel

In this case, we do not have any headers since the table starts at “Row 1.” We will deselect the “My data has headers” box. Once you have done that, you will notice that the whole table has been highlighted again and the “Columns” section changed from “duplicates” to “Column A, B, and C.”

How to remove duplicate rows in excel

Now that the entire table is selected, you just press the “OK” button to delete all duplicates. In this case, all the rows with duplicate information except for one have been deleted and the details of the deletion are displayed in the popup dialog box.

How to remove duplicate rows in excel

Option 2 – Advanced Filtering in Excel

The second tool you can use in Excel to Identify and delete duplicates is the “Advanced Filter.” This method also applies to Excel 2003. Let us start again by opening up the Excel spreadsheet. In order to sort your spreadsheet, you will need to first select all using “Control” and “A” as shown earlier.

After selecting your table, simply click on the “Data” tab and in the “Sort & Filter” section, click on the “Advanced” button as shown below. If you are using excel 2003, click on the “Data” drop down menu then “Filters” then “Advanced Filters…”

How to remove duplicate rows in excel

Now you will need to select the “Unique records only” check box.

How to remove duplicate rows in excel

Once you click on “OK,” your document should have all duplicates except one removed. In this case, two were left because the first duplicates were found in row 1. This method automatically assumes that there are headers in your table. If you want the first row to be deleted, you will have to delete it manually in this case. If you actually had headers rather than duplicates in the first row, only one copy of the existing duplicates would have been left.

How to remove duplicate rows in excel

Option 3 – Replace

This method is great for smaller spreadsheets if you want to identify entire rows that are duplicated. In this case, we will be using the simple “replace” function that is built into all Microsoft Office products. You will need to begin by opening the spreadsheet you want to work on.

Once it is open, you need to select a cell with the content you want to find and replace and copy it. Click on the cell and press “Control” and “C” (Ctrl + C).

How to remove duplicate rows in excel

Once you have copied the word you want to search for, you will need to press “Control” and “H” to bring up the replace function. Once it is up, you can paste the word you copied into the “Find what:” section by pressing “Control” and “V” (Ctrl + V).

How to remove duplicate rows in excel

Now that you have identified what you are looking for, press the “Options>>” button. Select the “Match entire cell contents” checkbox. The reason for this is that sometimes your word may be present in other cells with other words. If you do not select this option, you could inadvertently end up deleting cells that you need to keep. Ensure that all the other settings match those shown in the image below.

How to remove duplicate rows in excel

Now you will need to enter a value in the “Replace with:” box. For this example, we will use the number “1.” Once you have entered the value, press “Replace all.”

How to remove duplicate rows in excel

You will notice that all the values that matched “dulpicate” have been changed to 1. The reason we used the number 1 is that it is small and stands out. Now you can easily identify which rows had duplicate content.

How to remove duplicate rows in excel

In order to retain one copy of the duplicates, simply paste the original text back into the first row that has been replaced by 1’s.

How to remove duplicate rows in excel

Now that you have identified all the rows with duplicate content, go through the document and hold the “Control” button down while clicking on the number of each duplicate row as shown below.

How to remove duplicate rows in excel

Once you have selected all the rows that need to be deleted, right click on one of the grayed out numbers, and select the “Delete” option. The reason you need to do this instead of pressing the “delete” button on your computer is that it will delete the rows rather than just the content.

How to remove duplicate rows in excel

Once you are done you will notice that all your remaining rows are unique values.

The more complicated a spreadsheet gets, the easier it is to duplicate cells, rows or columns. Soon it’s difficult to see the real data from the copies and managing everything can become tiresome. Fortunately, spreadsheet pruning is simple if not time consuming but is can be made easier with a few tricks. Here are a few simple ways to remove duplicates in Excel.

How to remove duplicate rows in excel

Removing Duplicate Cells, Rows, & Columns

If you are editing an important or work spreadsheet, make a backup first. It could save time and heartache should something go wrong. Both the finding and removing parts of this tutorial are fairly safe for normal use as they use built-in tools. However, more complex spreadsheets containing formulas or filters already in place might cause you some headaches.

Quickly and easily remove duplicates in Excel

First, we need to identify if there are duplicates within a spreadsheet. In a small spreadsheet, they may be easily identifiable. In larger spreadsheets it may be difficult to identify without a little help. Here’s how to find them.

  1. Open your spreadsheet on the page you need to sort.
  2. Press Ctrl + A to select all.
  3. Click Conditional Formatting.
  4. Select Duplicate Values, set a style to highlight duplicates and click OK.

Now your spreadsheet will format each duplicate cell in the color you selected. It is a fast, simple way to see just how many duplicates you have within a sheet.

How to remove duplicate rows in excel

Once you know how many dupes you have, you can remove them in two simple ways. If you’re using Microsoft Office 2013/6 or Office 365, you have something of an advantage. Microsoft kindly added a remove duplicate function into Excel for just this occasion.

  1. Open your spreadsheet on the page you need to sort.
  2. Press Ctrl + A to select all.
  3. Click the Data tab and select Remove Duplicates.
  4. Select or deselect ‘My data has headers’ depending on whether yours has them or not.
  5. Click OK to remove the duplicates.

How to remove duplicate rows in excel

There is also another way to remove duplicates in Excel using advanced filters.

  1. Open your spreadsheet on the page you need to sort.
  2. Drag the mouse to include all the cells you want to filter.
  3. Click the Data tab and select Advanced.
  4. Check the ‘Unique records only’ checkbox and click OK.

This method removes all duplicates except those it thinks might be column headers. These you will need to remove manually. Other than that, it does the same job as remove duplicates.

There are other ways of easily removing duplicates in Excel using formulas but given how simple these two operations are, there really is no point using them. Do you have any other cool ways to remove duplicate entries? Let us know below if you do!

How to remove duplicate rows in excel

Duplicate values in a dataset can cause headaches if you do not know how to deal with them. In this guide, we’re going to show you how to find duplicates in Excel and also go over how you can remove or consolidate them using a few examples.

How to find duplicates in Excel

If you only want to locate the duplicate values, highlighting them is probably the easiest way to do so. With only two steps, you can change the color of the cells that have duplicate values. Let’s see how you can do this using an example.

First select the range of your data (You can select a range or a list).

How to remove duplicate rows in excel

In the Ribbon, follow the path Home > Conditional Formatting > Highlight Cell Rules > Duplicate Values

How to remove duplicate rows in excel

Clicking the Duplicate Values item pops a dialog with more options. You can select between highlighting duplicate values or unique values in a specific style.

How to remove duplicate rows in excel

Clicking the OK button applies the specified formatting to the selected range. In our list, “WATER” and “FIRE” values are listed more than once.

How to remove duplicate rows in excel

The good thing about this method is that Conditional Formatting works with every calculation. Thus, you don’t need to update this feature every time you get new duplicates. Let’s continue with how you can remove those duplicate values.

How to remove duplicate values

After you find duplicates in Excel, you can select rows one by one and delete them. However, the manual approach obviously might take too long if there is a lot of duplicates. Fortunately, you can use the Advanced Filter feature to remove them at once. Also note that this method is suitable if duplicate values reside in the same column.

Once again, start by selecting the column that contains the duplicate values. But this time, activate the Data tab in the Ribbon, and click on the Advanced icon from the Sort & Filter section. This action pops the Advanced Filter dialog.

How to remove duplicate rows in excel

In the Advanced Filter dialog, make sure that the Unique records only option is checked. Also, you can enable populating the filtered results on another range. Otherwise, Excel filters the results by hiding duplicate rows. To remove them completely, select Copy to another location option and select a cell for the Copy to range field.

How to remove duplicate rows in excel

Clicking the OK button populates a list of unique values in the target location.

How to remove duplicate rows in excel

Now you can use formulas with unique values to consolidate data from your data set. If you want to use formulas to do this instead, please see Pivot Table Alternative Using Formulas. Alternatively, you can also use Excel’s Consolidate feature. Next, we’re going to take a look at how you can use the Consolidate feature.

How to consolidate duplicate values

Consolidation can be done in many ways in Excel. Formulas like SUMIFS or COUNTIFS are frequently used to do this. You can also use an advanced tool like Pivot Table or Power Pivot for the same effect. As another alternative, Excel has a relatively less-known tool which is designed specifically for this job. Let’s see how you can use the Consolidate Tool.

This time, select a suitable cell where the consolidated data is to be populated.

How to remove duplicate rows in excel

Once again, activate the Data tab in the Ribbon. Click on the Consolidate icon under the Data Tools section to open the Consolidate dialog.

In the Consolidate dialog, you can

  • choose a function type which determines the consolidation method,
  • add references to be consolidated
  • determine label location
  • and create links to the source data if it is located in an external target.

Start by determining a function type. Next step is adding the data into references. Activate the Reference field, and select the range that contains the data.

How to remove duplicate rows in excel

Click the Add button to add the reference into the All references box. Check either Top row or Left column options depending on how your data is structured. We checked both options in this example, because both the top row of our data, 2 nd row, and left column, column B, contain labels.

How to remove duplicate rows in excel

Click OK when you are done. You will see that the consolidated values are populated starting from the selected cell.

How to remove duplicate rows in excel

That’s all! Note that your settings will be saved into the file, so you can repeat this when the data is updated.

Step 1: Select the range of cells that you want to remove duplicates from. A range is a “rectangular” block of cells containing data to be manipulated.

Just like in word, in most cases, the user must select or highlight the cells that he or she wants to manipulate. For Excel, the same operation of selecting text must be done.

To highlight a range of cells simply press shift at the cell where you want to begin your cell selection and then continuously press the arrow keys to control the size of the range. The range of cells is thus that block on which manipulation of data is to be carried out on.

How to remove duplicate rows in excel

Step 2: After highlighting the block of cells to manipulate, select the “Data “tab on MS excel ribbon. Navigate or look for the “Data tools” group commonly located at the rightmost part of the ribbon. Then select “Remove Duplicates” option.

Its icon is represented by two columns that have an arrow between them. Press or click on the “remove Duplicates” command button on the “Data Tools” group. A group in the ribbon is usually a collection of commands that are almost related and have a common title. Clicking on the “remove duplicates” options will give one an opportunity to pick the column or columns to be checked for duplicates.

How to remove duplicate rows in excel

Step 3: A dialog with the title Remove Duplicates will appear. Choose the checkbox that lists the column you intend to use to check for duplicates.

You choose to check for duplicates in all columns by having the “Select All” button option but for our case, since we are removing duplicates based on one single column we just need to choose the checkbox for the specific column that we need to use.

Step 4: Click “OK” button after checking the column of your choice from the checkboxes listed in the dialog box.

The okay button is used to confirm your submission on the choice of columns you want to check for duplicates from.

Its however important to note that before performing operations involving deletion of data from your worksheets, always have a copy of your workbook to work as security in case of accidental erasers that may arise when some operations are carried out.

Step 5: A dialog showing how many duplicate entries were deleted and how many remains are displayed. This dialog has a single “OK” button. Click on the “OK” button to close the dialog and resume with normal worksheet operations that you might wish.

Need to know how to delete duplicates in Excel 2013? Well, thank you Microsoft! I bet a million hours have been spent over the past 10 years where people did nothing but remove duplicate rows from a Spreadsheet. Microsoft has just improved the GDP of the US by .0001% with a single Feature.

The Remove Duplicates Wizard in Excel 2013 even allows you to define multiple cells to use for the duplicates matching. You don’t have to do concatenations or other gymnastics. They nailed it perfectly! Excel has gotten really good at figuring out if you’ve got a Table or if you’re just doodling on your Spreadsheet. The duplicates wizard works on tables, which can be almost any contiguous grouping of cells where your data is organized into columns and rows.

To tell you how fast this is, I had a table with around 60k rows in it. Deleting the 52,000 duplicates took less than a second. I am amazed.

How to Delete Duplicates in Excel 2013

How to remove duplicate rows in excel

  1. On the Ribbon, go to the Data Tab
  2. Click the ‘Remove Duplicates’ icon
  3. When the Remove Duplicates wizard pops up, all of your columns are selected by default. This is usually just fine, but if you only want to check for dupes based on specific columns, then just uncheck the ones you don’t want to use.
  4. Tell the wizard whether your table has a header row or not by checking or unchecking the ‘My data has headers’ box
  5. Click Ok and the duplicates are gone!

It is sad that this feature makes me so happy, but there you go, I’m a nerd. Hope this helps someone.

This page describes how to find duplicate rows in Excel. If you want to identify duplicate cells (rather than entire rows of data), you may find the Excel Duplicate Cells page more straightforward.

Note also that this page simply shows how to find the duplicated rows in a spreadsheet.

If you want to remove the repeated occurrences (but not the first occurrence) of a row in your spreadsheet, methods of doing this are described in the Remove Duplicate Rows page.

How to remove duplicate rows in excel

In order to illustrate how to find duplicate rows in an Excel spreadsheet, we will use the above example spreadsheet on the right , which has data spanning three columns.

The first step of finding the duplicate rows is to combine the contents of each column into a single column. We will then find the duplicate values within the single combined column using two different methods.

Step 1: Combine Columns

In order to concatenate the data from columns A – C of the example spreadsheet, we use the & operator in column D of the spreadsheet. The formula to be entered into cell D2 is:

Copying this formula down all rows gives the following spreadsheet:

How to remove duplicate rows in excel

Step 2: Highlight Duplicate Rows

Once columns A-C are concatenated into column D, we need to highlight the duplicate cells in column D. This can be done either by using Conditional Formatting or using the Excel Countif Function.

Highlight Duplicate Rows Using Conditional Formatting

The easiest way to highlight the duplicates in column D of the above spreadsheet is to use Conditional Formatting.

How to remove duplicate rows in excel

    Highlight the range of cells to be formatted (cells D2-D10 in the example spreadsheet).

Select the Excel Conditional Formatting drop-down menu from the Home tab at the top of your Excel workbook.

Within this menu:

    Select the Highlight Cells Rules option and from the secondary menu that appears, select the Duplicate Values. option.

A ‘Duplicate Values’ dialog box will pop up. Ensure that the drop down menu in the left hand side of this dialog box shows the value ‘Duplicate’.

How to remove duplicate rows in excel

The resulting spreadsheet, with the duplicates highlighted, is shown below:

How to remove duplicate rows in excel

Highlight Duplicate Rows Using The Countif Function

Warning: This method will only work if the contents of your cells are less than 256 characters in length, as Excel functions cannot handle text strings that are longer than this.

The conditional formatting method described above highlights all rows that occur more than once in the example spreadsheet.

However, sometimes you want to highlight the second (but not the first) occurrence of any duplicate rows. This can be done using the Excel Countif Function, as shown in the example spreadsheet below:

How to remove duplicate rows in excel

Using the above example spreadsheet, enter the following Countif function cell E2:

Note that this function uses a combination of Absolute and Relative Cell References. Due to this combination of reference styles, as the formula is copied down column E, it becomes,

=COUNTIF( D$2:D3, D3 )
=COUNTIF( D$2:D4, D4 )
=COUNTIF( D$2:D5, D5 )
etc.

Therefore, the formula in cell E4 returns the value 1 for the first occurrence of the combined text string “LauraCARTER#31032”, but the formula in cell E7 returns the value 2 as it detects the second occurence of this text string.

Microsoft has made it quicker to remove duplicate rows in Excel 2010, all it takes now is two simple steps. The best part is that you don’t need to select any specific row before removing the duplicate rows, the build-in Remove Duplicate feature takes care of it.

For demonstration purpose I created a quick data table, note that rows 2 and 5 have same values. Head over to the Data tab and click Remove Duplicate button. This will open a dialog window where you can select the columns from which you want to delete the duplicate values.

If you want to get rid of all duplicate columns in an excel spreadsheet, click Select All, so that all columns are selected and then click OK.

How to remove duplicate rows in excel

After the process is complete, you will be shown a notification window with the results. Note in the screenshot below that the duplicate in row 5 is removed.

How to remove duplicate rows in excel

This guide only covers method to remove duplicate rows, if you are looking to find and remove duplicate values in the whole spreadsheet, visit the guide here.

10 Comments

I don’t understand why you’d ever want to do this…

Because you are presented with a 5000 name mailing list in Excel, that has been appended to by various people over multiple years, and you want to trim it down to just the unique entries. That’s one.

Thank you, but my job requires me to remove BOTH, the duplicate field and the field it repeats. Is there a way to do this without manually scrolling through the spreadsheet and going through each row of records one-by-one?

I have the same problem lisa did you ever find out what is the way?

Hi Ekat,
Yes, I have! While I have found the Remove Duplicates feature to be very helpful for most of my tasks, I found another Excel command that points out duplicates for me, and then I manually remove them. Remember, the cells must have the same exact information, though. So if you have one field that says, “Louie G” and another that says “Louis G” Excel won’t see them as duplicates.
Keeping that in mind:
Go to the HOME tab
About 1/3 of the way in from the right, choose the down arrow from CONDITIONAL FORMATTING
Then choose the first option down: HIGHLIGHT CELLS RULES
That opens another window from the command. Choose the one on the bottom that says DUPLICATE VALUES.
I usually keep the default values: light red fill with a dark red text
Once I select those items, all the duplicates appear in a light red fill with a dark red text. Then I simply delete them.

If I find they are scattered all over the place, I do one more step before deleting.
I use the SORT function (DATA, SORT) and then choose the column header that has the duplicate fields. There is the option to sort on values. I use the drop down menu and then choose cell color or font color. Then the option for cell color (or font color) appears on the right of that menu screen.

All the common colors will be grouped together, where you can select and delete.

Hope that helps! : )

Thanks!! You Rock!

This is exactly what I need to know how to do, but on a Mac using excel. No one seems to know how to do it on a Mac! Any tips? Thanks!

Leave a Reply Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

To remove duplicate rows we can use the standard Excel functionality Remove duplicates. We can remove or replace duplicates in one column or in several columns.

This step by step tutorial will assist all levels of Excel users in learning how to remove duplicate rows.

Remove Duplicate Rows One Column

If we want to remove duplicate rows in a single column, we need to select the column and choose Remove duplicates button in the Data tab.

How to remove duplicate rows in excelFigure 1. Data for removing duplicates example

Now, we need to go to the Data tab and click on the Remove duplicates icon:

How to remove duplicate rows in excelFigure 2. Choose the Remove duplicates option

Then, we need to select the column in the selected range where we want to remove duplicate rows . As we selected only one column, it will be automatically selected:

How to remove duplicate rows in excelFigure 3. Choose the column for removing duplicate rows

We have the name John repeated in rows 3, 6 and 10, the function will keep the first value in row 3 and remove duplicate rows 6 and 10:

How to remove duplicate rows in excelFigure 4. The result of the Remove duplicates option

Remove Duplicate Rows Multiple Columns

We can also delete duplicate rows, comparing values in two or more columns. In this example, we have names in column B and surnames in column C. We want to delete all rows which have the same values in both columns.

How to remove duplicate rows in excelFigure 5. Data for removing duplicates example

First, we need to select both columns and go to the Data tab and click on the Remove duplicates icon:

How to remove duplicate rows in excelFigure 6. Choose the Remove duplicates option

Now, we need to select columns in the selected range where we want to remove duplicate rows . As we want to compare both name and surname, we will select columns B and C:

How to remove duplicate rows in excelFigure 7. Choose columns for removing duplicate rows

How to remove duplicate rows in excelFigure 8. The result of the Remove duplicates option

Name and surname John Jackson appears in rows 6 and 10, so row 6 is kept, while row 10 is removed.

Instant Connection to an Excel Expert

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.