(Duplicate)Excel工作表中的(Excel)重复值可能会弄乱您的数据。除非故意重复，否则它们可能会导致不准确和歪斜的报告。
在本教程中，我们将向您展示如何在Excel文档中查找和删除重复项。
如何查找重复的行或数据
必须首先检查哪些行（或列）具有相同的信息。因此，在我们向您展示如何在 Excel 中删除重复项之前，让我们带您完成检查工作表中是否存在重复数据的过程。
方法 1：搜索整个工作表(Method 1: Search Entire Worksheet)
Excel 有一个条件格式工具(Conditional Formatting tool)，可帮助识别、可视化数据并从数据中得出结论。以下是如何使用该工具突出显示Excel文档中的重复值。
- 按Control + A选择您的列表或表格。或者，单击表格左上角的全选图标以选择整个工作表。(Select All)
- 转到主页(Home)选项卡，然后单击条件格式(Conditional Formatting)下拉按钮。
- 将光标移到突出显示单元格规则(Highlight Cell Rules)并选择重复值(Duplicate Values)。
- 确保(Make)第一个下拉框显示为“重复”。您还可以在第二个下拉菜单中选择您喜欢的突出显示颜色。
- 选择确定(OK)继续。
Excel将立即突出显示具有重复值的行和列。
方法2：通过组合行(Method 2: By Combining Rows)
此方法非常适合在所有列或单元格中查找具有重复值的行。首先(First)，您需要使用 Excel 的“连接”功能来组合每一行的内容。然后，选择要存储组合值的列，然后按照以下步骤操作。我们建议将第一行最后一个值旁边的列中的值组合起来。
- 在单元格中键入或粘贴=CONCAT(，选择行上的第一个单元格，键入列 ( :)，然后选择行上的最后一个单元格。然后(Afterward)，用括号关闭公式并按Enter。
在我们的示例工作表（见下图）中，第一行的第一个和最后一个单元格分别具有参考 A2 和 D2。因此(Hence)，公式将是这种形式：=CONCAT(A2:D2)。
请记住(Remember)，单元格引用将根据表格上的行数和列数而有所不同。
- 将公式复制到列中，直到到达最后一行。为此，请选择包含公式的单元格，将鼠标光标移动到单元格的左下角，按住填充手柄(Fill handle)图标（即加号图标），然后将其向下拖动到列中。
- 选择整列 — 单击列顶部的字母或单击列中的一个单元格，然后按Control + Space。
- 转到主页(Home)选项卡并选择条件格式(Conditional Formatting)。
- 将鼠标光标悬停在突出单元格规则(Highlight Cell Rules)上，然后选择重复值(Duplicate Values)。
- 选择确定(OK)继续。
Excel将突出显示具有重复值的列。这会告诉您该特定行中具有重复值的单元格作为工作表上的另一行。
如果您仔细查看上图，您会注意到条件格式(Conditional Formatting)工具没有突出显示第 4(Row 4)行和第 13 行(Row 13)。两行在Names、Scores和ID列中都有重复的值，但在Day列中有不同的值。
两行中的 4 列中只有 3 列具有重复信息。这就解释了为什么条件格式(Conditional Formatting)工具没有突出显示两行的连接或组合值。两行（第 4 行(Row 4)和第 13 行(Row 13)）都是唯一的，因为“日”列中有区别信息。
如何删除Excel中的重复行(Duplicate Rows)
您在Excel工作表中发现多行包含重复信息。让我们向您展示如何使用两个Excel工具删除这些重复的行。
1.使用“删除重复”工具(1. Use the “Remove Duplicates” Tool)
该工具只有一项工作：确保您的Excel工作表中有干净的数据。它通过比较工作表中的选定列并删除具有重复值的行来实现这一点。以下是该工具的使用方法：
- 选择表格上的一个单元格，然后按键盘上的Control + A以突出显示表格。(A)
- 转到“数据(Data)”选项卡，然后单击“数据工具”部分中的“删除重复项(Remove Duplicates)”图标。
如果您的 PC 有一个小屏幕或Excel窗口已最小化，请单击数据工具(Data Tools)下拉按钮并选择删除重复(Remove Duplicates)项。
- 浏览列(Columns)部分并选择所有列。如果您的表格有标题，请选中“我的数据有标题”框。这将取消选择标题行或工作表上的第一行。单击“(Click) 确定(OK)”继续。
快速提示：要将(Quick Tip:)Excel工作表的第一行设为标题，请转到“视图”(View)选项卡，选择“冻结窗格(Freeze Panes)”，然后选择“冻结首行”(Freeze Top Row)。
- Excel将显示一个提示，通知您从工作表中找到并删除的重复值总数。单击“(Click) 确定(OK)”返回工作表。
2.使用高级过滤工具(2. Use the Advanced Filter Tool)
“高级过滤器”是另一个出色的工具，可帮助您清理 Excel 中的数据(clean your data in Excel)。该工具可让您查看、编辑、分组和排序工作表上的数据(group and sort data on your worksheet)。请按照以下步骤了解如何使用此工具从Excel工作表中删除重复行。
- 选择(Select)表格上的任何单元格，然后按Control + A突出显示整个表格。
- 转到“数据(Data)”选项卡，然后在“排序和过滤”部分中选择“高级”。(Advanced)
- 选中唯一记录(Unique records only)框并单击确定(OK)。
如果表或工作表包含具有相似信息或值的多行，Excel将删除除第一次出现的重复项之外的所有行。
注意：(Note:)高级筛选(Advanced Filter)工具会自动将第一行视为标题。这意味着该工具不会删除第一行，即使它包含重复信息。例如，在下表中，运行高级筛选工具的“仅(Advanced Filter)唯一(Unique)记录”功能不会删除第一行和最后一行，即使它们在所有列中都有重复值。
因此，如果您的Excel工作表或表格有标题，最好使用“删除重复项”工具来消除重复行。
快速提示：(Quick Tip:)意外删除了重复的行或值？按Control + Z恢复更改并取回重复数据。
删除Excel中的重复项：限制
我们应该提到，您不能从包含概述或分组数据(outlined or grouped data)的工作表中删除重复的行或值。因此，如果您将 Excel 工作表中的行和列分组(grouped the rows and columns in your Excel worksheet)，可能是汇总和小计，则必须先取消分组数据，然后才能检查重复项。请参阅Microsoft 的此官方文档，(this official documentation from Microsoft)以了解有关在Excel中删除重复项和过滤唯一值的更多信息。
How to Remove Duplicate Rows in Excel
Duplіcate νalues іn your Excel worksheet can mess up your datа. Unless duplicates are intentionаl, they can cause inaсcuracіes and skewed reporting.
In this tutorial, we’ll show you how to find and remove duplicates in your Excel document.
How to Find Duplicate Row or Data
It’s essential to first check which rows (or columns) have identical information. So before we show you how to remove duplicates in Excel, let’s walk you through the process of checking your sheet for duplicate data.
Method 1: Search Entire Worksheet
Excel has a Conditional Formatting tool that helps to identify, visualize, and draw conclusions from data. Here’s how to use the tool to highlight duplicate values in your Excel document.
- Press Control + A to select your list or table. Alternatively, click the Select All icon at the top-left corner of the table to select the entire worksheet.
- Go to the Home tab and click the Conditional Formatting drop-down button.
- Move your cursor over Highlight Cell Rules and select Duplicate Values.
- Make sure the first drop-down box reads “Duplicate.” You can also select your preferred highlight color in the second drop-down menu.
- Select OK to proceed.
Excel will immediately highlight rows and columns with duplicate values.
Method 2: By Combining Rows
This method is perfect for finding rows with duplicate values across all columns or cells. First, you’ll need to use Excel’s “Concatenate” function to combine the content of each row. Then, select the column where you want the combined values stored and follow the steps below. We recommend combining the values in a column next to the last value on the first row.
- Type or paste =CONCAT( in the cell, select the first cell on row, type a column (:), and select the last cell on the row. Afterward, close the formula with a parenthesis and press Enter.
In our sample worksheet (see image below), the first and last cells on the first row have the reference A2 and D2, respectively. Hence, the formula will be this form: =CONCAT(A2:D2).
Remember, the cell references will vary depending on the number of rows and columns on the table.
- Copy the formula down the column until you get to the last row. To do this, select the cell with the formula, move your mouse cursor to the bottom-left corner of the cell, hold the Fill handle icon (i.e. the plus icon), and drag it down the column.
- Select the entire column—click the letter at the top of the column or click on a cell in the column and press Control + Space.
- Go to the Home tab and select Conditional Formatting.
- Hover your mouse cursor on Highlight Cell Rules and select Duplicate Values.
- Select OK to proceed.
Excel will highlight the column with duplicates values. That tells you to the cells in that particular row that have duplicate values as another row on the worksheet.
If you look closely at the image above, you’ll notice that the Conditional Formatting tool did not highlight Row 4 and Row 13. Both rows have duplicate values in the Names, Scores, and ID columns, but different values in the Day column.
Only 3 out of 4 columns in both rows have duplicate information. That explains why the Conditional Formatting tool didn’t highlight the concatenated or combined values for both rows. Both rows (Row 4 and Row 13) are unique because there’s distinguishing information in the “Day” column.
How to Remove Duplicate Rows in Excel
You’ve found multiple rows containing duplicate information in your Excel worksheet. Let’s show you how to remove these duplicate rows using two Excel tools.
1. Use the “Remove Duplicates” Tool
This tool has only one job: to ensure you have clean data in your Excel worksheet. It achieves this by comparing selected columns in your worksheet and removing rows with duplicate values. Here’s how to use the tool:
- Select a cell on the table and press Control + A on your keyboard to highlight the table.
- Go to the Data tab and click the Remove Duplicates icon in the “Data Tools” section.
If your PC has a small screen or the Excel window is minimized, click the Data Tools drop-down button and select Remove Duplicates.
- Go through the Columns section and select all columns. If your table has a header, check the box that reads “My data has headers.” That’ll deselect the header row or the first row on the sheet. Click OK to proceed.
Quick Tip: To make the first row of an Excel worksheet a header, go to the View tab, select Freeze Panes, and select Freeze Top Row.
- Excel will display a prompt notifying you of the total duplicate values found and removed from the sheet. Click OK to return to the worksheet.
2. Use the Advanced Filter Tool
“Advanced Filter” is another brilliant tool that helps you clean your data in Excel. The tool lets you view, edit, group and sort data on your worksheet. Follow the steps below to learn how to use this tool to remove duplicate rows from your Excel worksheet.
- Select any cell on the table and press Control + A to highlight the entire table.
- Go to the Data tab and select Advanced in the “Sort & Filter” section.
- Check the Unique records only box and click OK.
If the table or worksheet contains multiple rows with similar information or values, Excel will remove all but the first occurrence of the duplicates.
Note: The Advanced Filter tool automatically treats the first row as a header. This means that the tool won’t remove the first row, even if it contains duplicate information. For instance, in the table below, running the “Unique records only” feature of the Advanced Filter tool did not remove the first and last rows—even though they both have duplicate values across all columns.
So, if your Excel worksheet or table has a header, it’s best to use the “Remove Duplicates” tool to eliminate duplicate rows.
Quick Tip: Removed duplicate rows or values by accident? Press Control + Z to revert the change and get back the duplicate data.
Removing Duplicates in Excel: Limitations
We should mention that you cannot remove duplicate rows or values from a worksheet containing outlined or grouped data. So if you grouped the rows and columns in your Excel worksheet, perhaps into Totals and Subtotals, you’ll have to ungroup the data before you can check for duplicates. Refer to this official documentation from Microsoft to learn more about removing duplicates in Excel and filtering unique values.
