How to Remove Duplicate Rows in Excel

But there are a few limitations

Duplicate values in your Excel worksheet can mess up your data. Unless duplicates are intentional, they can cause inaccuracies 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 youhow to remove duplicates in Excel, let’s walk you through the process of checking your sheet for duplicate data.

Excel has aConditional Formatting toolthat helps to identify, visualize, and draw conclusions from data. Here’s how to use the tool to highlight duplicate values in your Excel document.

Excel will immediately highlight rows and columns with duplicate values.

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.

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.

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 highlightRow 4andRow 13. Both rows have duplicate values in theNames,Scores, andIDcolumns, but different values in theDaycolumn.

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.

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:

If your PC has a small screen or the Excel window is minimized, click theData Toolsdrop-down button and selectRemove Duplicates.

Quick Tip:To make the first row of an Excel worksheet a header, go to theViewtab, selectFreeze Panes, and selectFreeze Top Row.

“Advanced Filter” is another brilliant tool that helps youclean 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.

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? PressControl+Zto 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 containingoutlined or grouped data. So if yougrouped 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 tothis official documentation from Microsoftto learn more about removing duplicates in Excel and filtering unique values.

Sodiq has written thousands of tutorials, guides, and explainers over the past 4 years to help people solve problems with Android, iOS, Mac, and Windows devices. He also enjoys reviewing consumer tech products (smartphones, smart home devices, accessories, etc.) and binge-watching comedy series in his spare time.Read Sodiq’s Full Bio

Welcome to Help Desk Geek- a blog full of tech tips from trusted tech experts. We have thousands of articles and guides to help you troubleshoot any issue. Our articles have been read over 150 million times since we launched in 2008.

HomeAbout UsEditorial StandardsContact UsTerms of Use

Copyright © 2008-2024 Help Desk Geek.com, LLC All Rights Reserved