Duplicate values are determined by the value displayed in the cell and not necessarily the value stored in the cell. 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.
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. Select the range of cells, click Copy to another location , and then in the Copy to box, enter a cell reference. The original data is not affected.
Select the Unique records only check box, and then click OK. 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. To remove duplicates, you must remove both the outline and the subtotals first.
Select one or more of the check boxes, which refer to columns in the table, and then click Remove Duplicates. 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. 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.
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. Make sure that the appropriate sheet or table is selected in the Show formatting rules for list. On the Data tab, under Tools , 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.
You'd probably not want to use this method to delete duplicate values from this particular sheet because of the displaced value. Post as a guest Name. And if you want to perform some other action , say, highlight duplicate rows without deleting them, or copy duplicate values to another location, select the corresponding option from the drop-down list:. Susan Sales Harkins is an IT consultant, specializing in desktop solutions. The best spent money on software I've ever spent! Jun 6, PM.
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. Make sure that the appropriate sheet or table is selected on the Show formatting rules for pop-up menu. Sort a list of data. When you click OK you should find that you are left with one copy of any duplicate rows in the list.
In Excel the duplicated data isn't truly removed from the list, rather it is filtered out by hiding the rows containing duplicate values.
If you look at the row numbers on the left hand side of the diagram above you'll notice that number 7 and number 11 are missing. Because the duplicates aren't actually removed from the list, you can easily bring them back again. To do this:. If you want to truly delete the duplicate values in Excel , the easiest way to do it is to choose the option Copy to another location within the Advanced Filter dialog box.
This will copy the unique values to a different set of cells. You can then delete the original list completely and then optionally copy the unique values back into the cells that were previously occupied by the original list. Deleting duplicates from the list is rather awkward in Excel Read the next part of this blog to see how easy the process is in Excel and , or visit our Excel training page.
Outstanding course feedback Average score 9. Thanks for looking at our blogs!