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

Want to learn more about named ranges? Read this:

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 named range

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. […]

Create a unique distinct alphabetically sorted list

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 […]

Unique distinct list sorted alphabetically based on a condition

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

Learn more about the function OFFSET function:

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 use the OFFSET function

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 a Table name in Data Validation Lists and Conditional Formatting formulas

David Hager gave this valuable comment about how to reference a table name in conditional formatting formulas: =INDIRECT("Table1[Start]") Watch this video to […]

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

Download Excel file through email

Enter your email address to receive the workbook. Note, an email will actually be sent to you.
* You will also get a weekly newsletter, unsubscribe anytime!