How To Find Unique Values Using Advanced Filter In Excel

In this video I explain how to create a list of unique values with the Advanced Filter feature in Excel. This is a follow-up to a previous video where I shared 2 other ways to list unique values using the Remove Duplicates feature and a VBA macro.

The Advanced Filter feature of Excel has an option called Unique Records Only that creates the list. We can take a column of cells that contains duplicate values, and create a list of the unique values only in another column on the sheet.

The disadvantages of the Advanced Filter technique are the output must be on the same sheet as the data range, and the formatting is not pasted to the output. Otherwise, it's another great option for creating a list of unique values and removing duplicates.

Please leave a comment below with any questions or suggestions for other ways to create a list of unique values.

[stevenshih]

Thank you very much! Simple but since people like you spend your time recording to educate us. It truly helps a lot. Video with example is always more intuitive

[sharonjones]

did start with this and now I've seen =UNIQUE so you can use it on another tab, just trying to see if I can filter it on another column this is proving a little tricky - JUST SEEN YOUR NEW VIDEO!!!! BRILLIANT HELPED ME LOADS

[kevinrichard]

This is an amazing method. It really helped me to get out of a big situation at work. Thanks a lot

[chrismeredith]

Thanks so much mate. This is an enormous help. I have liked the video and subscribed to your newsletter. Thanks again.

[AllergicToAshAndDust]

This is exactly what I needed! Thank you so much!

[manasranjanjena]

After hovering around here n there finally I found this useful tutorial. Thanks bro

[Billyboy]

YOU HELPED ME ALOT MAN. EASY TOOL USE INSTEAD OF A FORMULA.. THNX A LOT

[danielifrim]

Great, Great, amazing! You re doing a wonderful job! Thanks a lot

[RonWilliams-Hoodle]

Outstanding! This will save me tons of work.

Looking forward to learning more.

Thanks very much!

Ron

PS: Am not seeing the link for the macro download. Will head to your website & see if I can find it there. Thanks again.

[manishupadhyay]

Woow, I love you. Amazing, woow man I love you the way you teachs

[mohitverma]

Hi there,
I’m a subject matter expert on Excel. I’ve a suggestion here when you mentioned that we can’t have the unique list on other worksheet using Advanced Filters.
You may go to a new sheet and start applying the Advanced Filter from that blank sheet. Now select the Table range from the sheet where your table lies. Keep copy to another location radio button selected.
Finally put the new sheet cell selection in Copy To section and you are good to go.
Unique entries will get pasted. I hope this helps. Thanks! :)

[shashicreativethings]

REALLY ITS HELPFUL AWESOME VIDEO THANK YOU SIR

[aloks_]

This is what i've been looking for! Thanks mate!

[salmabanawan]

Hi, this was so informative!! I want to know how I can select the data from more than one column and extract the unique entries only.

[ranjithrash]

Interesting bro. Keep posting such type of videos

[DaveH]

Hi Jon, another possible solution in Excel 2016 would be to use Power Query.

[aslammsalam]

Very useful and used it right now. Can you show us how to add year to date such as Mar 27 or Apr 1. I want these dates to convert to DD/MM/YYYY such as 27/03/2019 and 01/04/2019.
Thanks

[eyaldavid]

Thank you very much Jon! :) <3
One Question please:
I've got a table with the following:
Running number (1-100), Date of sale, Customer name, sales value etc).
I want to sum all the sales by dates. For example:
If I have 3 sales on a specific day, I'd like to see 1 line with the date that sums the amount of sales and their total sum.

My problem is:
I am able to sort unique all but the 1st day - it shows it as 2 different days.. despite the fact that i had copy-paste from 1st to 2nd row in order to verify that values are the same.
Have you got any idea why and/or how to fix it please? :)
Thanks in advance & Best Regards from Israel^^
Stay safe <3

[mansoorali]

sir, is there any similar technique, which can be applied in Numbers in Mac?

[wassup]

Thank you so much I have been struggling to do this and finally you make my problem solve lol that was really exhausting