Table of Contents
Dropdown in excel is one of the most useful feature. With the help of drop you can setup predefined actions and makes text input easier.
Searchable dropdown list has always been an useful feature of Microsoft Excel. Now with this new update, it is now in-build into Excel.
1. Built-in Searchable dropdown in excel.
Many Excel users rely on creating Drop lists for their projects. If your drop list includes lots of items you want to be able to start typing the first one or 2 characters and have the list shrink accordingly. I created some video tutorial on my YouTube channel, showing how to do that using a combination of Formulas & Functions and some helper columns.
You can also watch the tutorial from one of Microsoft representatives regarding the same
Here are the links to these tutorials.
I then expanded the scope using the CELL function to be able to create the searchable drop list anywhere in the worksheet.
Here are the links to these tutorials.
Today, this functionality is built inside any data Validation Drop List by default in Microsoft 365. Let’s have a look.
In this example I have a list of countries in Column A and I want to create a drop list for these countries in Cell C1.
As always I go to the Data Tab of the Ribbon and Click on “Data Validation”. From the drop list under Allow ► Select List ► Click and drag to select all countries. Alternatively, we can use the shortcut ALT, D, L followed by TAB, L, TAB.
A drop list is created in Cell C1 showing all countries.
The first thing you notice is that this drop list excluded the blank cells in the source, which would require extra steps to do with formulas and functions.
Now if I am looking for a country starting with letter S, just by typing the starting letter, the list shrinks to show only countries with letter S. Isn’t that amazing? We call it progressive disclosure and works for any letter you type.
What if I want “Canada”. If I start typing letter C, the list shrinks to show only countries with letter C. However, Canada was typed twice in the source list and appears twice in the Drop List as well.
This needs the attention of the Excel Developer team to remove duplicate values in the list. I don’t know if I will be too demanding if I also request the items in the drop list to be automatically sorted as well. I suggest adding 2 check boxes to the data Validation Drop List: one for Sorting and the Other for Removing Duplicates, both to be checked by default. Like this
What do you think of this new functionality? And the suggestion?
Another new functionality was also just released:
2. Search box in the Right click menu.
If you right click any cell, chart, shape or picture, a search box appears at
the top of the menu. It has the saame functionality as the search box you see in the Title bar (Alt + Q)
If you start typing in the search box looking for any functionality or function the search returns links to help or takes to the functionality directly. If you type VL ► it returns VLOOKUP. If you type Da it returns Data Validation ► and if you click on it the Data Validation dialog box opens.
If you are wondering: Why do I need it if I already have the same option in the title bar? The concept applies to many other functionalities that we can access in different ways, such as opening the Format Cell dialog box: We have more than 5 methods for doing that. How many methods you know for opening the Format Cell dialog box? Write them down in a comment.
