Next, select the cells containing the options. Click the tab for the worksheet containing the drop-down list options. The Data Validation dialog box shrinks down to just the Source box, and you can access your workbook under the dialog box. To add the list from the separate worksheet to your drop-down list, click the up arrow on the right side of the Source box. You can also hide the worksheet containing the options (right-click on the worksheet tab and select Hide) when you distribute the workbook to your users. We’re now going to use that list to populate the drop-down list in each of the selected cells. This can be time-consuming if you have a long list of items.Įarlier in this article, we created a list of items on a separate worksheet. The first method involves manually typing the options in the Source box separated by commas. Now, we need to specify the source for the options in each drop-down list. Select the Source for the Drop-Down List Options On the Settings tab, you can have Excel restrict entries in the selected cells to dates, numbers, decimals, times, or a certain length.įor our example, select List from the Allow drop-down list to create a drop-down list in each of the selected cells. In the Data Tools section, click the Data Validation button.Īdd a Drop-Down List to the Selected Cells Select the cells you want to add the drop-down lists to. Turn on Data Validation for Selected Cellsįor this example, we want to add drop-down lists to the Rating column or column B. Then, go back to the worksheet with your data. You can do that when you define the data validation or define a list in another location on the same worksheet or another worksheet.įor this example, we’re going to list the options for the drop-down list on another worksheet so click one of the worksheet tabs at the bottom of the Excel window.Įnter each option in a column (or row), one option to a cell. Next, we’re going to enter a list of options for the drop-down list. You can either type your data in manually or copy and paste it from another source. Create a Data Table and the List of Optionsįirst, set up a basic data table.

The steps are also the same in modern versions of Excel in the Microsoft 365 (formerly O365) suite. Although I’m taking the screenshots using Excel 2016, the steps are the same if you’re using older versions. Today I’ll show you how to create a drop-down list in a cell using Data Validation.

Insert drop down menu in excel 2016