In Excel, we usually create a drop down list for some daily work. In default, you can add new data in a cell among the range of original data, then the relative drop down list will be auto updated. But if you add new data in the cell below the original data range, the relative drop down list cannot be updated. Here I will tell you a good way to auto update the drop down list when adding new data to the original data. Auto update drop down list
Auto update drop down list
1. Select a cell you want to put the drop down list, and click Data > Data Validation > Data Validation. See screenshot:
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.