Eliminate duplications in Microsoft Excel to keep your spreadsheets looking good and working better. There are a few ways to dedupe Excel data, but none of them are hard to do. How To: Merge two list tables and delete duplicates in Excel How To: Extract unique records with an array formula in Excel How To: Create a list of items with a 2-array formula in Excel How To: Extract records from a table to a column in MS Excel How To: Create a column of unique values in an Excel LU table.

-->

You can work with duplicate sets of values through transformations that can remove duplicates from your data or filter your data to show duplicates only, so you can focus on them.

Warning

Power Query is case-sensitive. When working with duplicate values, Power Query considers the case of the text, which might lead to undesired results. As a workaround, users can apply an uppercase or lowercase transform prior to removing duplicates.

For this article, the examples use the following table with id, Category, and Total columns.

Remove duplicates

One of the operations that you can perform is to remove duplicate values from your table.

Select the columns that contain duplicate values.
Go to the Home tab.
In the Reduce rows group, select Remove rows.
From the drop-down menu, select Remove duplicates.

Remove duplicates in excel keep first value

Warning

There's no guarantee that the first instance in a set of duplicates will be chosen when duplicates are removed.

Remove duplicates from multiple columns

In this example, you want to identify and remove the duplicates by using all of the columns from your table.

You have four rows that are duplicates. Your goal is to remove those duplicate rows so there are only unique rows in your table. Select all columns from your table, and then select Remove duplicates.

The result of that operation will give you the table that you're looking for.

Note

This operation can also be performed with a subset of columns.

Remove duplicates from a single column

In this example, you want to identify and remove the duplicates by using only the Category column from your table.

You want to remove those duplicates and only keep unique values. To remove duplicates from the Category column, select it, and then select Remove duplicates.

The result of that operation will give you the table that you're looking for.

Keep duplicates

Another operation you can perform with duplicates is to keep only the duplicates found in your table.

Select the columns that contain duplicate values.
Go to the Home tab.
In the Reduce rows group, select Keep rows.
From the drop-down menu, select Keep duplicates.

Keep duplicates from multiple columns

In this example, you want to identify and keep the duplicates by using all of the columns from your table.

You have four rows that are duplicates. Your goal in this example is to keep only the rows that are duplicated in your table. Select all the columns in your table, and then select Keep duplicates.

The result of that operation will give you the table that you're looking for.

Keep duplicates from a single column

In this example, you want to identify and keep the duplicates by using only the id column from your table.

In this example, you have multiple duplicates and you want to keep only those duplicates from your table. To keep duplicates from the id column, select the id column, and then select Keep duplicates. Blackberry desktop manager for pc windows 7.

The result of that operation will give you the table that you're looking for.

Getting Started

Add Duplicate Values In Excel

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.

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

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.

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.

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

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.

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.

Remove Duplicates In Excel Keep First Value

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…”

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

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.

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.