Duplicate values in your Excel worksheet can ruin your data. Unless duplicates are intentional, they can cause inaccuracies and skewed reporting.
In this tutorial, we will show you how to find and remove duplicates in your Excel document.
Find duplicate row or data
It is 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 the entire worksheet
Excel has a Conditional Formatting Tool that helps identify, visualize and draw conclusions from data. Here’s how to use the tool to highlight duplicate values in your Excel document.
- Press Check † A to select your list or table. You can also click the . click Select everything icon in the top left corner of the table to select the entire worksheet.
- Go to the House tab and click the Conditional Formatting drop-down button.
- Move your cursor over Highlight cell lines and select Double values†
- Make sure the first drop-down list says ‘Duplicate’. You can also select your desired highlight color from the second drop-down menu.
- Select Okay continue.
Excel immediately highlights rows and columns with duplicate values.
Method 2: By combining rows
This method is perfect for finding rows with duplicate values in all columns or cells. First, you need to use Excel’s “Merge” function to combine the contents of each row. Then select the column where you want to store the combined values and follow the steps below. We recommend combining the values in a column next to the last value in the first row.
- Type or paste =CONKAT( in the cell, select the first cell in the row, type a column (†) and select the last cell in the row. Then close the formula with a parenthesis and press Enter†
In our sample worksheet (see image below), the first and last cells in the first row are referenced A2 and D2 respectively. Therefore, the formula will have this form: =CONCAT(A2:D2)†
Keep in mind that cell references vary depending on the number of rows and columns in the table.
- Copy the formula down the column until you get to the last row. To do this, select the cell containing the formula, move your mouse cursor to the lower left corner of the cell, hold down the Fill handle icon (ie the plus icon) and drag it down the column.
- Select the entire column: click on the letter at the top of the column or click on a cell in the column and press Check † Room†
- Go to the House tab and select Conditional Formatting†
- Move your mouse cursor over Highlight cell lines and select Double values†
- Select Okay continue.
Excel highlights the column with duplicate 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 will notice that the Conditional Formatting tool is not highlighted Row 4 and Row 13† Both rows have duplicate values in the names† Scoresand ID card columns, but different values in the Day column.
Only 3 of the 4 columns in both rows contain 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 is distinguishing information in the ‘Day’ column.
How to Remove Duplicate Rows in Excel
You have found multiple rows of 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 only has one job: to make sure you have clean data in your Excel worksheet. It achieves this by comparing selected columns in your worksheet and removing rows with duplicate values. How to use the tool:
- Select a cell on the table and press Check † A on your keyboard to mark the table.
- Go to the Facts 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 says “My data has headers.” That deselects the header row or the first row on the sheet. Click Okay continue.
Quick Tip: To turn the first row of an Excel worksheet into a header, go to the Vision tab, select Freeze pansand select Freeze top row†
- Excel displays a prompt informing you of the total duplicate values found and removed from the sheet. Click Okay to return to the worksheet.
2. Use the advanced filter tool
“Advanced Filter” is another brilliant tool to help you clean up your data in Excel† The tool allows you to 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 a cell in the table and press Check † A to highlight the entire table.
- Go to the Facts tab and select Advanced in the “Sort and Filter” section.
- Check the Only unique records box and click Okay†
If the table or worksheet contains multiple rows with similar information or values, Excel removes all but the first occurrence of the duplicates.
Remark: The Advanced Filter tool automatically treats the first row as a header. This means that the tool will not delete the first row, even if it contains duplicate information. For example, in the table below, running the ‘Unique records only’ function of the Advanced Filter tool did not delete the first and last rows, even though they both have duplicate values in all columns.
So, if your Excel worksheet or table has a header, it’s best to use the “Remove Duplicates” tool to remove duplicate rows.
Quick Tip: Accidentally deleted duplicate rows or values? Press Check † z to undo the change and get the duplicate data back.
Removing Duplicates in Excel: Limitations
We should mention that you cannot remove duplicate rows or values from a worksheet with: sketched or grouped data† So if you have grouped the rows and columns in your Excel worksheet, for example in Totals and Subtotals, you must ungroup the data before you can check for duplicates. Referring to this official documentation from Microsoft for more information about removing duplicates in Excel and filtering unique values.