Managing a large Excel spreadsheet may be challenging, and duplicated column values may not assist your productivity, making management more difficult. Removing the duplicates may be the best option to reduce errors, improve efficiency, get better data visualization, and achieve data accuracy and consistency.
Thankfully, you can remove duplicates from selected Excel columns to leave unique values using the methods in this post. Let’s get into the methods immediately.
Method 1: Data Tools Option
You can remove duplicates from a column in Excel using the option from the Data Tools group. Here are the steps to do this:
Step 1: Open the Spreadsheet
First, open the Excel spreadsheet containing the duplicated data. It could be a file already stored on your PC or a new spreadsheet you are working on. The image below is an Excel worksheet for Samsung and iPhone brand models, and we need to identify the number of unique brands in the list by removing the duplicates.
Step 2: Select the Column
Select the column containing the duplicated value you want to remove.
Step 3: Data
Click the Data tab at the top ribbon of the worksheet.
Step 4: Remove Duplicates
Click the Remove Duplicates option from the Data Tools section in the toolbar.
Step 5: Authorize the Removal
A warning box will pop up on the screen. Click the radio button next to the ‘Continue with the current selection’ option. You can also use the expand selection option by clicking the radio button beside it.
Next, click the Remove duplicates button in the pop-up box.
Another box will appear on the screen asking you to select one or more columns containing the duplicates. It will also show the column you selected. Click OK to continue.
Next, a prompt will appear on the screen telling you the number of duplicate values found and removed and the number of unique values left.
Click OK in the prompt.
You will see that the duplicated values are no longer in the worksheet as in the image below.
Method 2: Advanced Filter Option
You can remove duplicate files from the Excel column with the advanced filter tool as follows:
Step 1: Open the Worksheet and Select the Column
As in the first method, open the document with the duplicated values and select the affected column.
Step 2: Data
Click the Data tab at the top ribbon of the page.
Step 3: Advanced
Select the Advanced option from the Sort & Filter section in the toolbar.
Step 4: Set up the Advanced Filter Prompt
First, select the copy to another location radio button on the dialog box that appears.
You will see the cell reference for the selected range in the List range field.
Give a location where the system will copy the values in the ‘Copy to’ field. You can select a destination in the current sheet or another sheet by clicking a desired cell. To do this, click the ‘Copy to’ field and select a desired cell.
Check the box next to the Unique records only option and click OK.
The system will filter the column and paste the unique values into the selected destination.
Method 3: Conditional Formatting Option
Another option for removing duplicates from Excel columns is the Conditional formatting method. Here are the steps to use this method.
Step 1: Open and Select the Duplicated Columns
Again, this method begins with opening the Excel document and selecting the duplicated column.
Step 2: Home
Click the Home tab at the top ribbon of the sheet.
Step 3: Conditional Formatting
Select the Conditional formatting option from the Styles section in the toolbar.
Step 4: Highlight Cells Rules
Click the Highlight Cells Rules option from the dropdown menu and select the ‘Duplicate values’ option.
A dialog box will appear on the screen, and Excel will highlight duplicated values in the column.
Click OK in the dialog box and delete the duplicated values manually.
Conclusion
You can remove duplicates from columns in Excel using any of the three methods above, but it is essential to understand that the methods work differently. The first method will keep the first value and remove all duplicates, while the second method will extract the unique values to a new destination while keeping the old data. The third method will highlight all duplicate values so you can remove them.
You may also like to read:
How to Use TEXTAFTER Excel with Examples
How To Rename a Table In Excel
How To Create Dependent Drop-down List in Excel
How to Switch First and Last Names in Excel with Comma
How to Insert a Signature in Excel
How To Remove Empty Cells In Excel