How to create an automatically extended or dynamic drop-down list

Excel

2016

When you work with data in Excel, often you need to add more and more of new data. However, it
takes some time to update formulas, charts, tables, when you add new data. Excel proposes functions
that can help to customize your data and avoid routine updating. This tip is about creating
an automatically extended (also known as dynamically updated or just dynamic)
drop-down lists.

To create a dynamic drop-down list, do the following:

1. Enter the list of items in a range. For example:

2. Select the cell that will contain the drop-down list.

3. On the Data tab, in the Data Tools
group, click Data Validation:

4. In the Data Validation dialog box, on
the Settings tab:

In the Allow drop-down list, select List.

In the Source box, enter the following formula:

= OFFSET ( <first cell>, 0, 0, COUNTA ( <column> ), 1)

For this example:

= OFFSET ( Data!$B$3, 0, 0, COUNTA ( Data!$B:$B ), 1)

So, you ask Excel to return the data range of filled cells, started from the
<first cell> in column (the function COUNTA () returns the number of
cells that are not empty in a range).

Make sure that the In-Cell Dropdown option is checked.

Notes:

You can create your data range in columns. To create a drop-down list from data in columns,
use the function COUNTA () as last parameter.

For example:

= OFFSET ( Data!$B$2, 0, 0, 1, COUNTA ( Data!$2:$2 ))

You can create an incorrect list, if you have blank cells in your data range.