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.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Order ID | Customer | Product | City | Amount |
| 2 | 1001 | John | Laptop | New York | 850 |
| 3 | 1002 | Sarah | Monitor | Chicago | 320 |
| 4 | 1003 | David | Keyboard | Boston | 75 |
| 5 | 1001 | John | Laptop | New York | 850 |
| 6 | 1005 | Emma | Mouse | Seattle | 40 |
| 7 | 1006 | Michael | Monitor | Chicago | 320 |
| 8 | 1003 | David | Keyboard | Boston | 75 |
| 9 | 1008 | Lisa | Webcam | Denver | 95 |
| 10 | 1009 | Sarah | Monitor | Chicago | 320 |
| 11 | 1001 | John | Laptop | New York | 850 |
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 ID | Customer | Product | City | Amount |
|---|---|---|---|---|
| 1001 | John | Laptop | New York | 850 |
| 1002 | John | Laptop | New York | 850 |
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”.

- 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.

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.

- You will see a summary of number of duplicate rows removed (3) and the number unique entries retained (7).

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.

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.

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.

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.

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.

- 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.

- When you’re finished, select “Home > Close & Load” and Excel will create a new sheet (like Table_1) with the unique data.

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:
| Method | Removes Data? | Useful For |
|---|---|---|
| Conditional Formatting | No | Finding duplicates visually |
| Remove Duplicates | Yes | Permanently cleaning a dataset |
| COUNTIF / COUNTIFS | No | Identifying duplicates with formulas |
| UNIQUE | No | Creating a separate unique list |
| Power Query | Yes, in the query output, original sheet is retained | Large or recurring data cleanup |




