If you often edit the items in the drop-down menu, you may want to create a dynamic drop down list in Excel. In this case, your list will get updated automatically in all the cells that contain it, once you remove or add new entries to the source list.
The easiest way to create such a dynamically updated drop-down list in Excel is by creating a named list based on a table. If for some reason you prefer a usual named range, then reference it using the OFFSET formula, as explained below.
- You start by creating a usual dropdown based on a named range as described above.
- In step 2, when creating a name, you put the following formula in the Refers to box.
- Sheet1 – the sheet’s name
- A – the column where the items of your drop-down list are located
- $A$1 – the cell containing the first item of the list
As you see, the formula is comprised of 2 Excel functions – OFFSET and COUNTA. The COUNTA function counts all non-blanks in the specified column. OFFSET takes that number and returns a reference to a range that includes only non-empty cells, starting from the first cell you specify in the formula.
The main advantage of dynamic drop-down lists is that you won’t have to change the reference to the named range every time after editing the source list. You simply delete or type new entries in the source list and all of the cells containing this Data Validation list will get updated automatically!