The Least Known Excel Trick: Use Hash sign (#) to create Unique & Dynamic Drop Down Lists

Its 2023 and you have to keep yourself updated with all new functions, formulas and techniques Excel is offering you. Lets learn a must know, but at the same time very little known, data validation hack in this short video.

[@primeoflight6674]

Amazing 👏

[@alexcool879]

Wow nice 👍

[@premiertraininghub]

Thanks for watching

[@mouhanadalazem8339]

Thank you

[@premiertraininghub]

You're welcome

[@q46ward]

Hi. Can't you just create a named range for the dropdown list to accomplish the same behavior?

[@premiertraininghub]

Hi Terrence. I had tried the same but a named range with UNIQUE formula results in an error while creating data validation dropdown list.

[@q46ward]

@@premiertraininghub I create a dropdown list using a table with a named range and duplicated values are not reflected in the dropdown. The named range is the value in the dropdown formula. I can add and delete values and the dropdown updates accordingly. Why use the UNIQUE formula when the results are the same with a named range?

[@premiertraininghub]

​@@q46ward Putting a named range that refers to a table, in the dropdown source field will not get rid of duplicated values. This is not possible unless you use UNIQUE formula. There are alternatives but those are more complex and non intuitive. I am not sure what exactly is your method. Maybe if you can explain it in detail, i can have a closer look.