Duplicates are common mistakes that most people make on their Excel worksheets during data entry. Duplicates in your data can lead to misinterpretation and wrong forecasting of data; they affect data analysis greatly.
Removing duplicate entries is quite a simple process in Microsoft Excel. From a given set of data, you can identify in so many ways. The following methods will guide you on how to remove repeated values on your worksheet.
How to Find and Remove Duplicates with the Remove Duplicates Command
- Click and select any cell within any given set of data.
- Go to the Data tab; it is in the data tools group.
- Click on “Remove Duplicates.”
- Once a dialogue box appears, do not do anything. Leave all the checkboxes checked.
- Click Ok
- Excel will automatically remove all duplicate rows and leave one behind.
Excel will always give you a summary of the number of duplicates that you remove and those that remain. Remember, you need to notify Excel on the columns to use when finding the repeated values. If you have a long list of columns in your data, the Select All and Unselect All buttons can be of great use.
How to Find and Remove Duplicate Values with Advanced Filters
- Select any cell within a given set of data
- Navigate to the Data tab and click on Advanced filter
- Once the Advanced filter window opens, you are supposed to choose whether to “Filter the list in space” or to “Copy to another location.” Usually, filtering will hide the duplicates, and copying will create a new version of the values.
- Normally, Excel guesses the range of data though you can still adjust it in the Lit Range. You can leave the Criteria range unfilled.
- In case you select Copy to another location, you will file the Copy to space.
- Click the box for Unique records only to check and agree.
- Click Ok to eliminate all the duplicate values on your worksheet.
How to Find and Remove Duplicate Values Using a Pivot Table
Pivot tables are not only used to analyze data but also to remove repeats as well. Here is how to use a pivot table to remove duplicates.
- Start by creating a pivot table based on your data.
- Select a given range of data on your worksheet.
- Go to the Insert tab and select the Pivot table.
- Click ok in the create pivot table dialogue box. A blank pivot table appears; add the required fields in the rows of the pivot table.
- Go to the design tab. Select report layout to change the design of the pivot table into a tabular format.
- Removing sub-totals is a crucial step too. To remove subtotals, move to the design tab and select sub-totals. Click on Do Not Show Sub-totals
- Since pivot tables only list unique values for a given set of data, all the duplicates will remove automatically.
Duplicate sets of values in your data can become annoying; they are an obstacle to achieving accuracy. Fortunately, there are so many ways of removing duplicates when using Microsoft Excel.