Author: Oscar Cronquist Article last updated on January 05, 2019

This article describes how to create a drop-down list populated with sorted values from A to Z. The sorted list is dynamic and it adds new values as you type them. The picture below shows a list containing some random values.

First we need to build a cell reference that expands when we add values to the list, named ranges allow us to do that.

However, if you own Excel 2007 or a later version I highly recommend using an Excel defined table instead.

It has a built-in dynamic cell reference system, named structured referencing.

#### Create a dynamic named range "List"

The following instructions tells you how to build an automatically expanding cell range.

1. Click "Formulas" tab
2. Click "Name Manager"
3. Click "New..."
4. Type a name. I named it "List". (See attached file at the end of this post)
5. Type
=OFFSET(Sheet1!\$A\$1, 0, 0, COUNTA(Sheet1!\$A\$1:\$A\$1001))

in "Refers to:" field.

6. Click "Close" button

Create a dynamic named range

A dynamic named range grows automatically when new values are added and also shrinks if values are deleted. This saves [โฆ]

#### Create a dynamic list sorted from A to Z

1. Select Sheet2
2. Select cell A1
3. Type
=IF(COUNTA(List)>=ROWS(\$A\$2:A2), INDEX(List, MATCH(SMALL(COUNTIF(List, "<"&List), ROW(A1)), COUNTIF(List, "<"&List), 0)), "") + CTRL + SHIFT + ENTER
4. Copy cell A2 and paste it down as far as needed.

This article explains how to extract a unique distinct list sorted alphabetically:

Create a unique distinct alphabetically sorted list

The array formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. [โฆ]

You can even use a condition if you prefer, read more here:

Unique distinct list sorted alphabetically based on a condition

The array formula in cell E6 filters values in column C based on value in cell E3, the output is [โฆ]

#### Create a dynamic named range "SortedValues"

1. Click "Formulas" tab
2. Click "Name Manager"
3. Click "New..."
4. Type a name. I named it "SortedValues". (See attached file at the end of this post)
5. Type
=OFFSET(Sheet2!\$A\$1, 0, 0, COUNTA(Sheet2!\$A\$1:\$A\$1001))

in "Refers to:" field.

6. Click "Close" button

How to use the OFFSET function

The OFFSET function returns a reference to a range that is a given number of rows and columns from a [โฆ]

#### How to create a drop down list containing dynamic values

1. Click Data tab
2. Click Data validation button
3. Click "Data validation..."
4. Select List in the "Allow:" window. See picture below.
5. Type
=SortedValues

in the "Source:" window

6. Click OK!

If you are using an excel defined table, read this:

How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas

This article demonstrates different ways to reference an Excel defined Table in a drop-down list and Conditional Formatting. There are [โฆ]