Short or Full Excel Drop Down List

iconlistlong You can make data entry easier in Excel, by create a drop down list with data validation. Sometimes those lists are so long, that they become a pain to use. Here’s a technique from AlexJ, that lets users switch between a full list of customers, and a short list of top customers.

The technique is driven by a formula — no macros required!

Set Up the Lists

The first step is to create two named lists — they will be used as the source for the drop down lists.

  1. Type the full list of customers
  2. Select all the cells in that list, and name the range: FullList
  3. In another column, type “Full List” in row 2.
  4. Starting in row 3, type the short list of top customers
  5. Select all the cells in that list, including the “Full List” cell
  6. Name that range: ShortList

DataValFull02

Create the Drop Down List

Next, you’ll create the drop down list, by using data validation with an IF formula:

  1. Select the cell(s) where you want the drop down list of customers
  2. On the Ribbon, click the Data tab, then click Data Validation
  3. In the Data Validation dialog box, under Allow, select List
  4. In the Source box, type an IF formula that refers to the active cell, and the named lists. If you’re using the list in a single cell, you can use an absolute reference, e.g. $E$3, and for multiple cells, use a relative reference, e.g. E3
    • =IF($E$3=”Full List”,FullList,ShortList)
    • OR
    • =IF(E3=”Full List”,FullList,ShortList)
  5. Click OK, to close the dialog box.

DataValFull05

Use the Drop Down List

Select the cell with the data validation list, and click the drop down arrow. If the cell does not contain the text “Full List”, the drop down will show the short list of top customers.

DataValFull03

If you need to see the full list, select “Full List” from the top of the drop down list, and then click the drop down arrow again.

DataValFull04

Watch the Video

To see the steps for creating a variable length drop down list, watch this short Excel video tutorial.

Watch in YouTube: Select Short or Long Drop Down List in Excel

_________

You may also like...

5 Responses

  1. Have read something similar in chandoo.org as well! but this is interesting as well. Thank for sharing :)

  2. Contextures Blog » Dynamic Excel Drop Down List says:

    […] Short or Full Excel Drop Down List […]

  3. Hugo says:

    Good idea!

    But – if I once entered a value in E3, I’ll never get back the full list.
    Except I put in “Full List”, what I have to remember.

    Room for enhancement, what do you mean?

  4. larry says:

    The FULL LIST feature does not show up in Excel 2013.

Leave a Reply

Your email address will not be published. Required fields are marked *