Home » Tech Tips » Productivity » 5 Ways to Remove Duplicate Entries in Excel

5 Ways to Remove Duplicate Entries in Excel

It’s common to copy and paste records or combine data from multiple sources when working with Excel sheets. This can easily result in duplicate entries or repeated values making your reports inaccurate and harder to analyze your data. Fortunately, Excel provides several ways to find, identify, and remove duplicate data. The best method depends on whether you want to simply highlight duplicates, delete them permanently, or create a separate list containing only unique values.

Sample Excel Data

Let’s use the following customer data with 10 line items to demonstrate different methods. Here, rows 5, 8, and 11 are duplicate rows.

ABCDE
1Order IDCustomerProductCityAmount
21001JohnLaptopNew York850
31002SarahMonitorChicago320
41003DavidKeyboardBoston75
51001JohnLaptopNew York850
61005EmmaMouseSeattle40
71006MichaelMonitorChicago320
81003DavidKeyboardBoston75
91008LisaWebcamDenver95
101009SarahMonitorChicago320
111001JohnLaptopNew York850

Note that a duplicate does not always mean that two complete rows are identical. Consider these two records (not included in the above example):

Order IDCustomerProductCityAmount
1001JohnLaptopNew York850
1002JohnLaptopNew York850

The Order IDs are different, but the remaining information is identical. Whether these are duplicates depends on what the Order ID represents. If every order should have a unique ID, these could be two legitimate orders. If the ID is irrelevant to your analysis and you are looking for repeated customer-product records, they may be duplicates. Therefore, always decide which columns define a duplicate before removing anything.

1. Highlight Duplicate Cells Without Removing Them

If you only want to find duplicate cells, Conditional Formatting is one of the easiest options.

  • Select the range you want to check like A2:E11.
  • Then go to “Home > Conditional Formatting > Highlight Cells Rules > Duplicate Values”.
Conditional Formatting in Excel
  • In the “Duplicate Values” dialog box that appears, keep the default “Duplicate” option and select a formatting style (color to highlight duplicate cells).
  • Excel will now highlight cells that contain values appearing more than once in the selected range. You can focus on the completely highlighted rows 2, 4, 5, 8, and 11 which are appearing more than once in the selected range. 
Highlight Duplicates with Colors

This method is useful when you want to inspect the data before deleting anything.

2. Using Remove Duplicates Feature

Excel’s built-in Remove Duplicates feature is the quickest option when you want to permanently remove repeated records.

  • First, select the entire dataset, including the headings. Then go to “Data” tab and click “Remove Duplicates” option in “Data Tools” group.
  • You will see a pop-up prompting to select the columns and make sure to check “My data has headers” option.
  • Click “Select All” and then “OK” buttons.
Remove Duplicates Feature in Excel
  • You will see a summary of number of duplicate rows removed (3) and the number unique entries retained (7).
Confirmed Removed Duplicates

Although you can undo the deletion action, we strongly recommend taking a backup or copy of the worksheet before you delete the duplicates.

3. Find Duplicates with a COUNTIF Formula

Sometimes you don’t want Excel to delete anything automatically. Instead, you may want a column that tells you whether a value is duplicated. For example, to check whether each “Customer” name occurs more than once, add a new column (F) called “Status”.

In F2, enter the following formula and drag it till F11.

=IF(COUNTIF($B$2:$B$11,B2)>1,"Duplicate","Unique")

The result will identify customers such as John, Sarah, and David as duplicates because they appear multiple times. This method is useful when you need more control over the cleanup process.

Show Unique Status

You can also count how many times a value appears using the following formula. If the result is 3, that customer appears three times in the selected range.

=COUNTIF($B$2:$B$11,B2)

Use the following formula to check the duplicates based on all five columns (A to E).

=IF(COUNTIFS($A$2:$A$11,A2,$B$2:$B$11,B2,$C$2:$C$11,C2,$D$2:$D$11,D2,$E$2:$E$11,E2)>1,"Duplicate","Unique")

This checks the combination of Customer, Product, City, and Amount rather than checking just one column. In our example, this will show rows 2, 4, 5, 8, and 11 are duplicates. This is a better approach when a single field is not enough to determine whether two records represent the same entry.

Unique Status Based on All Data

4. Create a Unique List with the UNIQUE Function

The UNIQUE function provides a convenient way to create a duplicate-free list without changing the original data. For example, to create a list of unique customers, enter the following command :

=UNIQUE(B2:B11)

Excel automatically creates a separate list containing each customer once.

Filter Unique Customers

You can also use UNIQUE with multiple columns as below to get the unique combinations of Customer, Product, City, and Amount.

=UNIQUE(B2:E11)

You can use the function on the same sheet or in a new sheet. The major advantage is that the original dataset remains untouched. If the source data changes, the resulting list can update automatically. This makes UNIQUE particularly useful for reports, dashboards, lookup lists, and data analysis.

Filter Unique Data Based on Multiple Columns

5. Use Power Query for Large or Repeated Data Cleanup

For large datasets or a process that you need to repeat regularly, Power Query is often a better choice than manually removing duplicates.

  • Select your data and go to “Data” tab. Click “From Table/Range” option in “Get & Transform Data” group.
  • A “Create Table” pop-up will appear, check “My table has headers” option and click “OK” button.
Open Power Query Editor
  • This will open the selected data as a table in Power Query Editor. Go to “Home” tab, click on the “Remove Rows” button and select “Remove Duplicates” option.
Remove Duplicates in Power Query Editor
  • When you’re finished, select “Home > Close & Load” and Excel will create a new sheet (like Table_1) with the unique data.
Removed Duplicates in Power Query

The biggest advantage is repeatability. If you receive a new version of the same type of data later, you can refresh the Power Query rather than performing the cleanup manually again.

Which Method Should You Use?

Each method solves a slightly different problem:

MethodRemoves Data?Useful For
Conditional FormattingNoFinding duplicates visually
Remove DuplicatesYesPermanently cleaning a dataset
COUNTIF / COUNTIFSNoIdentifying duplicates with formulas
UNIQUENoCreating a separate unique list
Power QueryYes, in the query output, original sheet is retainedLarge or recurring data cleanup

Leave a Comment

Your email address will not be published. Required fields are marked *