Duplicate values usually occur when the same values or a set of values appear in your excel worksheet data. Therefore, duplicated data is somehow common in excel worksheets. At times it may be useful. Sometimes it may prove unnecessary as it may make it harder for other users to understand it. Most times, having duplicate values can lead to errors and over-estimation of your final results. Luckily, Excel offers its users ways in which they can easily review their duplicates and delete them.
Using conditional formatting to remove duplicates
1. In your open excel worksheet, select all the cells you want to check for duplicates.
2. Go to your Home tab and click on Conditional Formatting.
3. On the drop-down list, select Highlight Cells Rules > Duplicate Values.
4. Doing so will display a dialog box named Duplicate Values. In this box, next to 'values with,' select the formatting you want to apply to your duplicates.
5. Click OK.
Note;
In case you have a Pivot Table report, excel cannot highlight the duplicate values.
Using the Remove Duplicates command feature in Excel
Using this feature permanently deletes your duplicate data. It is recommended to copy the original data to another worksheet before using this method.
1. First, select the range of cells containing the duplicated values you want to be removed.
2. Go to the Data tab
3. Under the field of Data Tools, click on the option Remove Duplicates.
4. Under Columns, check and uncheck all the columns you want to remove the duplicates.
5. Click OK.
Using the Advanced Filter feature
1. Select the range of cells you want to remove the duplicates.
2. Go to the Data tab.
3. Under the Sort & Filter group, click on the option 'advanced filter.'
4. Doing this will open up the Advanced Filter pop-up window. Here, you select your preferences. You can either choose to filter the list in place or copy it to another location. Note, excel will randomly guess the range of data. Therefore, you can adjust this range in the List range section. In case you chose the option Copy to another location, you will need to fill in the section' Copy to.' Afterward, check the box for 'Unique records only.'
5. Press OK to eliminate all the duplicates within your worksheet.
Using a Pivot Table to remove duplicates
A Pivot Table can be used to display only the unique values within your data sets rather than removing the duplicates. First of all, you will need to create a table. Here are the steps to follow;
1. Select a cell within your data. You can also select the entire range of data in your worksheet.
2. Go to the Insert tab.
3. Select PivotTable.
4. Press OK to create your PivotTable. The table will be blank. Add all the fields into the Rows area of the pivot table. Afterward, change the table to be in tabular format and remove any subtotals.
5. Your Pivot Table will now be in tabular format and display unique valuesfor items in the Rows area. Therefore, any duplicates will not be displayed.