How to remove duplicates in excel

Find and remove duplicates

Sometimes duplicate data is useful, sometimes it just makes it harder to understand your data. Use conditional formatting to find and highlight duplicate data. That way you can review the duplicates and decide if you want to remove them.

Select the cells you want to check for duplicates.

Note: Excel can’t highlight duplicates in the Values area of a PivotTable report.

Click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

Remove duplicate values

When you use the Remove Duplicates feature, the duplicate data will be permanently deleted. Before you delete the duplicates, it’s a good idea to copy the original data to another worksheet so you don’t accidentally lose any information.

Select the range of cells that has duplicate values you want to remove.

Tip: Remove any outlines or subtotals from your data before trying to remove duplicates.

Click Data > Remove Duplicates, and then Under Columns, check or uncheck the columns where you want to remove the duplicates.

For example, in this worksheet, the January column has price information I want to keep.

So, I unchecked January in the Remove Duplicates box.

Find and remove duplicates

Sometimes duplicate data is useful, sometimes it just makes it harder to understand your data. Use conditional formatting to find and highlight duplicate data. That way you can review the duplicates and decide if you want to remove them.

Select the cells you want to check for duplicates.

Note: Excel can’t highlight duplicates in the Values area of a PivotTable report.

Click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

Remove duplicate values

When you use the Remove Duplicates feature, the duplicate data will be permanently deleted. Before you delete the duplicates, it’s a good idea to copy the original data to another worksheet so you don’t accidentally lose any information.

Select the range of cells that has duplicate values you want to remove.

Tip: Remove any outlines or subtotals from your data before trying to remove duplicates.

Click Data > Remove Duplicates, and then Under Columns, check or uncheck the columns where you want to remove the duplicates.

For example, in this worksheet, the January column has price information I want to keep.

So, I unchecked January in the Remove Duplicates box.

Filter for unique values or remove duplicate values

In Excel, there are several ways to filter for unique values—or remove duplicate values:

To filter for unique values, click Data > Sort & Filter > Advanced.

To remove duplicate values, click Data > Data Tools > Remove Duplicates.

To highlight unique or duplicate values, use the Conditional Formatting command in the Style group on the Home tab.

Filtering for unique values and removing duplicate values are two similar tasks, since the objective is to present a list of unique values. There is a critical difference, however: When you filter for unique values, the duplicate values are only hidden temporarily. However, removing duplicate values means that you are permanently deleting duplicate values.

A duplicate value is one in which all values in at least one row are identical to all of the values in another row. A comparison of duplicate values depends on the what appears in the cell—not the underlying value stored in the cell. For example, if you have the same date value in different cells, one formatted as «3/8/2006» and the other as «Mar 8, 2006», the values are unique.

Check before removing duplicates: Before removing duplicate values, it’s a good idea to first try to filter on—or conditionally format on—unique values to confirm that you achieve the results you expect.

Follow these steps:

Select the range of cells, or ensure that the active cell is in a table.

Click Data > Advanced (in the Sort & Filter group).

In the Advanced Filter popup box, do one of the following:

To filter the range of cells or table in place:

Click Filter the list, in-place.

To copy the results of the filter to another location:

Click Copy to another location.

In the Copy to box, enter a cell reference.

Alternatively, click Collapse Dialog to temporarily hide the popup window, select a cell on the worksheet, and then click Expand .

Check the Unique records only, then click OK.

The unique values from the range will copy to the new location.

When you remove duplicate values, the only effect is on the values in the range of cells or table. Other values outside the range of cells or table will not change or move. When duplicates are removed, the first occurrence of the value in the list is kept, but other identical values are deleted.

Because you are permanently deleting data, it’s a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values.

Follow these steps:

Select the range of cells, or ensure that the active cell is in a table.

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

Do one or more of the following:

Under Columns, select one or more columns.

To quickly select all columns, click Select All.

To quickly clear all columns, click Unselect All.

If the range of cells or table contains many columns and you want to only select a few columns, you may find it easier to click Unselect All, and then under Columns, select those columns.

Note: Data will be removed from all columns, even if you don’t select all the columns at this step. For example, if you select Column1 and Column2, but not Column3, then the “key” used to find duplicates is the value of BOTH Column1 & Column2. If a duplicate is found in those columns, then the entire row will be removed, including other columns in the table or range.

Click OK, and a message will appear to indicate how many duplicate values were removed, or how many unique values remain. Click OK to dismiss this message.

Undo the change by click Undo (or pressing Ctrl+Z on the keyboard).

You cannot remove duplicate values from outline data that is outlined or that has subtotals. To remove duplicates, you must remove both the outline and the subtotals. For more information, see Outline a list of data in a worksheet and Remove subtotals.

Note: You cannot conditionally format fields in the Values area of a PivotTable report by unique or duplicate values.

Follow these steps:

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Style group, click the small arrow for Conditional Formatting, and then click Highlight Cells Rules, and select Duplicate Values.

Enter the values that you want to use, and then choose a format.

Follow these steps:

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Styles group, click the arrow for Conditional Formatting, and then click Manage Rules to display the Conditional Formatting Rules Manager popup window.

Do one of the following:

To add a conditional format, click New Rule to display the New Formatting Rule popup window.

To change a conditional format, begin by ensuring that the appropriate worksheet or table has been chosen in the Show formatting rules for list. If necessary, choose another range of cells by clicking Collapse button in the Applies to popup window temporarily hide it. Choose a new range of cells on the worksheet, then expand the popup window again . Select the rule, and then click Edit rule to display the Edit Formatting Rule popup window.

Under Select a Rule Type, click Format only unique or duplicate values.

In the Format all list of Edit the Rule Description, choose either unique or duplicate.

Click Format to display the Format Cells popup window.

Select the number, font, border, or fill format that you want to apply when the cell value satisfies the condition, and then click OK. You can choose more than one format. The formats that you select are displayed in the Preview panel.

In Excel for the web, you can remove duplicate values.

Remove duplicate values

When you remove duplicate values, the only effect is on the values in the range of cells or table. Other values outside the range of cells or table will not change or move. When duplicates are removed, the first occurrence of the value in the list is kept, but other identical values are deleted.

Important: You can always click Undo to get back your data after you have removed the duplicates. That being said, it’s a good idea to copy the original range of cells or table to another worksheet or workbook before removing duplicate values.

Follow these steps:

Select the range of cells, or ensure that the active cell is in a table.

In the Remove Duplicates dialog box, unselect any columns where you don’t want to remove duplicate values.

Note: Data will be removed from all columns, even if you don’t select all the columns at this step. For example, if you select Column1 and Column2, but not Column3, then the “key” used to find duplicates is the value of BOTH Column1 & Column2. If a duplicate is found in Column1 and Column2, then the entire row will be removed, including data from Column3.

Click OK, and a message will appear to indicate how many duplicate values were removed. Click OK to dismiss this message.

Note: If you want to get back your data, simply click Undo (or press Ctrl+Z on the keyboard).

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community.

Filter for or remove duplicate values

Filtering for unique values and removing duplicate values are two closely related tasks because the displayed results are the same — a list of unique values. The difference, however, is important. When you filter for unique values, you temporarily hide duplicate values, but when you remove duplicate values, you permanently delete duplicate values. A duplicate value is one where all values in the row are an exact match of all values in another row. Duplicate values are determined by the value displayed in the cell and not necessarily the value stored in the cell. For example, if you have the same date value in different cells, one formatted as «12/8/2017» and the other as «Dec 8, 2017», the values are unique. It’s a good idea to filter for or conditionally format unique values first to confirm that the results are what you want before removing duplicate values.

Note: If the formula in the cells is different, but the values are the same, they are considered duplicates. For example, if cell A1 contains the formula =2-1 and cell A2 contains the formula =3-2, as long as the value is formatted the same, they are considered to be duplicate values. If the same value is formatted using different number formats, they are not considered duplicates. For example, if the value in cell A1 is formatted as 1.00 and the value in cell A2 is formatted as 1, they are not considered duplicates.

Filter for unique values

Select the range of cells, or make sure that the active cell is in a table.

On the Data tab, in the Sort & Filter group, click Advanced.

Do one of the following:

Filter the range of cells or table in place

Select the range of cells, and then click Filter the list, in-place.

Copy the results of the filter to another location

Select the range of cells, click Copy to another location, and then in the Copy to box, enter a cell reference.

Note: If you copy the results of the filter to another location, the unique values from the selected range are copied to the new location. The original data is not affected.

Select the Unique records only check box, and then click OK.

More options

When you remove duplicate values, only the values in the selected range of cells or table are affected. Any other values outside the range of cells or table are not altered or moved. Because you are permanently deleting data, it’s a good idea to copy the original range of cells or table to another sheet or workbook before removing duplicate values.

Note: You cannot remove duplicate values from data that is outlined or that has subtotals. To remove duplicates, you must remove both the outline and the subtotals first.

Select the range of cells, or make sure that the active cell is in a table.

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

Select one or more of the check boxes, which refer to columns in the table, and then click Remove Duplicates.

Tip: If the range of cells or table contains many columns and you want to only select a few columns, clear the Select All check box and select only the columns that you want.

You can apply conditional formatting to unique or duplicate values so that they can be seen easily. Color coding duplicate data, for example, can help you locate and, if necessary, remove that data.

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Styles group, click Conditional Formatting, point to Highlight Cells Rules, and then click Duplicate Values.

Select the options that you want in the New Formatting Rule dialog box, and then click OK.

You can create a rule to color code unique or duplicate data in your sheet. This is especially helpful when your data includes multiple sets of duplicate values.

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Styles group, click Conditional Formatting, and then click New Rule.

In the Style list, choose Classic, and then in the Format only top or bottom ranked values list, choose Format only unique or duplicate values.

In the values in the selected range list, choose either unique or duplicate.

In the Format with list, select an option for how you want the unique or duplicate values to be formatted.

You can edit an existing rule and modify it to apply conditional formatting to unique or duplicate data.

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, in the Styles group, click Conditional Formatting, and then click Manage Rules.

Make sure that the appropriate sheet or table is selected in the Show formatting rules for list.

Select the rule, and then click Edit Rule.

Select the options that you want, and then click OK.

Filter for unique values

Select the range of cells, or make sure that the active cell is in a table.

On the Data tab, under Sort & Filter, click the arrow next to Filter, and then click Advanced Filter.

Do one of the following:

Filter the range of cells or table in place

Select the range of cells, and then click Filter the list, in-place.

Copy the results of the filter to another location

Select the range of cells, click Copy to another location, and then in the Copy to box, enter a cell reference.

Note: If you copy the results of the filter to another location, the unique values from the selected range are copied to the new location. The original data is not affected.

Select the Unique records only check box, and then click OK.

More options

When you remove duplicate values, only the values in the selected range of cells or table are affected. Any other values outside the range of cells or table are not altered or moved. Because you are permanently deleting data, it’s a good idea to copy the original range of cells or table to another sheet or workbook before removing duplicate values.

Note: You cannot remove duplicate values from data that is outlined or that has subtotals. To remove duplicates, you must remove both the outline and the subtotals first.

Select the range of cells, or make sure that the active cell is in a table.

On the Data tab, under Tools, click Remove Duplicates.

Select one or more of the check boxes, which refer to columns in the table, and then click Remove Duplicates.

Excel displays either a message indicating how many duplicate values were removed and how many unique values remain, or a message indicating that no duplicate values were removed.

Tip: If the range of cells or table contains many columns and you want to only select a few columns, clear the Select All check box and select only the columns that you want.

You can apply conditional formatting to unique or duplicate values so that they can be seen easily. Color coding duplicate data, for example, can help you locate and, if necessary, remove that data.

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, under Format, click the arrow next to Conditional Formatting, point to Highlight Cells Rules, and then click Duplicate Values.

Select the options that you want, and then click OK.

You can create a rule to color code unique or duplicate data in your sheet. This is especially helpful when your data includes multiple sets of duplicate values.

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, under Format, click the arrow next to Conditional Formatting, and then click New Rule.

On the Style pop-up menu, click Classic, and then on the Format only top or bottom ranked values pop-up menu, click Format only unique or duplicate values.

On the values in the selected range pop-up menu, click either unique or duplicate.

On the Format with pop-up menu, select an option for how you want the unique or duplicate values to be formatted.

You can edit an existing rule and modify it to apply conditional formatting to unique or duplicate data.

Select one or more cells in a range, table, or PivotTable report.

On the Home tab, under Format, click the arrow next to Conditional Formatting, and then click Manage Rules.

Make sure that the appropriate sheet or table is selected on the Show formatting rules for pop-up menu.

Select the rule, and then click Edit Rule.

Select the options that you want, and then click OK.

Find and remove duplicates

Sometimes duplicate data is useful, sometimes it just makes it harder to understand your data. Use conditional formatting to find and highlight duplicate data. That way you can review the duplicates and decide if you want to remove them.

Select the cells you want to check for duplicates.

Note: Excel can’t highlight duplicates in the Values area of a PivotTable report.

Click Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values.

Remove duplicate values

When you use the Remove Duplicates feature, the duplicate data will be permanently deleted. Before you delete the duplicates, it’s a good idea to copy the original data to another worksheet so you don’t accidentally lose any information.

Select the range of cells that has duplicate values you want to remove.

Tip: Remove any outlines or subtotals from your data before trying to remove duplicates.

Click Data > Remove Duplicates, and then Under Columns, check or uncheck the columns where you want to remove the duplicates.

For example, in this worksheet, the January column has price information I want to keep.

So, I unchecked January in the Remove Duplicates box.