Duplicates in your spreadsheet may be a minor inconvenience, but they’re still frustrating to deal with. Most spreadsheet tools like Google Sheets don’t have a straightforward way of removing duplicates in your sheet.
Fortunately, with Excel, you can solve this problem easily using several different methods. After you read this article, you will possess a comprehensive arsenal of tools to deal with this problem.
So let’s get started with our step-by-step guide on how to remove duplicates in Excel.
Table of Contents
How to Remove Duplicates in Excel Using the Built-in “Remove Duplicate” Tool
Microsoft Excel has a dedicated feature for removing duplicates, but you should be cautious when using this feature.
Before you go ahead and remove important information, make sure that you have selected the cell or the range which contains the duplicate data you want to get rid of.
A large dataset may have repeating values that are accurate and correct which may be important information, so make sure that the duplicates you want to remove are not needed and are instead a nuisance.
I recommend creating a copy of your work before performing this method.
These are the steps to take to remove duplicates using the Excel built-in feature:
- Open your Excel spreadsheet document and highlight the range of cells with the data from which you want to remove duplicates.
- Navigate to the Data tab located at the Excel ribbon at the top.
- In the Data tools menu, select the Remove Duplicates button, and a pop-up dialogue box will appear.
- Microsoft Excel will automatically determine the range of cells for you after you select the remove duplicates option. Make sure the “My data has headers” is checked if your columns have headers. This option exempts the first row of your data from being considered to remove duplicates.
- You can review the columns that Excel has selected for duplicate comparison. Uncheck the columns that you do not want to remove duplicates from if you just want to remove duplicates on specific columns.
- Click OK once you have completed all the steps above.
Excel will automatically delete all the duplicate data on your worksheet, and a dialogue box will appear with the information on how many duplicates were found, how many of them were removed, and the number of unique values remaining
- Click OK on the dialogue box and go back to your worksheet to see the value removed and what is left of your data. Remember to save after removing the duplicates.
The remove duplicates feature permanently removes the duplicate values from your data, so make sure to have a copy of your worksheet before going ahead with this method in case you notice that you are missing important data.
How to Get Rid of Duplicates in Excel Using Filters.
You can also remove duplicates in Excel using the filter feature by following these steps:
- Select the range of data that contains duplicates.
- Go to the Data tab in the Excel ribbon.
- Navigate to Sort & Filter > Filter button. This will add filter arrows to each column header.
- Click on the filter arrow in the column containing the duplicates you want to remove.
- In the filter dropdown menu, uncheck the box for Select All to deselect all values.
- Scroll down the list and check the box for “(Blanks)” if you want to include blank cells as well.
- Click the OK button. Excel will filter the data, showing only the unique values and any selected blanks.
- Select the filtered data (excluding the column headers).
- Right-click on the selected data and choose “Delete” from the context menu.
- In the Delete dialog box, select Entire Row and click the OK button. This will delete the rows containing duplicate values.
- Go back to the Data tab and click on the Filter button again to turn off the filter and show all the data.
How to Remove Duplicates in Excel using the Advanced Filter Option
This is a cool way to eliminate duplicates in Excel. The advanced filter method allows one to copy the unique values to any other cells they want while preserving the original data.
This proves to be really efficient and useful when you need to keep a record of the removed duplicates or when you want to work with a separate dataset without the duplicates.
One thing to consider when using the advanced filter feature to delete duplicates in Excel is if you have a constantly changing dataset. If it is indeed constantly changing, then the unique values will need to be updated automatically. So you may need to repeat the steps below or combine with other methods like VBA macros or Excel formulas.
Below are the steps on how to delete duplicates in Excel using the advanced filter option:
- Highlight the range of cells in which you wish to remove duplicate values, including the header row.
- Go to Data > Advanced.
- Choose the option “Copy to another location.”
- Check the box for Unique records only.
- Click in the Criteria range text box and select the range you want to remove duplicates from.
- Click in the Copy to text box and select the range you want to paste the new unique table.
- Click on OK and watch as Excel automatically copies the unique values of your selected dataset to the specified location without duplicates.
You can review the destination range to see the unique values that have been moved. You can also decide to remove the original data if you have no need for it anymore by highlighting it and pressing the delete key on your keyboard.
Remember to save your Excel spreadsheet document with the duplicates removed.
How to Conditionally Format Unique or Duplicate Values
If you want to format unique or duplicate values in your dataset in Excel, Follow the steps below:
- Open your Excel spreadsheet document and highlight the range of cells with the data from which you want to conditionally format duplicate or unique values.
- At the Excel ribbon on the toolbar, click on Home.
- Navigate to the styles section or group in the toolbar and click on the Conditional Formatting button.
- A drop-down menu will appear after clicking the conditional formatting button, and from it, select the Highlight Cells Rules option.
- After clicking the Highlight cells rules option, a sub-menu will appear, and select the duplicate values option from it to open a dialogue box.
- In the dialogue box, select the cells you want to format, either the cells containing duplicate values or the cells containing unique values using the drop-down menu provided.
- After deciding on the cells you want to format, click the other drop-down menu to see the preset formatting options, or click on Custom Format at the bottom for more formatting options.
- Click on OK after choosing your formatting options and Excel will apply the conditional formatting on the selected range.
Conditional formatting does not remove or delete duplicates in your data. It’s only used to highlight duplicates or unique values so the user can spot them with ease, making the spreadsheet easy to comprehend however, Conditional formatting allows the deletion of the duplicates easy once they are highlighted.
How to Remove Duplicates in Excel Based on One Column
Removing duplicates in Excel based on one column is easy and it is done using the remove duplicates feature mentioned above in the article.
Follow the steps below to learn how to delete duplicates in Excel based on only one column
- Open your spreadsheet document in Excel and select the entire range of cells holding your data, including the column with the duplicates.
- Click on Data at the Excel ribbon at the top of the toolbar.
- Select Remove Duplicates from the data tools section.
- Excel automatically selects the entire range of cells containing your data after you click on the Remove Duplicates option. Make sure you have the My data has headers check box checked if your data has headers. If not, you can leave it unchecked.
- In the same dialogue box that pops up, you can review the columns that you want to check and remove duplicates from. As shown above, I have selected only one column from the entire worksheet.
- Click on OK, and Excel will remove the duplicates in the column you selected alone.
- A message box will appear showing you how many cells with duplicate values were found and removed, as well as the number of cells containing unique values.
That is how you remove duplicates in just one column of your data in Excel. By specifying the column for duplicate removal, you ensure that only the values in that column are considered for the operation.
Frequently Asked Questions
Do I Need a Formula to Remove Duplicates in Excel?
No. You do not need a formula to remove duplicates from Excel. Using a formula is a way you can remove duplicates, but there are better methods to use which are more efficient and easy to understand.
In this article, we’ve shown you other methods for removing duplicates in Excel including using the Remove Duplicates option.
How Do I Remove Duplicates from Excel Formatting?
You can’t remove duplicates from Excel using formatting. The only thing you can do is differentiate the duplicate values from the unique values.
Wrapping Up
Now you know how to remove duplicates in Excel, (congrats on your new ability) we have more useful guides similar to this that might interest you, or perhaps you’d like to skip the tutorials and get a perfectly working template. In this case, you should check out our premium template library, where you can use the code SSP to save 50%.
Related:
