How to Delete Duplicate Columns in Excel (6 Ways)

Microsoft Excel is an extensively used program, but when it comes to deleting and eliminating duplicate data, it can be a bit complicated. When working with large datasets, eliminating duplicates in Excel is a common task. You can wind up with duplicate entries in your spreadsheet if you merge different tables or if multiple users have access to the same document. As a result, the data becomes obsolete. The greater the dataset, the more likely duplicate records will be found. It can be a concern if they aren’t discovered and dealt with properly. In this tutorial, We’ll demonstrate to you how to delete duplicate columns in Excel.


Download Practice Workbook

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


6 Suitable Ways to Delete Duplicate Columns in Excel

Duplicates in Excel can be extremely inconvenient. Duplicate data can always seep in, whether you import it from a database, obtain it from a colleague, or compile it yourself. And if the data you’re working with is large, finding and removing duplicates in Excel becomes extremely tough.

Therefore, we’ll illustrate to you 6 best-fit ways to delete duplicate columns in excel.

Featured Image


1. Use Remove Duplicates Command to Delete Duplicate Columns in Excel

Excel offers a built-in function that can help you remove duplicate entries from your spreadsheet. Let’s have a look at the procedures for removing duplicates using the Remove Duplicates command.

Step 1:

  • Select the columns within range with the header.

Suitable Ways to Delete Duplicate Columns in Excel

Step 2:

Use Remove Duplicates Command to Delete Duplicate Columns in Excel

Step 3:

  • Make sure My data has header option is marked
  • Mark the columns you want to find duplicate entries.
  • Press Enter or select the OK

Use Remove Duplicates Command to Delete Duplicate Columns in Excel

Step 4:

  • Excel will display the number of duplicate values found, press OK to the results.

Use Remove Duplicates Command to Delete Duplicate Columns in Excel

Therefore, you can see that the duplicate values in the columns are deleted.

Use Remove Duplicates Command to Delete Duplicate Columns in Excel


2. Apply Advanced Filter Feature to Delete Duplicate Columns in Excel

One of the most underutilized functions in Excel is the Advanced Filter. I am sure you have used Excel if you work with it. It filters a data set fast based on a selection, specified text, number, or other criteria. Let’s look at how to use the Advanced Filter command to remove duplicates.

Step 1:

  • Select the columns within range with the header.

Use Remove Duplicates Command to Delete Duplicate Columns in Excel

Step 2:

  • Click Advanced in the Sort & Filter group on the Data tab.

Apply Advanced Filter Feature to Delete Duplicate Columns in Excel

Step 3:

  • Select the Filter the list, in-place option.
  • Select the List range where you want to find the duplicates.
  • Mark Unique records only.
  • Click OK to see the results.

Apply Advanced Filter Feature to Delete Duplicate Columns in Excel

Consequently, you will see that Row 9 will be removed with a duplicate value from the columns.

Apply Advanced Filter Feature to Delete Duplicate Columns in Excel

Note: You may choose either Filter the list in place or Copy to another location. Filtering the list in place will hide duplicate rows, however, copying the data to another location will copy the data.


3. Use Conditional Formatting to Delete Duplicate Columns in Excel

Conditional formatting facilitates the visual exploration and analysis of data, the detection of key concerns, and the identification of patterns and trends.

Here, we’ll use Conditional Formatting to identify the duplicates at first. Then, we’ll delete the duplicates we need.

The benefit of using Conditional Formatting is that we can mark the duplicates firstly. We may don’t need to remove all the duplicates at a time. So, we can choose the data we want.

Step 1:

  • Select the columns within range with the header.

Apply Advanced Filter Feature to Delete Duplicate Columns in Excel

Step 2:

  • Select Conditional Formatting from the Home tab.

Use Conditional Formatting to Delete Duplicate Columns in Excel

Step 3:

  • Select Highlight Cell Rules.
  • Select Duplicate Values.

Use Conditional Formatting to Delete Duplicate Columns in Excel

Step 4:

  • You have the option of highlighting Duplicate or Unique data. Here, choose the Duplicate

Note: To highlight the values, you can choose from a number of predefined cell forms or design your own custom format.

Use Conditional Formatting to Delete Duplicate Columns in Excel

As a result, we’ll see highlighted values with duplicate entries.

Use Conditional Formatting to Delete Duplicate Columns in Excel

Step 5:

  • Select the columns in the range.

Use Conditional Formatting to Delete Duplicate Columns in Excel

Step 6:

Use Conditional Formatting to Delete Duplicate Columns in Excel

Step 7:

  • Choose the Select All
  • Press Ok.

Use Conditional Formatting to Delete Duplicate Columns in Excel

Step 8:

  • Press Enter or Ok

Use Conditional Formatting to Delete Duplicate Columns in Excel

Therefore, we’ll see the results shown below.

Use Conditional Formatting to Delete Duplicate Columns in Excel


4. Use Power Query to Delete Duplicate Columns in Excel

Because Power Query is all about data transformation, you can rely on it to detect and eliminate duplicate values.

Step 1:

  • Select the columns in the range.

Use Conditional Formatting to Delete Duplicate Columns in Excel

Step 2:

  • Go to the Data tab and select From Table/Range.

Use Power Query

A Power Query Editor box will appear. You can now change what type of data transformation you want.

Step 3:

  • Hold Ctrl and select the columns by clicking on the header.

Use Power Query

Step 4:

  • Right-Click and select the Remove Duplicates.

Use Power Query

As a result, we’ll see that there will be a table that appears with the deleted duplicate data.

Use Power Query


5. Run A VBA Code to Delete Duplicate Columns in Excel

The concept of removing duplicates can also be used in VBA.

In the following section, I’ll show you how to use VBA to delete duplicate columns in Excel. To write the code on your own, carefully follow the steps.

Step 1:

  • Select the columns in the range.

Use Power Query

Step 2:

  • Type ALT+F11 to open the VBA.
  • Select Insert at first.
  • Then, select Module to open the coding window.

Run A VBA Code

Step 3:

  • Paste the following VBA
Sub RemoveDuplicates()
Dim DuplicateValues As Range
Set DuplicateValues = Selection
DuplicateValues.RemoveDuplicates Columns:=Array(1, 2, 3), Header:=xlYes
End Sub

Run A VBA Code

  • Save the codes.
  • Then, press F5 or click Run to run the codes.
  • Consequently, you will the result is shown below.

Run A VBA Code


6. Use the COUNTIFS Function to Delete Duplicate Columns in Excel

The COUNTIFS function in Excel calculates the number of cells that fulfill one or more conditions. The COUNTIFS function can handle criteria based on dates, numbers, text, and other variables.

We’ll use the COUNTIFS function to count the duplicate values to delete those from Excel.

Step 1:

  • Select cell E5 to combine the values of B5, C5, and D5.
  • Type the following formula,
=B5&C5&D5
  • Press Enter and drag the Auto-Fill.

Use the COUNTIFS Function

Step 2:

  • In cell F5, type the following formula,
=COUNTIFS($E$5:E11,E5)
  • Press Enter.

Use the COUNTIFS Function

In the below image you can see that the columns with duplicate values count two times. So, you will just delete one entry to make count it one.

Use the COUNTIFS Function

Step 3:

  • Delete the duplicate values and see the results.

Use the COUNTIFS Function

Therefore, you can see that the entries in different columns count as unique.


Conclusion

To conclude, I hope this article has given you some useful information about how to delete duplicate columns in Excel both in various ways. All of these procedures should be learned and applied to your dataset. Take a look at the practice workbook and put these skills to the test. We’re motivated to keep making tutorials like this because of your valuable support.

If you have any questions – Feel free to ask us. Also, feel free to leave comments in the section below.

We, The Exceldemy Team, are always responsive to your queries.

Stay with us & keep learning.

Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo