Excel Searchable Drop Down List - No VBA and No Formulas!

This Excel searchable drop down list is dead easy, requiring no VBA/macros and no formulas! In this video I use it to build an interactive chart linked to the drop down list.

🔔 SUBSCRIBE if you’d like more tips and tutorials like this.

💬 Please leave me a COMMENT. I read them all!

🎁 SHARE this video and spread the Excel love.

Or if you’re short of time, please click the 👍

⏲ TIMESTAMPS
0:00 Introduction
0:21 Searchable Drop Down List Chart Example
1:07 Creating a Searchable Drop Down List
2:03 Formatting your Searchable Drop Down List
2:36 Referencing in a Formula
7:23 Linking Searchable Drop Down List to a Chart
9:46 Demo Finished Result
10:43 Shortcomings

[georgetosounidis]

0:40 !!!! OMFG!!! This is astonishing! Been wishing for something like thisto be implemented in excel, didn't know you could do it !
Thank you so much!

[Pamela-dodp]

I discovered the Pivot Table drop list when I originally learned how to use pivot tables, but I did not know that it could be added to charts and other information like you did. Thanx for the great video!

[jensvonkracht]

Mynda, you can't imagine, how long I was looking for exactly this function. So easy as useful. Formatted PivotTables seem to be a powerful hidden function to get more out of Excel. I really appreciate your videos. Big thumbs up smashed.

[shetkar]

Usually, it takes a lot of time to find exactly what I want but this method made my day. THANK YOU!

[wayneedmondson]

Hi Mynda.. very cool trick.. thanks for the tip. I did discover something worth mentioning. When fields are added to the filter area, by default the sort option is Manual. So, the filter drop down does not sort alphabetically and when items are added to the source data and refreshed into the filter list through the Pivot Table, they just appear at the bottom of the list. The trick is to first drag the field into the Rows area and set the sort to A to Z, then drag it into the Filter area. Refresh the Pivot Table and the Filter drop down list will be in alphabetical order and any subsequent additions to the source data will refresh into the the filtered drop down in the correct position. You can even do it in reverse (Z to A) if for some reason you want to do that. I discovered it because my filter lists were not sorting and so with a bit of Internet research, I came upon the answer (thanks Debra Dalgleish at Contextures).. so.. passing it along. Thanks for the great videos.. always some good learning going on at MyOnlineTrainingHub. Thumbs up!!

[katmai]

Big Thanks for existing on YT. You are doing Great Job!

[ThatOfficeGuy]

That's an interesting way to use a pivot table, not one I have ever thought about either. I might give this a go in the near future!

[shetkar]

This is the only useful Searchable Drop Down List found on youtube using pivot table, u r amazing thank a lot. :)

[DaveSlavens]

You can add an average to a pivot table and the min / max with the NA error code. Use an Excel table with formulas similar to what you had in you generic table and it will all work the same as you have in the video. I do it all the time and works just fine.

I will give you a shout out for your use of the markers, I never thought about using them the way you did here. Your video gave me a solution on how I can dynamically solve one of the issues I have. In my reports I want to highlight the current values on a burn up chart but I've been manually updating them each week. If I use your Min / Max column example I can have the data label based on that value and each week as my reports are updated the value will automatically shift week to week.

So thanks for that tip!

[KO]

Instead of having 2 long SUMIFS put your IF statement inside the criteria component of the SUMIFS statement and use a wildcard. i.e. SUMIFS(SumRange, ProductCatRange, IF($C$3="(All)", "*", $C$3))

[pralhadshetye]

Amazing trick, Mynda. Very much appreciated.

[jrodclark]

Your posts are always good but this is one of your (many) bests.

[felipe]

Things that are very simple and add significant value to the work in Excel!

Thanks Mynda!

[jessicadehesa]

Where is the facepalm emoji when we need it! This is something so simple that most of us have completely overlooked it until now. Thanks for setting us straight!

[pionicki]

damn, i never thought about PT in that way. You are just pure Gold

[exceltricks-de]

Cool trick Mynda - have a great weekend

[jamie]

your videos are high level, succinct, and helpful

[graphguy]

Great lesson!
As an adjunct, since the drop down list presents to the user the option to select multiple options (and the user will if the option is there for them to see), solution needs to address that fact. Otherwise when they do the chart will not format and display properly.

[melindam]

So easy solution I have never thought of! 🙈

[anggitoxs]

this is what I've been waiting for, thanks beb