How to auto update drop down list in Excel?
Auto update drop down list
Transpose Table Dimensions（transpose a cross table to list, and vice versa.）
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
1. Select a cell you want to put the drop down list, and click Data > Data Validation > Data Validation. See screenshot:
|Kutools for Excel, with more than 120 handy Excel functions, enhance working efficiency and save working time.|
2. In the Data Validation dialog, click Setting tab, and select List from Allow list, then type =OFFSET($A$2,0,0,COUNTA(A:A)-1) into the Source text box. See screenshot:
Tip: In the above formula, A2 is the first cell of the data range you want to create a drop down list with, and A: A is the column original data locate.
3. Click OK. Now an auto-updated drop down list is created. And when you add new data to the original data range, the drop down list updates at the meanwhile.
Select Specific Cells (select cells/row/columns based on one ore two criteria.)