![Mac Mac](http://www.online-tech-tips.com/wp-content/uploads/2010/04/ClickonRemoveDuplicatesButton.png)
May 16, 2014 - I've taken a quick screenshot for this. I've selected my data (1). You won't find 'remove duplicates' in the Data menu (from the menu bar). This example teaches you how to remove duplicates in Excel.Go here to only find duplicates. Click any single cell inside the data set. On the Data tab, in the Data Tools group, click Remove Duplicates.
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). Note: You cannot conditionally format fields in the Values area of a PivotTable report by unique or duplicate values. Quick formatting 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.
Advanced formatting 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. Need more help?
You can always ask an expert in the, get support in the, or suggest a new feature or improvement on.
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.
Also see our article 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.
Open your spreadsheet on the page you need to sort. Press Ctrl + A to select all. Click Conditional Formatting.
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. 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. Open your spreadsheet on the page you need to sort.
Press Ctrl + A to select all. Click the Data tab and select Remove Duplicates. Select or deselect ‘My data has headers’ depending on whether yours has them or not. Click OK to remove the duplicates. There is also another way to remove duplicates in Excel using advanced filters. Open your spreadsheet on the page you need to sort.
Drag the mouse to include all the cells you want to filter. Click the Data tab and select Advanced. 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!