Although Microsoft Excel is a widely used programme, removing and getting rid of duplicate data can be a little confusing. Working with large datasets frequently involves removing duplicates in Excel. Repeated entries may appear in your spreadsheet when you merge separate tables or when numerous users have access to the same document.
The data would thus be unnecessary. The likelihood of finding duplicate records increases with dataset size. If they are not recognised and dealt with appropriately, it may be problematic.
Using a Sports dataset, we will demonstrate how to eliminate duplicates in Excel. The Olympic medalists for the year 2012 are listed in this dataset.
Locate and Eliminate Duplicates
While duplicate data can occasionally be helpful, it usually only makes the data more difficult to analyse. Better than eliminating all of the duplicates at once is finding, highlighting, and reviewing them first.
How to Remove Duplicate Values in Excel
A built-in feature of Excel makes it simple to remove redundant data points from your dataset. Let’s look at the procedures to be followed in order to eliminate duplicates in Excel.
Step 1: To start, click on any cell or a particular range in the dataset that contains duplicate data. Excel will choose the range for you automatically in the following step if you click just one cell.
Step 2: Next, find and pick the “Remove Duplicates” option.
Remove Duplicates is a part of the Data Tools tab.
Step 3: A dialogue box displays, as illustrated below. The columns you want to compare and look for duplicate data in can be chosen.
If your data contains column headers, check the box next to “My data has headers,” then click OK.
The first row will not be taken into account for deleting duplicate values when the header option is checked.
Step 4: At this point, Excel will eliminate the redundant rows and present a dialogue box. Along with the count of unique values, the dialogue box displays a summary of how many duplicate values were discovered and eliminated.
Step 5: the duplicate records have been deleted.
Let’s proceed and learn how to use Excel’s Advanced Filter feature to eliminate duplicates.
Recognize how To Remove Duplicate Values or Perform Filtering for Unique Values
You can either delete duplicate values or filter for unique values with the goal of obtaining a list of unique values. The duties all have the same goal in mind. There is, however, a significant distinction. When you filter for unique values, duplicate values are momentarily concealed, but if you choose to remove duplicate values, they are permanently removed.
Also Read: How to Merge Cells in Excel: Quick and Easy Guide to Excel Cell Merging
Furthermore, it is critical to realise that a comparison of duplicate values is based on the contents of the cell rather than its underlying value. When two cells with the same date value are formatted as “3/9/2022” and “Mar 9, 2022,” for example, those two values are taken to be separate values.
Make it a habit to check before deleting duplicates as a result. To obtain the desired outcomes, try filtering or conditionally formatting unique values.
Apply a Unique Values Filter
To search for unique values, take the following actions:
Selecting the range of cells is the first step. Make sure the active cell is in a table if you want it to work.
The Advanced filter option must then be found and chosen in the Sort & Filter group.
Advanced in the Sort & Filter section of the Data tab.
A popup box for the Advanced Filter will appear on your screen. One of the following options is available to you:
Click Filter the list in place to filter the current set of cells or tables.
Take the following actions if you need to copy the filter’s results to another location:
To copy the values, select “Copy to another location.”
Enter the cell reference where the output values need to be transferred in the “Copy to” box.
To temporarily hide the popup window, select the “Collapse Dialog” option ().
Then, after choosing a worksheet cell, select “Expand” from the menu that appears ().
Click OK after selecting the “Unique records only” checkbox.
Using the Advanced Filter Option
You can remove duplicate data using Excel’s Advanced Filter feature and copy the unique values to another location. To learn how to use the Advanced Filter option, look at the steps below.
To start, select the dataset’s cell or range from which duplicates should be eliminated. When you select Advanced Filter and click on a single cell, Excel instantly determines the range.
Identify the Advanced Filter menu item.
Select the Advanced option under the Sort & Filter section on the DATA tab.
A dialogue box will appear. It consists of a menu of advanced filtering choices.
Select the ‘Copy to another location’ option to copy the unique values to a different location.
Verify that the records’ range in the “List Range” field corresponds to the range you specified.
Enter the range where the resulting unique values must be transferred in the “Copy to:” field.
Select “Unique records only” in the checkbox. The most important step comes next.
Select OK.
The unique values will be copied to cell G1. \sadvanced filter 4
These are the built-in Excel functions that assist us in deleting duplicates. Let’s move on and discover how we may design our own function to accomplish the same thing.
How Can I Remove Duplicates in Excel Using Formulas?
To illustrate this strategy, we’ll use a straightforward example using the columns “kind of sport,” “athlete name,” and “medal won.”
In this approach, the columns are combined using an Excel formula to determine the count. The duplicate values will then be filtered out (ones that have a count greater than 1).
Let’s use the concatenation operator “&” to merge the columns A, B, and C. The Excel formula would be =A2&B2&C2 as a result.
The cell D2 is used to insert the formula, which is then copied down to all the rows.
To identify the duplicates in Column D, we will now require a new column called “Count.” Therefore, on cell E2, we apply the COUNTIF function. It will be written as =COUNTIF($D$2:D2,D2)
The frequency of each value in column D is counted using this algorithm.
If Count has the value “1,” then the item has only ever existed once and is unique. Duplicate values are those that have a value of “2” or higher.
Selecting the Filter option will now add a filter to the Count column.
It is located in the Sort & Filter section under the DATA tab.
In Column E, select the filter at the very top. To maintain only the distinct values and discard duplicates, select “1”.
The duplicate values will be eliminated from the table after you click OK. You can copy these consequent unique records and paste them elsewhere.
Let’s go on to learn the last method for removing duplicates: Power Query is used.
Duplicate Removal from Outlined or Subtotaled Data Issues
The inability to eliminate duplicate values from outlined or subtotaled data is a typical issue when eliminating duplicates. You must eliminate both the outline and the subtotals in order to get rid of duplicates.
Also Read: How to Password Protect an Excel File: Step by Step Guide to Secure a File
Format Unique or Duplicate Values Conditionally
Conditional formatting makes it easier to locate and identify duplicate content. Excel cannot, however, highlight duplicate values in the Values section of the PivotTable report. For other places, follow the methods given below:
- Start by choosing the cells whose values you want to compare for duplication.
- The Duplicate Values option can be found under conditional formatting in the Home tab.
Home tab Highlight Cells Rules Duplicate Values Conditional Formatting.
- A popup window will display once you select the Duplicate Values option.
- Select the formatting for the duplicate values from the drop-down menu in the box next to the “values with” option. then press OK.
The sheet will highlight the duplicate values. Before using the remove duplicates tool, keep in mind that all the duplicate data will be permanently deleted. To prevent the unintentional loss of any information, it is therefore advisable to copy the original data to a different worksheet.
Take the Following Actions to Eliminate Duplicate Values Right Away:
- Select the range of cells containing duplicate values you want to eliminate.
- Next, find and pick the “Remove Duplicates” option.
Data Tools > Remove Duplicates is located in the Data tab.
- Check or uncheck the columns where you want to eliminate duplicates under Columns. Next, select OK.
For instance, in the spreadsheet that follows, we want to maintain the January pricing information column.
Therefore, under the Remove Duplicates box, uncheck the January checkbox.
Except for the columns that are not checked, all other duplicates will be deleted.
How to Remove Duplicates in Excel Using the Power Query Tool
You can import data from several sources, clean up your data, and change it using Excel’s Power Query feature. Duplicates in Excel may be easily removed using this tool.
Choose a cell or range, then find the Data Tab’s Get & Transform Data section and click on it.
You will get a dialogue box to create a power query table when you click. Verify that the supplied range of values is accurate. Select OK.
The Power Query editor window that follows appears.
You now have two choices. Based on, you can eliminate duplicates:
A single column or more
complete table
Right-click on the header of the desired column to eliminate duplicates based on one or more columns. Using the CTRL button, you may select many columns at once and delete any duplicates as necessary.
Click the button located in the upper left corner of the data preview to eliminate duplicate records based on the full table. After that, choose “Remove Duplicates” from the menu.
The data will be free of duplicate values in this fashion.
When you select “Close & Load,” the information is loaded onto your spreadsheet.
Are you hoping to advance your business analysis career? Enroll in our special PGP In Business Analytics course to learn how to efficiently resolve complicated business issues!
Conclusion
This article taught us several methods for deleting duplicate records. There is a free course on Simplilearn called “Business Analytics with Excel” that you may take if you’re interested in learning the fundamentals of Microsoft Excel or in extending your knowledge of the programme.
Removing duplicates in Excel is a laborious process, so we sincerely hope this article has given you some tips on how to do it effectively.
Any queries can be posted in the “How to Remove Duplicates in Excel? : A Step-By-Step Guide” article’s comments area. Our experts will respond to you as soon as possible on the subject.
For More Information Visit Our Site: https://www.techllog.com/
