This can help you select from a list without having to retype each time. Do you want to take your Excel skills to the next level? Learn how to create dependent drop-down lists in Excel. Learn how to create a drop-down list in Excel in our latest video. As a result, the range returned by the OFFSET function expands and the drop-down list will be updated.Ĥ. When you add an item to the list on Sheet2, COUNTA(Sheet2!$A:$A) increases. Note that unchecking Ignore blank would mean an empty cell is.

Type in Yes, No as the Source for the drop down list. In the Settings tab, select List under Allow, and ensure that Ignore blank and In-cell dropdown are checked. In the Ribbon, select Data > Data Tools > Data Validation.

COUNTA(Sheet2!$A:$A) counts the number of values in column A on Sheet2 that are not empty. Data Validation to Add a Yes/No Drop Down List. Reference: Sheet2!$A$1, rows to offset: 0, columns to offset: 0, height: COUNTA(Sheet2!$A:$A), width: 1. To automatically update the drop-down-list, when you add an item to the list on Sheet2, use the following formula: =OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)Įxplanation: the OFFSET function takes 5 arguments. To allow other entries, on the Error Alert tab, uncheck ‘Show error alert after invalid data is entered’.ģ. If you type a value that is not in the list, Excel shows an error alert.Ģb. For example, if a user types pizza, an error alert will be displayed.Ģa. Step 2: Create the Drop-down List Using Data Validation Select the cell or cells you want the drop-down list to appear in Click on the Data tab on Excels. Note: this makes your drop-down list case sensitive.

Add a drop down list in excel