Short or Full Excel Drop Down List

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 short or full Excel drop down list of customers.

Video: Long or Short Drop Down

To see the steps for creating a variable short or long drop down list, watch this short video. There are written steps below the video.

Short or Full Excel Drop Down List

With this technique, you can see a full list of customers in a drop down list. Or, switch to a short list, with just your 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

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, use an absolute reference
     • =IF($E$3=”Full List”, FullList, ShortList)
   • For multiple cells, use a relative reference
     • =IF(E3=”Full List”, FullList, ShortList)
5. Click OK, to close the dialog box.

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.

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.

_________