Overview

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 allows 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. You can read more about that here:

Become more productive – Learn Excel Defined Tables

An Excel table allows you to easily sort, filter and sum values in a data set where values are related.

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

In this post I am going to explain the dynamic named range formula in Sam's comment. The formula adds new rows and columns […]

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 extracted from a column

The formula in cell D3 extracts unique distinct values sorted A to Z, from column B to column D. Unique […]

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

Unique distinct list sorted alphabetically and 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

Learn more about the function OFFSET function:

Explaining OFFSET function

In this post I am going to provide some basic examples to demonstrate how the OFFSET function works. I also […]

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:

Learn this genius trick on 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 […]

Download example workbook

Create a drop down list containing alphabetically sorted values.xls
(Excel 97-2003 Workbook *.xls)

Functions in this article

IF(logical_test,[value_if:true],[value_if_false])
Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

INDEX(array,row_num,[column_num])
Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

OFFSET(reference,rows,cols, [height],[width])
Returns a reference to a range that is a given number of rows and columns from a given reference

COUNTIF(range,criteria)
Counts the number of cells within a range that meet the given condition

COUNTA(value1,[value2],)
Counts the number of cells in a range that are not empty

ROW(reference) Returns the rownumber of a reference

ROWS(array) returns the number of rows in a reference or an array