#How to create a drop down list in excel how to
Do you want to take your Excel skills to the next level? Learn how to create dependent drop-down lists in Excel. If the data that you want to show up in the drop down list is in a named range, just put the name in Row Source. As a result, the range returned by the OFFSET function expands and the drop-down list will be updated.Ĥ. Find the Row Source field in the Properties Window. The next option for sorting drop down lists uses a free Excel add-in that I created. First of all, change your normal range into a table. Here are the simple steps to create a dynamic drop down list in Excel. Applies to: All desktop versions of Excel for Windows. The best method to create a dynamic drop down list is using an Excel table for source data. On the Data tab, in the Data Tools group, click Data Validation. On the second sheet, create the following named ranges. Sorting Drop Down Lists with the List Search Add-in. To create these dependent drop-down lists, execute the following steps. When you add an item to the list on Sheet2, COUNTA(Sheet2!$A:$A) increases. Anyone using a desktop version of Excel on either Windows or Mac should be able to use a macro to automatically sort their drop downs. COUNTA(Sheet2!$A:$A) counts the number of values in column A on Sheet2 that are not empty. Reference: Sheet2!$A$1, rows to offset: 0, columns to offset: 0, height: COUNTA(Sheet2!$A:$A), width: 1. Click the Data ribbon > click Data Validation > under Allow select List > select Source and point to the list of options (e.g.
#How to create a drop down list in excel update
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. Note: this makes your drop-down list case sensitive.