Remove duplicate trong excel 365
Find and remove duplicates Show Excel for Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Excel Starter 2010 More...Less 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.
Remove duplicate valuesWhen 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.
Need more help?
Need more help?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. 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:
Select the Unique records only check box, and then click OK. More optionsWhen 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.
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.
You can edit an existing rule and modify it to apply conditional formatting to unique or duplicate data.
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:
Select the Unique records only check box, and then click OK. More optionsWhen 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.
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.
You can edit an existing rule and modify it to apply conditional formatting to unique or duplicate data.
See alsoSort a list of data Filter a list of data Filter by font color, cell color, or icon sets Find and replace text or numbers |