How to Remove Duplicate Rows in Excel:
Duplicate values in your Excel worksheet can mess up your data. Unless duplicates are intentional, they can lead to inaccuracy and skewed reporting.
In this tutorial, we will show you how to find and remove duplicates in your Excel document.
How to find duplicate row or data
It is necessary to first check which rows (or columns) contain the same information. So before we show you How to Remove Duplicates in ExcelIn this article, let us walk you through the process of checking your sheet for duplicate data.
Method 1: Search the entire worksheet
Excel has one conditional formatting tool Which helps to identify, visualize and draw conclusions from the 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 select all icon in the upper-left corner of the table to select the entire worksheet.
- go to House tab and click conditional formatting drop-down button.
- move your cursor up highlight cell rules choose more duplicate value.
- Make sure the first drop-down box reads “Duplicate.” You can also choose your preferred highlight color in the second drop-down menu.
- Choose Okay To move forward.
Excel will immediately highlight rows and columns with duplicate values.
Method 2: Combine Rows
This method is perfect for finding rows with duplicate values in all columns or cells. First, you’ll need to use Excel’s “Concatenate” function to concatenate the contents of each row. Then, select the column where you want to store the combined values and follow the steps below. We recommend concatenating the values in a column next to the last value in the first row.
- type or paste =CONCAT( In Cells, select the first cell on the row, type a column (:), and select the last cell on the row. Afterwards, close the formula with parentheses and press recorded.
In our sample worksheet (see picture below), the first and last cells in the first row refer to A2 and D2, respectively. Therefore, the formula will be of the form: =CONCAT(A2:D2).
Remember, cell references will vary depending on the number of rows and columns on the table.
- Copy the formula down the column until you reach the last row. To do this, select the cell with the formula, move your mouse cursor to the lower-left corner of the cell, hold down fill handle icon (ie the plus icon), and drag it to the bottom of the column.
- Select the entire column—click the letter at the top of the column, or click and hold a cell in the column Control + Space.
- go to House tab and select conditional formatting.
- turn your mouse cursor on highlight cell rules choose more duplicate value.
- Choose Okay To move forward.
Excel will highlight the column containing the duplicate values. This tells you about the cells in that particular row that have duplicate values as the second row on the worksheet.
If you look closely at the image above, you’ll notice that the conditional formatting tool didn’t highlight row 4 and line 13. Both rows have duplicate values Name, Score, and Eid Columns, but different values day column.
Only 3 out of 4 columns in both rows have duplicate information. This explains why the conditional formatting tool did not highlight the combined or combined values for both rows. Both rows (row 4 and row 13) are unique because the “Day” column contains specific information.
how to remove duplicate rows in excel
You’ve got multiple rows with duplicate information in your Excel worksheet. Let us 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 make sure your Excel worksheets have clean data. 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 data tab and click remove duplicates icon in the “Data Tools” section.
If your PC screen is small or the Excel window is small, click data tools drop-down button and select remove duplicates.
- Go to the Columns section and select All Columns. If your table has a header, check the box that reads “My data contains headers.” This will deselect the header row or the first row of the sheet. Click Okay To move forward.
quick Tip: To make the first row of an Excel worksheet a header, go to opinion pick up the tab freeze panes, and select freeze top row.
- Excel will display a prompt informing you about the total number of duplicate values found and removed from the sheet. Click Okay To return to the worksheet.
2. Use the Advanced Filter Tool
“Advanced Filters” is another great tool that helps you Clear Your Data in Excel. Tools let you view, edit, Group and sort the 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 whole table.
- go to data tab and select advanced In the “Sort and Filter” section.
- check unique records only box and click Okay.
If there are multiple rows in a table or worksheet with the same information or value, Excel will delete all except the first occurrence of a duplicate.
pay attention: The Advanced Filter Tool automatically treats the first line as a header. This means that the tool will not remove the first row, even if it contains duplicate information. For example, in the table below, running the Advanced Filter tool’s “Unique records only” feature did not remove the first and last rows—even though they both had duplicate values in all columns.
Therefore, if your Excel worksheet or table has a header, it is best to use the “Remove Duplicates” tool to eliminate duplicate rows.
quick Tip: Accidentally deleted duplicate rows or values? press Control + With To revert the change and get back the duplicate data.
Removing Duplicates in Excel: Limits
We should mention that you cannot remove duplicate rows or values from a worksheet. underlined or grouped data. then you Grouped rows and columns in your Excel worksheet, probably in sum and subtotal, you need to ungroup the data before checking for duplicates. refer to This official document from Microsoft To learn more about removing duplicates and filtering unique values in Excel.