Whether you’re cleaning a contact list, preparing sales data, or organizing information for analysis, knowing how to remove duplicates in Excel helps keep your workbook accurate and reliable.
The good news is that Excel offers several built-in ways to remove or identify duplicate values, whether you want to delete them or review them first.
How to Remove Duplicates in Excel Using Remove Duplicates
Excel’s built-in Remove Duplicates feature is the fastest way to clean up repeated entries. Follow these steps to find and remove duplicate data in just a few clicks.
Step-by-Step Guide
Before making any changes, it’s always a good idea to create a backup copy of your worksheet. Although the process is simple, deleting duplicates is permanent once the workbook is saved, so having a copy ensures you can recover any data if needed.
- Step 1. Select the data range
Highlight all the cells that contain the data you want to clean. If your dataset includes headers, make sure to include them in the selection so Excel can correctly identify each column.
- Step 2. Go to data
The first step in how to remove duplicates in Excel is opening the Data tab on the Ribbon, where you’ll find sorting, filtering, and the Remove Duplicates feature.
- Step 3. Choose Remove Duplicates
Click Remove Duplicates in the Data Tools group. Excel will display a dialog box where you can choose which columns should be checked for duplicate values.
- Step 4. Select the columns Excel should check
Check or uncheck the columns depending on how you want Excel to compare your data. Choosing the right columns is important because Excel only removes rows that match based on the selected fields.
- Step 5. Click OK
After confirming your selections, click OK. Excel immediately scans the dataset and removes any duplicate records that match your chosen criteria.
Important: If you’re looking for how to remove duplicates but keep first instance in Excel, remember that the Remove Duplicates feature always keeps the first occurrence and removes every matching record that follows.
Removing duplicates from one column vs. multiple columns
Knowing how to remove duplicates in Excel also means understanding which columns to select. Choosing only one column tells Excel to check duplicates based solely on that data.
| Feature | One Column Selected | Multiple Columns Selected |
| Duplicate check | Compares values in one column only. | Compares all selected columns together. |
| Example | Two rows with the same email are duplicates. | Two rows are duplicates only if every selected value matches. |
| Best for | Email addresses, phone numbers, IDs. | Customer records, orders, or product lists. |
| Result | May remove rows with different details. | Keeps rows unless the entire selected data matches. |
>>> Read more: How to Delete a Page in Word: Simple Fixes That Work 2026
How to Find Duplicates Before Removing Them
Knowing how to remove duplicates in a column in Excel also means knowing when not to remove them. Conditional Formatting highlights duplicate values while keeping your data intact.
- Step 1. Select the data
Highlight the cells or columns you want Excel to check. You can select a single column or an entire table depending on your dataset.
- Step 2. Go to Home > conditional formatting
On the Home tab, click Conditional Formatting to open the formatting menu. This feature automatically applies visual formatting based on rules you choose.
- Step 3. Select highlight cells rules > duplicate values
When learning how to remove duplicates in Excel, choose Highlight Cells Rules > Duplicate Values. Excel will automatically compare the selected cells and identify repeated entries.
- Step 4. Choose a formatting style
Select the highlight color or formatting style you prefer. Bright colors like light red or yellow make duplicate records much easier to spot while reviewing large datasets.
- Step 5. Review the highlighted records
Excel highlights every duplicate value without deleting anything. This allows you to compare similar records, verify whether they’re true duplicates, and decide whether you want to remove them using the Remove Duplicates feature or keep them because they contain different information.
How to Remove Duplicates in Excel Without Deleting Data
Fortunately, Excel provides a couple of methods that let you display unique values without permanently changing your data. If you’re looking for how to remove duplicates in Excel without risking data loss, these options are worth considering.
Use the UNIQUE function
If you’re using Microsoft 365 or Excel 2021, the UNIQUE function is the simplest way to display only unique values while preserving the original data.
Simply enter the following formula in an empty cell: =UNIQUE(A2:A100)
Replace A2:A100 with the range that contains your data. Excel automatically generates a new list containing only unique values, and the results update whenever the source data changes.
Use advanced filter
If you’re learning how to remove duplicates in Excel and your version doesn’t support the UNIQUE function, Advanced Filter is another reliable solution.
Go to: Data > Advanced
Then select: Unique records only
Next, choose whether you want to filter the list in place or copy the unique records to another location. Copying the results is usually the safer option because your original dataset remains unchanged.
>>> Read more: What Is a Dual Boot? How Running Two Operating Systems Works
What to Do If Excel Does Not Remove Duplicates
How to remove duplicates in Excel seems straightforward. But sometimes Excel may not find duplicates even when the data looks identical. In most cases, the issue comes from hidden differences in the data rather than the Remove Duplicates tool itself.
- Extra spaces and hidden characters
Spaces or invisible characters from copied data can make identical entries appear different. Use functions like TRIM() and CLEAN() to remove unnecessary spaces and hidden characters before trying again.
- Inconsistent formatting or data types
Values may be stored differently even when they look the same. For example, Excel may treat 12345 stored as text differently from 12345 stored as a number. Standardize number formats, capitalization, and date formats before removing duplicates.
- Incorrect columns selected
Excel only checks the columns you select in the Remove Duplicates window. If you choose the wrong columns, records that should match may appear unique. Review your selected columns carefully before confirming the removal.
- Subtotal rows or grouped data
Subtotal rows, outline groups, or summary data can affect how Excel identifies duplicates. Remove or adjust these elements before cleaning your dataset.
FAQs
How to remove duplicate values in Excel shortcut key?
Excel doesn’t have a single dedicated shortcut that instantly removes duplicates, but you can open the Remove Duplicates dialog quickly by selecting your data and pressing Alt + A + M on Windows.
How do I delete duplicate cells in Excel but keep one?
If you’re wondering how to remove duplicates in Excel but keep one, use the built-in Remove Duplicates feature. Excel automatically keeps the first occurrence of every value and removes the remaining matching records.
What is the fastest way to remove duplicates in Excel?
For most spreadsheets, the fastest solution is Data > Remove Duplicates. It takes only a few clicks, works with both single and multiple columns, and quickly reports how many duplicate values were removed.
Conclusion
Learning how to remove duplicates in Excel is about more than simply deleting repeated values. The right approach depends on whether you want to permanently clean your data, review duplicates before removing them, or create a separate list of unique records.
