It is quite easy for you to get unique value where there are so many duplicates, especially if you would like to paste them in a new location. We are going to count unique values together.

Have a data with duplicates.


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

select range cells

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

data advanced ribbon

Click yes

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.

advanced filterList 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:

unique values

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

You can also count distinct values in pivot table.