We start by creating two lists that will be used by the Data Validation tool. These lists supply the entries shown when the user activates the drop-down list.
The drop-down lists will be used to select an appetizer and dessert for each person filling out the survey form.
Creating the First Drop-Down List
To create the drop-down list that will display appetizers, we perform the following steps:
- Select the first cell below the “Appetizer” heading (cell B2).
- Launch the Data Validation tool by clicking Data (tab) -> Data Tools (group) -> Data Validation.
- In the Data Validation dialog box, on the Settings tab, select “List” from the Allow
- In the Source field, browse out to highlight the list of appetizers (cells “List” sheet, cells A2 through A17. Notice that we have selected a few extra, empty cells for future appetizer entries.)
- Click OK to close the Data Validation dialog box.
Featured Course
Excel Essentials for the Real World
Using the “Appetizer” Drop-Down List
When we select cell B2 and click the drop-down button, we see the list of appetizers from the “Lists” sheet.
BONUS FEATURE: Notice how the blank cells that were selected do not show up in the drop-down. Before this new version, any extra cells would have been displayed, making the list appear less than professional. This relieves us of the need to convert the lists to proper tables or utilize Named Ranges with complex formulas to suppress blanks while making lists easily expandable.
“I want something with cheese.”
Looking through longer lists (say, hundreds of entries), if I want to see all entries that have “cheese” in the name, I can click in the cell and type the first few letters of “cheese”. Look what happens.
The drop-down list automatically reduces its scope to only display entries that contain the typed character string.
We can apply this drop-down list feature to many cells in the “Appetizer” column by selecting cell B2 and clicking Copy, then select additional cells (ex: B3 through B25) and click Home (tab) -> Clipboard (group) -> Paste -> Paste Special -> Validation.
Excluding Empty Cells
We saw that the new Data Validation Drop-Down feature would automatically exclude empty cells located at the end of the source selection range.
But what if the list has empty cells mixed in with the data?
Let’s find out how this is dealt with.
- Select a range of cells below the “Dessert” heading (cells C2 through C10).
- Launch the Data Validation tool by clicking Data (tab) -> Data Tools (group) -> Data Validation.
- In the Data Validation dialog box, on the Settings tab, select “List” from the Allow
- In the Source field, browse out to highlight the list of appetizers (cells “List” sheet, cells C2 through C12.
- Click OK to close the Data Validation dialog box.
Selecting a cell in the “Dessert” column and typing the first few letters in the words “Peanut Butter”, we see the following filtered drop-down list.
It doesn’t matter where those letters appear in the row entry, the matching item is displayed.
To display the entire dessert list, select a cell in the “Dessert” column and click the Data Validation drop-down. This displays the following result.
Notice that all the blank cells in between entries as well as extra cells after the list have been removed.
Featured Course
Power Excel Bundle
Searchable Features in Context Menus
Another new feature is the ability to locate and activate almost any Excel feature from a search box in the right-click context menu.
right-clicking any cell on the worksheet reveals the cell context menu. At the top of the menu is a Search feature.
When you click in the Search field, you can quickly access virtually any feature in Excel by typing a few letters of the feature name.
NOTE: This Search feature does NOT appear if you are right-clicking on a cell in an official Excel Table.
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
