[ad_1]
MS Excel Tips – How To Filter And Remove Duplicate Values In Excel
A duplicate row in Excel is one where all data in cells match of all data in another row. Duplicate values in cells are determined by the value displayed in the cell, but not stored in the cell for example one cell that has 01/02/2011 in it compared to February 01 2011 are not duplicated, but classed as unique values.
There a two ways to filter for unique values or remove unwanted information.
Both of these tasks are very similar and give the same results, one just hides the duplicate values (filtering) and the other deletes the duplicates permanently.
I suggest you conditionally format unique items in your Excel worksheet to ensure the results are what you expect before attempting to remove any of your data. This is a useful step which I always carry out and is really easy in Excel 2007 onwards:
- Highlight the data set you want to search for duplicate items in
- Home Tab
- Styles Section
- Conditional Formatting
- Highlight Cell Rules
- Duplicate Values
- In the duplicate values dialog box select how you want to highlight your cells with duplicate values
- Hit Ok
You can now easily see your duplicates before you attempt to temporarily filter or permanently delete them.
To filter or temporarily remove duplicate values we use the filter for unique values function.
- Select the range of cells
- Data Tab
- Sort and Filter
- Advanced
- In the Advanced Filter Dialog Box
- Chose either Filter the list in place OR copy results to another location in this case you need to enter into copy to box a cell reference or hide the dialog box and click into a cell on your worksheet then select the expand dialog box
Your unique values will now be filtered either in place, or to the location of your choosing.
If you choose to remove the duplicate values rather than just hide them by filtering then it is always a good idea to make a copy of your data set as data will be permanently deleted from your worksheet.
To permanently delete duplicate data:
- Select the range of cells
- Data Tools Group
- Data Tab
- Click Remove Duplicates
- Under Columns select one or more columns
- To quickly select all of your available columns click Select All
- To quickly clear all of your available columns click Unselect All
- Click Ok
- You will be notified by Excel how many duplicate values have been deleted
- You will be notified also by Excel how many unique values remain in your data set
- Click Ok
[ad_2]
Source by BJ Johnston