Have you ever wanted to create a drop down list in Excel to avoid data entry errors or to make the data entry process easier?

In this post I’ll cover a whole range of tips on how to work with drop down lists in Excel including:

Selections from dropdowns - Excel

Excel has a feature called Data Validation that you can use to make sure the users of your spreadsheets enter certain values in the cells. The lists (or drop down lists) are a type of data validation used to make sure the users enter a value from a set of options.

How to create/modify drop down lists

To create/modify a drop down list:

  • Select the cell(s) where you want to create/modify the drop down list
  • Go to the Data tab and click on the data validation button

Data Validation Tab - Excel

In the Data Validation dialog box click in the validation criteria selection button and select ‘List’:

Excel Data Validation Criteria - ListIf you’re modifying an existing list, the validation criteria type will be preselected with the existing type.

  • Enter the source for the list:
    1. The source can be entered manually, using commas to separate the options:

Dropdown - Manual Listor it can be entered by selecting a range that contains the options you want in your list.

Dropdown - Select Range

Create lists using information from other worksheets

If you’re using Excel 2007 or later you can create a drop down from a source range that is located in another worksheet.

To create a list from a range in another worksheet, in the Data Validation dialog box press the range selection button range selection excel,  and go to the desired range in the other worksheet.

Excel Data Validation - Select from another worksheet

How to remove drop down lists

  • Select the cell(s) where you want to create/modify the drop down list
  • Go to the Data tab and click on the data validation button
  • Click on Clear All

Excel Data Validation - Clear All

How to copy lists from one cell to another

  • Select a cell that contains the drop down list you want to copy
  • Copy the cell by pressing Ctrl + C or Right-click -> Copy
  • Select the cells where you want to paste the drop down list
  • Right-click, select paste special, click on Validation and press OK.

Note: You can also do a regular Copy/Paste operation to paste the list. However, if you copy a cell with a value or formula they will be pasted along the drop down.

How to deal with a workbook with multiple different lists

When creating a workbook with multiple different list one of the recommended approaches is to create a worksheet specifically for all the list sources. For example, you can create a worksheet called ‘List Sources’ and place there all the sources for your lists.

In addition, is a good practice to name the lists so you can easily refer to them using their name. To name a list, select the range that contains the list elements and enter the corresponding name in the name box.

Excel Data Validation - naming rangesThen you can use the names of the lists in the Data Validation dialog box

Excel Dropdown - Referencing Range names

To help the users of your worksheets you can display a tooltip when they select the cells that contain a drop down list or any other type of data validation.

Once you’re in the data validation dialog box, click on the Input Message and enter the title and message you want to display.

Excel Data Validation - Input MessageThe tooltip will look like this

Data Validation TooltipTo show a message when the user enters a wrong value in the cell:

Go to the Error Alert tab within the Data Validation dialog box
Enter the title and error message you want to display

Excel Data Validation - Wrong Entry Dialog BoxThe error message will look like this:

Excel Data Validation - Wrong Entry Message

Create an expandable drop down list

If you work with lists that constantly need to be updated and every time you add a new value to the list you need to go the Data Validation dialog box and select the new range, there’s a workaround to make your life easier.

The trick is very simple, just convert the list source to a table and you’ll be good to go. Here are the steps:

1) Select the range that contains the source for the list

2) Convert the source range to a table: Go to Insert tab, click on Table and then click OK.

Dropdown - Create table

A faster way of create tables is to select the desired range and press Ctrl + T, then press Enter.

3)  Create the drop down list and use as a source the table you just created.

Dropdown - Select tableAnd that’s all!

See below how the drop down list automatically captures new values when you add them below the list.

Dropdown - Add new items to the table

Create dependent drop down lists

Another thing you can do is to limit the choices in a drop down list based on the selections of another list. For example, if you select ‘beverages’ in column A, then only beverages appear in column B; but if you select ‘Dairy’ in column A then only dairy products appear in column B. See example below:

Dropdown - Indirect Same cell1) The first step is to create a named range for each option of the main list.

For example, if the main list is composed by Beverages, Dairy, Bakery, and Frozen, then you should create a named range for each option.

Notice that each list MUST be named exactly as the corresponding option in the main list.

Dropdown - Naming Ranges

2) Then create the drop down for the main list

3) Then create the drop down for the sublist using the INDIRECT function

Type =INDIRECT(<Address of cell with main list>)

In the following example the main list is in cell A5, therefore you have to go to the cell B5 and create the drop down list using =INDIRECT(A5) as the source. See example below:

Dropdown - Dependent list

 

If you liked this or know someone who could use it please click the buttons at the top to share it with your friends on Facebook, Twitter, and LinkedIn

Newsletter

Stay up to date with our latest news, receive exclusive deals, and more.

© Master Data Analysis All Rights Reserved 2024