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.
Related Articles
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.
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.
Step 2:
- Go to the Data tab and select the Remove Duplicates
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
Step 4:
- Excel will display the number of duplicate values found, press OK to the results.
Therefore, you can see that the duplicate values in the columns are deleted.
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.
Step 2:
- Click Advanced in the Sort & Filter group on the Data tab.
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.
Consequently, you will see that Row 9 will be removed with a duplicate value from the columns.
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.
Step 2:
- Select Conditional Formatting from the Home tab.
Step 3:
- Select Highlight Cell Rules.
- Select Duplicate Values.
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.
As a result, we’ll see highlighted values with duplicate entries.
Step 5:
- Select the columns in the range.
Step 6:
- Select the Remove Duplicates option from the Data tab.
Step 7:
- Choose the Select All
- Press Ok.
Step 8:
- Press Enter or Ok
Therefore, we’ll see the results shown below.
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.
Step 2:
- Go to the Data tab and select From Table/Range.
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.
Step 4:
- Right-Click and select the Remove Duplicates.
As a result, we’ll see that there will be a table that appears with the deleted duplicate data.
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.
Step 2:
- Type ALT+F11 to open the VBA.
- Select Insert at first.
- Then, select Module to open the coding window.
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- Save the codes.
- Then, press F5 or click Run to run the codes.
- Consequently, you will the result is shown below.
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.
Step 2:
- In cell F5, type the following formula,
=COUNTIFS($E$5:E11,E5)- Press Enter.
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.
Step 3:
- Delete the duplicate values and see the results.
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.
The post How to Delete Duplicate Columns in Excel (6 Ways) appeared first on ExcelDemy.
