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.

Apply the Remove Duplicates Feature to Remove Duplicates from Single Column


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

Apply the Remove Duplicates Feature to Remove Duplicates from Single Column

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.

Apply the Remove Duplicates Feature to Remove Duplicates from Single Column

Apply the Remove Duplicates Feature to Remove Duplicates from Single Column

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

Apply the Remove Duplicates Feature to Remove Duplicates from Single Column

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

Apply the Remove Duplicates Feature to Remove Duplicates from Multiple Columns

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.

Apply the Remove Duplicates Feature to Remove Duplicates from Multiple Columns

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

Apply the Remove Duplicates Feature to Remove Duplicates from Multiple Columns

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

Apply the Remove Duplicates Feature to Remove Duplicates from Multiple 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

Use Advanced Filter Option to Remove Duplicates from Column

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.

Use Advanced Filter Option to Remove Duplicates from Column

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

Use Advanced Filter Option to Remove Duplicates from Column

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.

Use Advanced Filter Option to Remove Duplicates from Column

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

Use Advanced Filter Option to Remove Duplicates from Column

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


Similar Readings


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.

Apply a Function to Remove Duplicates from Column

Step 2:

  • In cell D4 of the Combine Text column, apply this formula,
=B4&C4
  • This formula will add cells B4 and C4

Apply a Function to Remove Duplicates from Column

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

Apply a Function to Remove Duplicates from Column

  • Now we will apply the COUNTIF function in cell E4. The formula is
=COUNTIF(D$4:D4, D4)
  • Where D$4:D4 is the range and the criterion is D4.

Apply a Function to Remove Duplicates from Column

  • 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.

Apply a Function to Remove Duplicates from Column

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

Apply a Function to Remove Duplicates from Column

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

Apply a Function to Remove Duplicates from Column

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

Apply a Function to Remove Duplicates from Column

  • We have successfully removed the duplicates from the columns.

Apply a Function to Remove Duplicates from Column

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

Asikul Himel
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo