Excel Count Unique Values: Distinct Data Identification

How To Count Unique Values In Excel

Have a data with duplicates.

Select the range of cells, or have it all in a table.

Click on Data Tab (1), and choose Advanced (2) in Sort & Filter.

Click yes

Click copy to another location (1), where you want to copy it to (2), check on the unique records only (3), and click ok.

List Range: =&A&1:&A&14

Note: If the cells you choose in number 2 isn’t large enough to contain all the unique numbers, Excel would automatically enlarge it.

Unique Values:

As you can see there is a possible that data in your tables or reports will be duplicated. To extract only distinct/unique data like products/employee/companies/cities etc. just use this trick.

You can also count distinct values in pivot table.

Template

Further reading: 
Basic concepts 
Getting started with Excel
Cell References