How To Count Unique Values In Excel

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 distinct/unique data like products/employee/companies/cities etc. just use this trick.

You can also count distinct values in pivot table.


You can download the Template here – Download
Previous articleAvoid Errors Using IFERROR-Everyone Should Know
Next articleLinking Text Box To A Specific Cell