What Is Remove Duplicates From Excel Column?
The remove duplicates from Excel column techniques enable us to delete values occurring multiple times in a chosen column of data. We can use the Remove Duplicates, Advanced Filter, and Conditional Formatting feature to delete the duplicates.
Users can utilize the remove duplicates from Excel column methods to eliminate redundancy in a given dataset. It improves the Excel information quality and makes it clearer.
For example, the below table lists students and their test scores in different subjects.
However, the dataset includes two entries for the student Gloria Watson. And we must remove the second row of data for the repeated student name in column A.
Then, we can use the Remove Duplicates Excel option to remove entire row based on duplicates from single column in Excel.
In the above example of remove entire row based on duplicates from single column in Excel, we select the entire dataset. And then, choose the Remove Duplicates option from the Data tab.
Next, the Remove Duplicates window opens with all the column heading check boxes chosen under Columns. Since we must remove row 9 based on the duplicate student name in column A, uncheck all the column heading check boxes under Columns, except the first column, Student. And click OK in the Remove Duplicates window to close it.
Excel will delete the required row from the dataset. And it will show a message stating the total duplicates found and removed and the total unique values remaining in the column chosen in the Remove Duplicates window.
Thus, we checked one column, column A, for duplicate names. And as Gloria Watson occurred twice in the column in cells A6 and A9, the entire row 9 data got removed, reducing the total rows of data in the dataset from 11 to 10.
Table of contents
Key Takeaways
- The remove duplicates from Excel column option helps one to delete values appearing multiple times in a chosen column range.
- Users can use the options to remove duplicates from a massive column in a worksheet in one go. And hence, it helps improve the Excel data quality hassle-free.
- We can use the Remove Duplicates and Advanced Filter options to remove duplicates from one or more columns in Excel.
- We can utilize the Conditional Formatting option to highlight duplicates in the chosen column. And then, use the Delete option to remove the duplicates from the specific column.
How To Remove Duplicates In Excel?
We can use the following methods to remove duplicates from Excel column:
- Remove Duplicates From Data Tools Group
- Using Advanced Filter In Excel
- Conditional Formatting To Remove Excel Column Duplicates
The abovementioned techniques retain the first occurrence of the value that appears repeatedly in a chosen range and remove their remaining occurrences. And thus, the resulting range contains unique values.
Furthermore, consider we must remove duplicates from multiple columns simultaneously. Then, the first and second methods will consider the columns to have duplicates when multiple rows of data in the concerned columns are identical. In other words, if multiple cells in the first column contain duplicates, the remaining columns must also contain duplicates in the same rows of cells. Otherwise, repeated data in multiple columns is not considered duplicates.
On the other hand, the third method will highlight cells containing duplicates in each chosen column, which may or may not be in the same rows in all the concerned columns.
And the techniques will help us delete such rows of data, leading to the chosen columns showing unique values.
Let us see the methods in detail with practical examples.
#1 – Remove Duplicates From Data Tools Group
The steps to remove duplicates from Excel column using the Remove Duplicates option from the Data Tools group are as follows:
- Choose the column where we must remove the duplicate values.
- Choose the Data tab – Remove Duplicates option in the Data Tools group.
- We will see the Remove Duplicates Warning message box if the source dataset contains columns before or after the chosen column range. It provides options to expand or continue with the chosen column range selection.
Choose the required option and click Remove Duplicates.
Selecting either of the options will open the Remove Duplicates window. However, the difference is that choosing the option to expand the current selection will lead to the Remove Duplicates window listing all the columns in the dataset. And we can select the columns where we wish to remove duplicates.
On the other hand, going with the current column range selection will make the Remove Duplicates window list only the chosen column. - Click OK to complete the duplicate removal action.
- The duplicates get deleted from the selected column range. And Excel will show a message box stating the total duplicates found and removed and the remaining unique values in the chosen range.
However, if the chosen range does not contain duplicates, the message box will show the no duplicates found comment. - Click OK to close the message box.
Basic Example
The following remove duplicates from Excel column example includes a table listing smartphones and their costs.
The task is to remove the duplicates of smartphones listed in column A.
Then, the steps are as follows:
Step 1: Select the cell range A1:A14 and choose Data – Remove Duplicates.
Step 2: The Remove Duplicates Warning message box opens. Since we must remove duplicates only from the chosen column range, select the option to continue with the current selection.
And click Remove Duplicates.
Step 3: The Remove Duplicates window opens, where only the chosen column will be listed and selected in the Columns section.
Click OK to exit the window and proceed.
Step 4: The duplicates get removed from the chosen column range.
And Excel will show a message box. It briefs about the total duplicate values found and removed and the count of the remaining unique values in the chosen column range.
In this example, Excel found four duplicates, which got removed, with the chosen column range containing nine unique values after duplicate removal.
Click OK to exit the message box and obtain the required column with the duplicates removed.
Furthermore, if we want to remove duplicates from two columns in Excel, the steps are as follows:
Step 1: The two columns are adjacent, and there are no additional columns in the given dataset. So, select a cell in the dataset. And then choose Data – Remove Duplicates.
Step 2: The Remove Duplicates window will open instead of the Remove Duplicates Warning window.
The window will list the columns of the dataset in the Columns section. And all the column check boxes will appear checked.
As we must remove duplicates from two columns in Excel, let the two columns listed and chosen in the Columns section remain selected.
Next, click OK in the Remove Duplicates window.
Excel will remove the duplicates from the two columns. And the message box stating the total duplicates identified and deleted and the count of the remaining unique values in the two columns appears.
Click OK to exit the message box and view the required columns without duplicates.
In the above example, rows 9, 12, 13, and 14 across columns A and B contain duplicates, with the same product and cost data as in the other rows in the two columns.
Thus, the Remove Duplicates option removes the four specified rows of data. And the remaining rows shift up to display a dataset with unique values.
#2 – Using Advanced Filter In Excel
The steps to remove duplicates from Excel column using the Advanced Filter Excel option are as follows:
- Select a cell in the active sheet containing the column from which we must remove the duplicates.
- Choose the Data tab – Advanced filter option in the Sort & Filter group.
- The Advanced Filter window opens.
First, select whether we want to show the output in the same location as the source dataset or at a different location in the worksheet.
Next, update the column or range, where we must remove the duplicates, in the List range field. And if required, update the required duplicate removal condition in the Criteria range field.
Next, if we choose the option that displays the output in another location, update the target cell reference in the Copy to field.
And then, check the option to display only the unique records. - Click OK in the Advanced Filter window to view the required data in the specified target cell, with the duplicates removed.
Basic Example
The following remove duplicates from Excel column example involves a table listing teams and their employees.
The requirement is to delete the duplicate entries in the given dataset and display the dataset with unique values in the target cell E1.
Then, here is how to use the Advanced filter option to obtain the required output in the target cell.
Step 1: Since we must remove the duplicates from the entire dataset, select a cell in the source data range. And then, choose Data – Advanced in the Sort & Filter group.
The Advanced Filter window opens, with the first option chosen to filter and show the required data in the same location as the source dataset. And the List range field shows the Excel absolute reference to the source dataset range as we chose a cell in the dataset.
Step 2: Choose the option to copy or display the output in another location.
Step 3: Click inside the Copy to field. And then, select the target cell E1 to update it as the location to copy or display the final dataset with all the duplicates removed.
Step 4: Check the option to display unique records in the output.
Clicking OK in the Advanced Filter window will give the following result.
The dataset, containing unique values, gets pasted in the range starting from the target cell E1.
Thus, the Advanced filter option removes the rows of data across the two columns containing duplicates. In other words, those rows with the same pair of team and employee get counted as duplicates and removed from the dataset displayed as the output.
#3 – Conditional Formatting To Remove Excel Column Duplicates
The steps to remove duplicates from Excel column using the Excel Conditional Formatting option are as follows:
- Choose a cell in the column where we want to delete the duplicates.
- Select the Home tab – Conditional Formatting feature – Highlight Cells Rules right arrow – Duplicate Values option.
- The Duplicate Values window opens, with the first field set as Duplicate and the first highlighting format option set as the second field.
- Set the required highlighting format in the second field using its drop-down button and list.
- The duplicates get highlighted in the column. Click OK to close the Duplicate Values window.
- Click on the number of the row containing the first highlighted cell from the cells we aim to remove. And then, press Ctrl and click on the row numbers containing the remaining cells we want to delete, to select the rows. Next, right-click to open the context menu and choose Delete to delete the chosen rows of duplicate data in the required column.
Basic Example
The following remove duplicates from Excel column illustration includes a table containing US states and capitals.
And the requirement is to delete the duplicates from columns A and B.
Then, the steps are as follows:
Step 1: Choose the cell range A2:B25. And then select the Home tab – The Conditional Formatting feature – The Highlight Cells Rules right arrow – The Duplicate Values option.
The Duplicate Values window will open, showing the first field as Duplicate. Also, the first highlighting format option appears chosen in the second field.
And hence, the duplicate values in the two columns appear highlighted in the specified format.
We can click the second field drop-down button to set the required highlighting format.
Step 2: Click OK in the Duplicate Values window.
Thus, the columns with the duplicates highlighted will appear as shown below:
On the other hand, we can also remove duplicates from one column based on another column in Excel using the Conditional Formatting feature.
For instance, in this example, multiple rows in the dataset have the same US state-capital data. In other words, the two columns have duplicates in the same rows.
So, here is how to remove duplicates from column B based on column A data.
Choose the cell range A1:A25. And then, select the Duplicate Values option under the Highlight Cells Rules option in the Conditional Formatting feature in the Home tab.
The Duplicate Values window opens, with the field and second field set as Duplicate and the required highlighting format. And the duplicates get highlighted in the chosen column range.
Click OK to close the Duplicate Values window.
Whether it is the first scenario or the remove duplicates from one column based on another column in Excel case, the following step explains how to delete duplicates.
Step 3: Click on the number of the row containing the first highlighted cell from the cells we wish to remove. And then, while pressing Ctrl, click on the row numbers containing the remaining cells we want to delete to select the rows.
And then, right-click to choose Delete from the context menu to delete the chosen rows of duplicate data in the required column.
And as we choose the entire rows, the column B cells containing the duplicates also get deleted along with the highlighted column A cells. Thus, we achieve the below dataset without duplicates.
Important Things To Note
- The keyboard shortcut to use the Remove Duplicates option to remove duplicates from Excel column is Alt + A + M.
- The Remove Duplicates and the Advanced Filter options remove all the duplicates from the chosen column range except the first occurrences of the values.
- The Remove Duplicates and the Advanced Filter options consider multiple columns to have duplicates only when they include multiple rows of identical data.
Frequently Asked Questions (FAQs)
We can remove duplicates from a column in Excel VBA using the following steps, explained with an example.
The following table lists customers and their contact numbers.
The requirement is to remove the duplicates from column A. Then, the steps are as follows:
Step 1: Open the worksheet containing the above-shown table. And press Alt + F11 to access the VBA Editor.
Step 2: Choose the required VBAProject. And then, select the Module option under Insert in the menu to open a new module.
Step 3: Enter the VBA code in the module to remove the duplicates from the required column A range.
Step 4: Press the play icon to run the code.
The code specifies not to consider the first row of the specified column range, as it is the heading. And the code ensures that duplicates are removed from the cited column range, with only the first occurrence of a value displayed as a unique value in the output.
Finally, open the active worksheet to view column A without duplicates.
Excel remove duplicates in column without shifting cells is possible. And the steps are as follows:
1. Click on a cell in the column where we want to remove the duplicates without shifting the cells.
2. Choose the Home tab – Conditional Formatting feature – Highlight Cells Rules right arrow – Duplicate Values option.
3. The Duplicate Values window will open. The first field will be Duplicate, and the first highlighting format option will be the second field value.
4. Choose the required highlighting format in the second field using its drop-down button and list.
5. The duplicates get highlighted in the column. Click OK to exit the Duplicate Values window.
6. Click the first highlighted cell from the cells we want to remove. And then, while pressing Ctrl, click the remaining cells we want to remove. Next, right-click to open the context menu and select Clear Contents to remove the duplicate data in the chosen cells in the required column without shifting cells.
The shortcut to remove duplicates from a column in Excel is to select the required column range. And then, press Alt + A + M to choose the Remove Duplicates option in the Data tab.
The Remove Duplicates window will open, where we can choose additional columns listed in the Columns section. Finally, pressing Enter or clicking OK will remove the duplicates from the chosen column or columns.
Download Template
This article must be helpful to understand the Remove Duplicates From Excel Column, with its formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to What Is Remove Duplicates From Excel Column. We learn to do it using the Data Tools group, Advanced Filter, & Conditional Formatting. You can learn more from the following articles –
Leave a Reply