Extract UNIQUE Items for Dynamic Data Validation Drop Down List

Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
This tutorial guides you through extracting unique values from a list of data using Excel formulas, without the need for complex Control + Shift + Enter (CSE) commands. Split into two parts, the video first demonstrates creating a list of unique items, followed by creating a dynamic drop-down list that includes only these unique items.
⬇️ Download the workbook here: pages.xelplus.com/unique-list...
🔍 Key Highlights:
▪️ Creating a List of Unique Items: Discover how to generate a unique list of divisions from a table, ensuring the list updates dynamically with any new additions.
▪️ Dynamic Drop-Down List Creation: Learn how to create a dynamic drop-down list that references your unique list, without including empty fields.
▪️ Utilizing INDEX and MATCH Formulas: Understand the efficient use of INDEX and MATCH formulas combined with COUNTIF to dynamically generate unique lists and drop-down menus.
▪️ Advanced Excel Techniques: Explore the practical application of advanced Excel techniques for dynamic data management.
As a first step I use formulas to extract the list of unique items to separate cells. My method includes a combination of the Index function together with Match and the Countif function. I'll show you a way to also avoid CSE (control shift enter) as well.
In the second part of the video, I use the Offset formula to create a dynamic list that expands depending on the number of items that should be included in the list.
The solution provided is dynamic so if you add new data or change data, everything updates automatically (we use Excel's Table feature here)
Links to related videos:
Dependent-Drop Down List (Followup): • Find Multiple Matches ...
Index Match: • How to use Excel Index...
Offset: • Excel OFFSET Function ...
This video from Kevin Lehrbass summarizes all the different techniques you can use to get the job done: • 00172 Unique Values fo...
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel

[@LeilaGharani]

Grab the file I used in the video from here 👉 pages.xelplus.com/unique-list-file

[@MySpreadsheetLab]

Thanks Leila !!

[@rahulahuja1412]

Expounded perfectly. Thank you.
But doesn't work in Excel 2007 unless I go to each cell individually and enter F2, then F9.

[@ModernPindawale]

Thank you MA'am

[@ModernPindawale]

I will do it through your suggestion

[@javadkhalilarjmandi3906]

I saw the video that you suggested, your solution is the best, fastest, simplest and very easy, really thank you. although I must add:"ExcelIsFun" was awesome too.

[@jimcrain412]

Mind Blown! You are brilliant!! Thanks for this. I have looked for more than a decade for a way to do this. This is so elegant & much less klug-y than other solutions I have found. i have gotten partway there, before. but I rarely need to work with arrays. It gets fuzzy to try to recall this stuff. Thanks, again!!

[@excelisfun]

Unique List & Sort Formulas - hardest ones around!!! Great work on the video !!!

[@rajkathare123]

ExcelIsFun
Hello Sir,
I do watch your excel videos daily...
But some time I feel irritating because I don't understand them...
But your videos are excellent 👍👍👍

[@LeilaGharani]

Thanks a lot Mike :) yes, and also fun to tackle.

[@michalroesler]

yes.

[@TravisLP27]

Best explanation of how to use OFFSET for data validation I've seen yet, well done. I've combined this with UNIQUE and FILTER for a cascading drop down based on powerquery tables. I always forget exactly how to do it and have to look it up and this is the best one I've found so far. Named Ranges help alot too. Good stuff.

[@kbjagadeesh]

OMG! how did I miss this channel, one of the best excel tutor....subscribed!

[@michalroesler]

Animal...

[@meghasyam427]

This is the most confusing topic so far and still you made it as simple as possible...Thank you so much Leila

[@sideshowbobby71]

I love how you went through your logic and thought process.

[@peterzika6535]

You do far the best thorough videos. Great presentation, explanation and really structural. I am amazed and grateful. Thanks

[@praveenk1672]

Easily the best tutorial. Though I couldn’t understand on the first instance, I just simply followed your steps and achieved the result

[@RonBarrett1954]

OMG. Nothing else just simply OMG! You have earned a new level of respect. Well done!

[@blackknight6452]

everything is great even your way of speaking is so sweet. writing "thank you" is less than what you deserve

[@DP-xz8xr]

Best video on CZcams. I have wanted to do this for years on various projects and could never get anything working, so also worked around it. Just followed your example on one of my complex solutions and works like a charm even though I'm using Mac Office365. Many many thanks. Beautifully clear example and explanation.

[@LeilaGharani]

My pleasure Darren. Glad it worked on your Mac too :)

[@mariostudio7]

This is the second time this video helps me out to solve a problem! I can't thank you enough!

[@almeidr]

Hello Leila, I'm impressed with your knowledge of Excel and explanation of things in a calm, soothing tone. I wish growing up, I had teachers that taught in this style!

[@wampage]

This is Excel brilliance. When you see the complexity of what you think is simple, you will see the simplicity of what you think is complex. Incredibly useful and flexible insight for exploiting a flatfile. Thank you!

[@LeilaGharani]

I like that sentence! I'm glad you like the video. It's fun trying to solve cases like these. I'm glad there are others like yourself who also see the beauty in solving these challenges. Thank you!

[@iziaurrahman]

I like your channel Leila. It is very easy to follow at the same time contents are very rich. Also you explain couple of different options to come to same answer. I really like it. Please keep posting new videos.

[@DrSougataBanerjee]

Every time I watch your lessons, I end up being in awe with the level of your Excel mastery and the innovative solutions you bring on the table!! Kudos to your wisdom!

[@LeilaGharani]

Thank you for the kind words. There is still so much to learn though :) that's what makes Excel fun. Thank you for your support.

[@DrSougataBanerjee]

True! 'Learning' is a never-ending journey.

[@ismailismaili0071]

It's OMG you really blow my mind the count if formula when you switch the rule of criteria as range wow i never thought of that thank you Ms. Leila

[@LeilaGharani]

You're very welcome Ismail :)

[@GeertDelmulle]

Impressive and super-smart way of tackling the problem! Love it.
And you really are the perfect Index/Match teacher - your knowledge and application of these functions is really top-notch.
Thank you for sharing...
...But I still wish for and I’m still counting the days until we get the new calc engine at work so that we can use dynamic arrays and the new functions to make this really easy to solve.

[@LeilaGharani]

Thank you Geert! I can't wait either :)

[@philipmcdonnell7168]

Another great tutorial with explanations I’ve never seen anywhere else. I have data validations with excess blanks everywhere. Those are going to be so much neater now. 👍🏻🤓

[@LeilaGharani]

Thank you Philip! Glad you like the tutorial & explanation :)

[@kennethkretschmer1027]

Thank you. Your channel has every answer I am looking for to finish setting up my accounting system. Eleven thumbs up.

[@LeilaGharani]

I'm really happy to hear that! Thanks for the thumbs up :)

[@JNguyenKnight]

Very useful. I would have thought Excel would have something like this already built in, since it's likely to be needed quite often. But obviously they don't. So I'm glad to see you take this on. Thanks Leila. I'm definitely going to make use of this one.

[@LeilaGharani]

Thanks for your comment. Yes - it would be great if the Excel team come up with a feature like this soon :)

[@alokkumar1189]

This is amazing! If you remember I had asked this same question few months ago but I think it didn't described the requirement well enough.
Thank you very much for this insightful video.
Keep Rocking

[@LeilaGharani]

Thanks Alok. Yes - Questions become videos :) Thanks for your comments and questions. They help me improve my knowledge as well.

[@kigoindonesia3195]

Thanks laila, this is good solution for everyone still using the old version of excel.

[@diannedrechsler4792]

Thanks again Leila, I was copying the formula while you were showing it. Now onto the next video to create a second dynamic list that is conditional upon the first list (depts. and sub-depts.) You made it really easy to follow - except the formula - never could have come up with that myself. So handy when you're under pressure to get things done!

[@LeilaGharani]

You're welcome Dianne - glad this helps. Don't forget, you can also download the workbooks from the link provided (you get to the blog post and then scroll right down)

[@doublehd159]

I was having so much trouble understanding this concept but you helped me finally get it so thank you!!

[@danielgarduno8067]

Omg, you are awesome. I been search for a formula like yours for about 3 months. This formula is exactly what I needed. Thank you!

[@LeilaGharani]

That's GREAT!

[@hadibq]

the array nature of countif is the unlocker here!! awesome Leila! Thanks!

[@gurneetkaur1498]

Hi Leila, I could create a Unique drop-down list, by using UNIQUE, FILTER and then created a combo box to get a Unique drop down. All thanks to your wonderful tutorials and the way you explain each and every function in detail.

[@DanielFassa]

thank you, that was what I was looking for. so much easier.

[@gyalbobhutia2825]

Thank you although I am a naive in excel but your explanation are lucid and open the thought process, please keep posting your new videos.

[@ahmedfadali4064]

Thank you very much, I used to use pivot tables, but this formula is worthy to be learned, it would be really handy when creating summary tables (similar to pivot tables) in which columns or rows are needed to be inserted and pivot table doesn't allow to do so.

[@venkatiyer7459]

Excellent One Mam to avoid CSE ... Your explanation and making the viewers think beyond ... Thanks a lot ....

[@johannesmp3]

This video is so awesome and yet very tricky. One has just keep practicing this. It's so much nesting that my head is still spinning. Gotta say that your way of eliminating the spaces in the list (after using iferror) was more straightforward than mine. I said counta range-countif(range,"")

[@rustamtau7944]

Your 15-minutes video i watched about 1.5 hour to understand what am i doing when i repeat your actions on the screen. It's amazing.
By the way, i already purchased two of your courses on Udemy, your solutions and approach to explain things in my Top3 Excel-Tutors Chart.

[@LeilaGharani]

Glad the video is helpful. I hope you enjoyed the courses.

[@dadgau]

I looked for a tutorial like this for years. Thank you so much.

[@grantdrinnen2076]

Interesting, I will have to give this a try! I had always used the following array formula to extract unique items from a range, where "data" is the same range reused over and over:
{=INDEX(Data,SMALL(IF(MATCH(Data,Data,0)=ROW(INDIRECT("1:"&ROWS(Data) ) ) , MATCH(Data, Data, 0), "") , ROW(INDIRECT ( "1: "&ROWS (Data))))) }

[@leofiatlux01]

You are a life saver, Leila! Tks!

[@airduino5484]

This is Perfect leila ! Great job
God bless you

[@rodrocdl]

It looked complicated the first time I watched it but on my second run it totally makes sense. Thank you Leila

[@yuema1495]

So soothing to listen to your voice and learned some useful skills at the same time !!!

[@LeilaGharani]

I'm glad to hear that.

[@francisocaaki8299]

Thanks Leila. A special thanks from Uganda

[@marine_accountant3171]

This is brilliant formula! Thank you for sharing this!

[@johnshaffell8125]

Awesome lesson. Had been trying to figure out how to do this. Thank you.

[@MrDanielduranc]

Brilliance, you are the best!! Love it

[@armin0815]

Some serious formula magic going on there, thanks!

[@know1squarter307]

Genius,Saves CSE and the use of pivot tables....Great video.....

[@m3lv1n19]

Happy New Year Leila
I have just started a project and this was the first hurdle I was trying to overcome. Can't wait to try this.
Excellent video as ever

[@LeilaGharani]

That's great! Glad this helps. Happy new year to you too Melvin.

[@timosborn73]

Thanks Leila, very useful & methodically explained.

[@nathannychyporuk1189]

MIND BLOWN! Thank you so much for doing this!!!

[@FerrelFrequency]

WOW…This helped way more than the lookups. Big thanks.

[@sarishhendre726]

Thank you so so so so much for the video. I was looking for a solution since days. Thank you so much!!

[@gauravatrey]

Greate way to extract unique items, thank you very much Leila for this video.

[@subhadiproy4025]

Leila Gharani... You are the best in excel. I watch most of your videos and it really helped me a lot in my professional life.

[@LeilaGharani]

Thank you for the nice comment! I'm really happy to hear you find the videos useful for your work :)

[@danpagano5299]

Outstanding video! you are awesome. Thank you so much for saving me a lot of time working this out.

[@edwardlin2941]

Nice formula, ❤️ your explanations!

[@coogs9335]

This is fantastic, love your videos. Thanks for posting!

[@user-sw2tt9nl8y]

WOW this just saved me so mutch time, tanks a lot! What a great channel BTW

[@MrsCathT]

Brilliant and so helpful! I’m a fan of all your videos! Thank you!

[@LeilaGharani]

Thank you for your support!

[@andrewmoss6449]

Happy New Year, Leila. This is another very useful tutorial. Keep up the great work in 2018!

[@LeilaGharani]

Thanks Andrew. Happy 2018 to you too.

[@SUBRAMANIANM1958]

Hi Very simple and easy to follow for excel users. Great work. Keep up the knowledge sharing

[@MysteryMan159]

I’m thankful your channel exists but furious that it’s this complicated to do anything in excel. This is an example of using excel for something that a coded application attached to a database could do WAY better lol.

[@BaoPham-sk2hh]

This is awesome. I've been trying to write a formula using CSE, but this is way much more efficient! Thanks for the tips! =)

[@LeilaGharani]

You're very welcome. Glad the tutorial is helpful.

[@sebghatulbarykhabaey6680]

Awesome!
You teach clearly.
Thank you

[@1gopalakrishnarao]

Happy New Year, Very first Video of the year, "AMAZING". Once again, big namaskar from the bottom of my heart to my great, eminent, & beautiful teacher. Every video has got some very interesting tricks. Great work indeed.

[@LeilaGharani]

Thank you Gopala. Happy new year to you too! I'm very happy you like the videos. Thanks for your support & the very kind words.

[@1gopalakrishnarao]

I am the most lucky and fortunate disciple of your'., I am having the opportunity to view all your videos, though you are very far away from India. I pray the almighty to bless you with good health, prosperity and success in your professional/personal life, and to publish more number of videos for the benefit of all Excel users/learners/addicts in future without any hurdles.

[@chamindabasnayake4844]

Happy New Year!
This is awesome, It is very useful. Thank You!

[@LeilaGharani]

Happy new year to you too :) You're very welcome Chaminda.

[@MohammadArifRahim]

I like the Lila division part!!! awesome

[@genccomo7993]

Same, except one less index(). =IFERROR(INDEX($B$8:$B$22, MATCH(0,COUNTIF($C$7:C7, $B$8:$B$22), 0)),""). Still works, albeit infinitely slow with large data sets. One of the best videos I've seen.

[@LeilaGharani]

Thanks for the great formula :)

[@clarehumail]

I am real like your teaching, it is very clear and helpful for me. Thank you Leila!!! Btw, I come from Taiwan!!!

[@LeilaGharani]

That's great. I'm glad you like the tutorials. Greetings to Taiwan!

[@philalmeida1962]

Your a lifesaver Leila! Thanks!

[@LeilaGharani]

Happy to help, Phil!

[@houstonsam6163]

Very helpful. I frequently learn useful applications of worksheet functions from you, many thanks.

[@LeilaGharani]

I'm happy to hear that Sam. Thanks for your comment.

[@TH3LMITA]

Awesome.. This is for me the most useful function that you could teach us, thank you so much!

[@LeilaGharani]

You're very welcome!

[@jackychawla5422]

Mam ur concepts are out of this world ..new year starts with this magic....

[@LeilaGharani]

Wow! Thanks Jacky for the very nice comment :)

[@douglaszulu6281]

Great video Leila, it helps me get a unique list without CSE.

[@LeilaGharani]

Glad you like it Douglas :)

[@irfanmansoori3060]

Awesome Leila.. You are a genius..

[@AaronMecua]

Thank you so much for this tutorial Leila!

[@LeilaGharani]

You're very welcome Aaron!

[@schalklubbe6290]

Great lesson, Leila!

[@ursraj27]

This is an ingenious way of using COUNTIF. Excellent tutorial.

[@LeilaGharani]

Thanks Rajesh! Glad you like it.

[@ToluThaDon1]

Amazing work, thank you for making these videos!

[@LeilaGharani]

Thank you for your support. I'm glad you like the videos!

[@yatinpurohit]

Very useful take on alternative to Ctrl-Shift-Enter. Also, very well explained step-by-step.

[@LeilaGharani]

Thank you. Glad you like it.

[@drsteele4749]

Way to go Leila. Nice use of INDEX to avoid CSE.

[@LeilaGharani]

Thanks. It's always fun to look for CSE free formula alternatives :)

[@adighose]

Oh God truly the new unique formula changes the entire game. Thanks this video though is excellent

[@AndrewFrink]

the new Unique formula is awesome if... everyone that will use your sheets has a new enough version of excel.

[@hemanthrajakuncham]

To get a unique list..we can trim the duplicates using advanced filter... but it doesn't meet requirement like automatic update..
Really nice work...

[@rsn29sep]

Content is superb, shared with my colleagues, explained with ease

[@LeilaGharani]

Thank you for your support and sharing the videos!

[@arnavgupta5386]

Best possible way and awesome explanation!!!!! Superb😊

[@johnborg6005]

Great Trick Leila. I always used the match and Frequency to get a unique list with CSE. This was Great. :)

[@LeilaGharani]

Thanks John. Match & frequency is great too.

[@jhamca10]

Happy New Year Leila. Thanks a lot for your valuable video.

[@LeilaGharani]

Thank you and happy 2018 to you too :)

[@AndrewBennettScience]

Countif ?* just changed my life. Thank you! 😁

[@faresrabia]

Very thanks for your contribution Leila, good trips and tricks

[@LeilaGharani]

Thanks Fares. Glad you like the tips and tricks.

[@davidhambric]

Brilliant! I've been trying to solve this problem for too long! Thank you!

[@LeilaGharani]

My pleasure David. I'm glad the tutorial is helpful.

[@RameshGau]

thanks a lot for your videos for enhancing our excel skill.

[@mazarata73]

Great tutorial. Thank you.

[@johannesmp3]

Simply genius. Anything you don't know in Excel?

[@tafheemkhattak3089]

Thank you. it saved my day. Helped me in completing a project.👍

[@harshababu8321]

Hi Leila,
At the outset thank you for the wonderful video and above all your ideas. I simply admire your explanation of the formula. Simply superb!

[@justinmeyers7044]

I used your formula. Nice video. I appreciate it.

[@edilpoulina1870]

GR8! I like these tips very much! Very useful for me!

[@gurmeetsingh-cq6br]

Thanks, I applied it in my office. Felt great thanks

[@jozze8336]

Thank so much,
Really helpful

[@mmukkamala]

This is awesome! Thank you so much.

[@ajayrana707]

Thank you so much for explaining this