Browse » Home
Sunday, May 13, 2012
How to Get a List of the Unique Values in an Excel Spreadsheet
Filtering
1. Highlight the cells you wish to filter for unique values.
2. Click the 'Data' tab and select 'Sort Filter,' then 'Advanced.' Inside the box that pops up, choose 'Filter the list, in place' if you wish to filter the data within the spreadsheet. You can also copy the filtered list into a new table by clicking 'Copy to another location,' then selecting the cell range you wish to paste by entering the range in the 'Copy to' field.
3. Choose the check box for 'Unique records only,' then click 'OK' to generate the list of values.
Removing Duplicate Values
4. Highlight the cells from which you wish to remove duplicate values.
5. Navigate to the 'Data' tab and select 'Remove Duplicates,' which is located in the data tools group.
6. Select one or more columns under the 'Columns' section in the pop up box, based upon those columns from which you wish to remove duplicates. Click 'OK.'
7. A message will display showing how many values were removed from the data table and how many unique values are left. Document the information you need, then click 'OK' to view the table.
Applying Conditional Formatting
8. Highlight the cells to which you wish to apply conditional formatting for unique values.
9. Select 'Conditional Formatting' under the 'Home' tab and within the 'Style' group,
10. Choose 'Manage Rules,' then select 'New Formatting Rule.'
11. Ensure the cell range and worksheet displayed in the 'Show formatting rules for' box are appropriate. Then select 'Edit rule.'
12. Select 'Format only unique or duplicate values.' in the Edit Formatting Rule box that pops up. In the 'edit the rule description' field, choose 'Format all' as the list and then choose 'unique' or 'duplicate,' depending upon which items you want to highlight. Choose the type of formatting you want (i.e., color, font, fill) then click 'OK.'