Creating drop down menu in excel 2016
Notice that all the blank cells in between entries as well as extra cells after the list have been removed. To display the entire dessert list, select a cell in the “ Dessert” column and click the Data Validation drop-down. It doesn’t matter where those letters appear in the row entry, the matching item is displayed. 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. Click OK to close the Data Validation dialog box. Create a dropdown list in Excel Select a cell (in our example F1) Select the menu Data > Data Validation In the dialog box, select the option List Then.In the Source field, browse out to highlight the list of appetizers ( cells “ List” sheet, cells C2 through C12.In the Data Validation dialog box, on the Settings tab, select “ List” from the Allow.Launch the Data Validation tool by clicking Data (tab) -> Data Tools (group) -> Data Validation.Select a range of cells below the “Dessert” heading ( cells C2 through C10).We saw that the new Data Validation Drop-Down feature would automatically exclude empty cells located at the end of the source selection range.īut what if the list has empty cells mixed in with the data? 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. Instead of manually adding the items to list separated by comma you can simply create a list of items in excel sheet and select that range of cells in the. The drop-down list automatically reduces its scope to only display entries that contain the typed character string. 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”. 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. Then select the cell where you want the drop down. Before this new version, any extra cells would have been displayed, making the list appear less than professional. First, create a list of items that you want to appear in a drop down menu or list. When we select cell B2 and click the drop-down button, we see the list of appetizers from the “ Lists” sheet.īONUS FEATURE: Notice how the blank cells that were selected do not show up in the drop-down. Put the below given formula on column B,C and D Open the Data Validation Window (use shortcut key Alt+D+L) Select the list in allow drop-down.