Duplicate record in your data is the last thing that you want, but do not worry because here, you will learn how to remove duplicates in excel.
How to Remove Duplicates in Excel with Duplicate Command
If you have a grip on Excel, then it means you have sorted half of your works. But most of the users find it difficult how to remove duplicates in excel? Here you will learn how to remove duplicate records in excel using the command.
Removing the duplicate record is a common and easy task. In the ribbon, you will find the dedicated command option.
For this, you need to highlight the cell that you want a check for the Duplicate and need to remove it. check screenshot how to remove duplicates in excel
2. Once you select the desired cell and then go to the option Data.
3. Form the Data option the option Remove Duplicate.
4. After that, the excel office chooses the selected cells and in the pop-up window: Remove Duplicate.
In Pop Window:
- After that, you must inform the Excel about the data contain the column headers in the first row. If it is checked, then data of the first row is excluded, after finding remain and find the duplicate record.
- Once you have done this, you choose the columns that you would like to terminate duplicates. There is one more option for the beginner that is Unselect all and Select all.
- After this, you need to hit the option, OK. Press the Enter option from the keyboard. By doing this, it will remove all the duplicate content.
It is best to keep a copy of the original data because this will alter the data. Now you know how to remove duplicates in excel.
Video Tutorial: How to Remove Duplicates in Excel
If you learn more click here How To Merge Cells In Excel open the excel window and apply these methods.
How to Remove Duplicates in Excel With Advanced Filters
If you are still confused and unable to understand how to find and remove duplicates in Excel by the above-mentioned methods. Then here is the easiest way for you. Have you heard about the advanced filter, but this method you can find and remove the Duplicate? For this, you need to follow the steps mentioned below.
- First, you need to choose the cells then go to the DATA option and form the Data option click on the ADVANCED FILTER.
- After choosing the command ADVANCE FILTER, the window will pop up. From the Advance Filter Window, follow the steps mentioned below.
- Form here, you can go for either to te Filter list in place, or you can choose the Copy to other places. By filtering list in the place will hide all the rows consisting of any duplicate while it is copying to other places. It will keep another copy of the data.
- After this, the Excel office will find the data range. But can fix it in a List range. You can keep the Criteria Range BLANK and Copy to option will have to be loaded a Copy to other location was chosen
- After that, you need to check the tick box for the unique records.
When pressing the option, OK, and you will remove duo=plicate records. If you are a beginner in Excel, then the ADVANCE FILTER IS THE handy option. So now you have learned another method how to remove duplicates in excel using advance filters. If you think that method is still tricky then, move to another one.
How to Remove Duplicate Entries in Excel with the Pivot Table
If you are using Excel frequently, then you must know about the Pivot table. This Table best for data analysis. But do you know you can also find duplicates data? You must be thinking about how to remove duplicates in excel with the Pivot table.
So let’s remove the duplicate Data with Pivot Table. For this, you need to follow the steps mentioned below.
In this, you are not removing the duplicate record, but it will only highlight the unique content from the Set.
- First, you need to create the pivot table on the excel using your data. After this, you need to select the cell inside the data or the completest of the DATA.
- After that, go straight to the Insert Tab after that move to Pivot Table option and lastly press the ok option in a create PivotTable box.
- After that, in a new blank table, fill the field into Row areas of a Pivot box.
After that, you have to change the pivot layout. Once you selected the Design of the pivot table, then go to the tan Design and choose REPORT LAYOUT.
Hers are the 2 options that you have to change:
- Chose the Show ina Tabular Form
- Select Repeat All Labels
After this, you also have to erase any subtotals. After that, go straight to the TAB DESIGN, then choose the subtotal and pick DO NOT SHOW SUBTOTALS.
Now you have the pivot table that resembles the data set.
This Table only requires innovative values for the thing in a row. So this Table automatically erases any duplicate content. This method is tricky, but it will work. So here is another way for you to learn how to remove duplicates in excel.
How To Remove Duplicates in Excel with the Power Query
Do you know her is another method to learn how to remove duplicates in excel column using Power query? What is Power Query? It is about the transformation of data. So you can be sure that you can find and remove the duplicate data using the power query method. So you need to follow the steps mentioned below for this job.
- First, you need to select tables of record that you like to erase duplicates then go to the DATA tab from their pick the FORM RANGE/TABLE query.
If you are still unable to find the solution, then there is another method for you about how to remove duplicate entries in Excel.
Erase the Duplicate Record Bases on the More Columns
Do you know what the Power Query, you can erase the duplicates depending on the more or one column in tables?
For this, you need to choose the column to erase the duplicate records.
- First, you need to click right on the chosen column and pick the Remove Duplicate option from a menu.
- you can access the command from a Home Tab, then go to the Remove Rows and to the option remove the duplicates.
= Table.Distinct(#”Previous Step”, {“Make”, “Model”})
Here are the formulas that are made; it is utilizing Table. Distinct with a second referencing parameter with the columns.
How to Remove Duplicate Entries in Excel in the Entire Table
Do you want to learn how to remove duplicates in excel form the entire table? Then get ready because there is another secure method for you. To achieve this, you need to choose the steps mentioned below. It will help you to execute the command correctly. So get ready to follow the step mentioned below
- First, you need to highlight or select the columns on a table than erase the duplicate, Hey! Stop here is the best and quickest process for you.
- At the left corner is the option. From there, select the command that you would like to execute.
- Pick the button table in a left-top corner then pick the remove Duplicates.
- = Table.Distinct(#”Previous Step”)
- You can also follow the formal or save it. This formula supports the same function, Table-Distinct, with a no 2nd parameter.
Remember: Without a 2nd parameter, the formula will apply on the entire table.
Do you find this method useful? If yes, then do follow it. Excel is a simple office but needs little guidance. Now you can say you have a firm grip on the question of how to remove duplicates in excel. This method will solve your issues.
In a Nutshell
We understand the duplicate record in your data is a huge obstacle. Most of you unable to find the solution to you. But after following, the methods above, you can learn how to remove duplicates in excel like a professional. So which method works for you?
Leave a Reply