Office Tips 001: Excel, Find Unique Value

The following shows the fastest way (probably!) to filter out the unique values in a list that contains duplicates, and to count the number of occurrences of each unique value.

(Excel Version: Office 2013).

Find Unique Values

  1. Select the data to be filtered.


    Fig 1. A column of names.

  2. Go to DATA tab, in the Sort & Filter section, click on Advanced.


    Fig 2. Select filter tool.

  3. In the Advanced Filter options,
    – Select Copy to another location
    – Select the cell to copy the unique values to, using the Copy to: option
    – Check Unique records only
    – Click OK


    Fig 3. Adjust Advanced Filter options.

  4. Ta-Da! A list of unique values will appear at the cell that you have selected.


    Fig 4. Unique values output.

Number of occurrences for each value

For this to work, you will have to modify the data sheet a little bit and make use of a pivot table.

  1. Create a column of a single value “1” beside the column of data.


    Fig 5. Add a count beside each value.

  2. Go to INSERT tab, in the Tables section, click on PivotTable.


    Fig 6. Select table tool.

  3. In the PivotTable options,
    – Under Choose the data that you want to analyze,
    – Select Select a table or range
    – Select both column of data, using Table/Range: option
    – Under Choose where you want the PivotTable report to be placed,
    – Select Existing Worksheet
    – Select a cell to output the results, using Location: option


    Fig 7. Adjust PivotTable options.

  4. Go to PivotTable Fields that appear on the right of the window,
    – Under Choose fields to add to report: option,
    – Check both check-boxes that belong to the header of your data columns


    Fig 8. Display PivotTable fields.

  5. There! You now have a pivot table with the number of occurrences for each value in your data.


    Fig 9. PivotTable output.

These are definitely not the most elegant solutions, but it will solve your immediate problems.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s