How to Remove Duplicates from Column in Excel (3 Methods)

While working with a large dataset in Excel, there is a possibility that you are getting the same duplicate values from columns. Sometimes we may need to remove those duplicate values from columns to get a clear concept about the worksheet. Excel provides some built-in features and formulas from which you can easily remove duplicates from columns. Today, in this article, we will learn how to remove duplicates from columns in Excel.

Download Practice Workbook

Download this practice sheet to exercise while you are reading this article.

3 Suitable Methods to Remove Duplicates from Column in Excel

Consider a situation where you are given a dataset of some camera body and lens models in the “Camera Model” and “Lens Model” columns. Now in the dataset, these columns are containing some duplicate values. We need to remove those duplicate values from columns. To do that we will use three different methods discussed below.

1. Apply the Remove Duplicates Feature to Remove Duplicates from Column

The Remove Duplicates is an amazing feature by which you can easily remove your duplicates from the dataset. You can apply this feature to a single column or multiple columns. Let’s learn both of them!

i. Remove Duplicates from Single Column

First, we will use the Remove Duplicate feature which is a built-in feature in Excel. Let’s follow these steps to learn!

Step 1:

• Select the column from where you want to remove duplicate values. Go to Data then select the Remove Duplicates option in the Data Tools group.

Data → Remove Duplicates

Step 2:

• In the Remove Duplicates window, check on the Select All option as we want to remove the duplicate values from the entire column. Make sure to check on the Camera Model column and then click OK to continue.

• A new window appears telling that our duplicate rows are removed. Click OK to proceed

• Now we have successfully removed all the duplicate values from the Camera Model

Read More: How to Remove Duplicate Rows Based on One Column in Excel

ii. Remove Duplicates from Multiple Columns

Step 1:

• Select the whole dataset. Go to the Data tab then select the Remove Duplicates option in the Data Tools

Data → Remove Duplicates

Step 2:

• In the Remove Duplicates window, check on the Select All option as we want to remove the duplicate values from both columns. Make sure to check on all columns and then click OK to continue.

• A new window appears telling that our duplicate rows are removed. Click OK to proceed

• Now we have successfully removed all the duplicate values from both of the columns.

Read More: Excel VBA: Remove Duplicates Comparing Multiple Columns (3 Examples)

2. Use Advanced Filter Option to Remove Duplicates from Column in Excel

Step 1:

• To remove duplicate values from columns using the advanced filter, select the whole dataset, go to the Data tab, then in the Sort & Filter group, click on Advanced.

Data → Sort & Filter → Advanced

Step 2:

• In the Advanced Filter window, check on Filter the List, in-Place to filter the dataset in its current location. Select your List-Range Check on Unique Records Only. Click OK to get the result.

• Our dataset is filtered and duplicate values from columns are removed.

Step 3:

• If we want to make our unique dataset list in another place in the worksheet, just click on Copy to Another Place in the Advanced Filter Select your place by inserting values in the Copy to box. Click OK to continue.

• Our duplicate values from columns are removed and a new unique dataset is created.

Read More: How to Delete Duplicates in Excel but Keep One (7 Methods)

Similar Readings

• Excel VBA: Remove Duplicates from an Array (2 Examples)
• How to Remove Both Duplicates in Excel (5 Easy Ways)
• Remove Duplicate Rows Except for 1st Occurrence in Excel (7 Ways)
• How to Remove Duplicate Names in Excel (7 Simple Methods)
• Fix: Excel Remove Duplicates Not Working (3 Solutions)

3. Apply a Function to Remove Duplicates from Column in Excel

You can also use functions to remove duplicates from columns. The COUNTIF function can help you to find if there are any duplicates in the columns. Then you can filter the result to remove those duplicates from columns. Let’s follow these steps below!

Step 1:

• Create two new columns named Combined Text and Count in the existing dataset. Here we will apply the formula and filter the duplicate values.

Step 2:

• In cell D4 of the Combine Text column, apply this formula,

• This formula will add cells B4 and C4

• Press Enter to apply the formula and copy this formula down to the last cell.

• Now we will apply the COUNTIF function in cell E4. The formula is

• Where D$4:D4 is the range and the criterion is D4.

• Get the result by pressing Enter. Apply the same function to the rest of the cells. If the function returns more than one value then the value is a duplicate. That’s how we will find out the duplicates in the dataset.

Step 3:

• So we have got the status of our dataset. Now we will filter the dataset. Click on the Count column header and go to Sort & Filter in the Editing Group and click on Filter

Home → Sort & Filter → Filter

• Now we have the filter option in every column of our dataset.

• Click on the drop-down filter icon in the Count From the given option, uncheck 2 to filter the dataset, and click OK.

• We have successfully removed the duplicates from the columns.

Read More: How to Remove Duplicates in Excel Using VBA (3 Quick Methods)

Things to Remember

👉 When you select your Range, you have to use the absolute cell references ($) to block the array.

👉 You can filter your data in other places in the worksheet while using the Advanced Filter option.

Conclusion

Removing duplicate values from columns in excel is quite easy if you follow the procedures we discussed in this article. We hope this article proves useful to you. If you have any ideas or suggestions, you are welcome to share your thoughts in the comment box.

Related Articles

• How to Remove Duplicates and Keep the First Value in Excel (5 Methods)
• Excel Formula to Automatically Remove Duplicates (3 Quick Methods)
• How to Remove Duplicates Using VLOOKUP in Excel (2 Methods)
• Remove duplicate rows based on two columns in Excel [4 ways]
• How to Remove Duplicates Based on Criteria in Excel (4 Methods)