Qicker way to find unique items than Autofilter

Excel Autofilter feature is one of most useful of this popular application. Whenever you click filter arrows (Or by shortcut Alt+Down Arrow ) excel shows the the items of that column. It contains each item once so you are confident that these are only available in this column

See the red eclipse below with corresponding list at left.

But if there is any cell which has more than 255 character then it will not be shown in autofilter. See pic below..

 

 We many time decide on basis Autofilter list. It is most of the time very effective but if cell has more than 255 characters then it will not appear in autofilter.

And another problem is if list is lengthy with many rows excel autofilter will take lot of time to prepare a unique list.

Some quick work arounds are...

One option is doing pivot of the list which will show only unique items.

Or copy the list to another place and use remove duplicate on the copy. It will remove all duplicates

Or you can use Excel's Advanced filter by checking "Unique Records". This is most simple way.Below is details..

First select list. Goto Data tab, then Advanced under "Sort & Filter" group. Check the dialog box. Most of the time excel guess correctly "List Range" but it may be wrong. Be sure that address shown in "List Range" is actually address of your list. Put Check on "Unique Records Only". Do nothing with 1st Option button group. Default is "Filter The List, in-place" and "Criteria Range". See the pic below..

The above will help in getting unique items of list in another scenario.

If any list contains more than 10000 unique items, autofilter will show only first 10000. Note: your list may be more than 10000 but it may contain less unique items. Suppose your list is simple arithmetic series of 1,2,3...upto 20000. Then autofilter will show you till 10000. And lot of time will pass before autofilter shows unique list to you.



Hope this helps...All the best wishes...