Excel Dropdown List

An Excel dropdown list allows users to select a value from a list of options instead of typing it manually. For example, you can create a dropdown list for employee attendance, payment status, departments, or countries.

Dropdown lists make data entry easier and help prevent spelling mistakes. For example, if one employee enters "Approved" and another enters "approved", Excel may treat them as different values in some calculations and filters. A dropdown list helps everyone use the same value.

In this article, we will learn how to create a dropdown list in Excel using Data Validation. We will cover four methods, including an automatic dropdown list and a dependent dropdown list.

What is a dropdown list in Excel?

A dropdown list in Excel is a list of predefined options that appears when you select a cell. You can click the dropdown arrow and choose the value you need.

For example, suppose you want to track the status of office tasks. Instead of typing the status in every row, you can create a dropdown list with three options: Pending, Approved, and Rejected.

This makes the worksheet easier to manage because users can select a value from the available options.

Method 1: Create a Dropdown List by Typing Options

This method is useful when you have only a few options, such as Yes/No, Pass/Fail, or Pending/Completed.

Steps to Create a Dropdown List

1. Open your Excel worksheet and select the cell where you want the dropdown list, such as B2.
2. Go to the Data tab in the Excel ribbon.
3. Click Data Validation.
4. In the Settings tab, select List from the allow dropdown menu.

5. In the Source box, type the options separated by commas:

   Pending, Approved, Rejected

6. Make sure the in-cell dropdown option is selected.
7. Click OK.

Output

When you select cell B2, a dropdown arrow appears. Click it to see the available options.

Explanation

Excel uses Data Validation to control which values users can enter into a cell. Because we entered three options in the Source box, the dropdown list displays those three values.

This method is suitable for short lists that do not need frequent changes. Depending on your regional settings, Excel may require semicolons instead of commas to separate the options.

Method 2: Create a Dropdown List Using a Cell Range

If you have many options, entering them one by one in the Data Validation box can become difficult. In this situation, you can store the options in worksheet cells and use that range as the dropdown source.

Example

Suppose you want to create a dropdown list of departments. First, enter the department names in cells F2 to F5.

Steps to Create the Dropdown List

1. Select the cells where you want the dropdown list, such as C2.
2. Open the Data tab and click Data Validation.
3. Select List from the Allow dropdown menu.

4. Click the Source box and enter the following formula:

   =$F$2:$F$5

5. Click OK.

Output

The cells from C2 to C10 will have a dropdown list containing IT, HR, Sales, and Finance.

Method 3: Create a Dropdown List That Updates Automatically

In the previous method, the dropdown list uses a fixed cell range. If you add a new department below F5, the new department will not automatically appear in the dropdown list.

You can solve this problem by converting the list into an Excel Table and creating a named range.

Steps to Create an Automatic Dropdown List

Follow these steps to create a dropdown list in Excel that updates automatically when you add a new department.

Step 1: Enter the department names

First, create a list of departments in Excel. Type Department in cell F1, then enter IT, HR, Sales, and Finance in cells F2 to F5.

Step 2: Convert the list into a Table

1. Select the department list, including the heading.
2. Press Ctrl + T on your keyboard.
3. Make sure the My table has headers option is checked.
4. Click OK.

Step 3: Name the Table

1. Click any cell inside the Table.
2. Open the Table Design tab.
3. Find the Table Name box.
4. Type DeptTable and press Enter.

Step 4: Create a Named Range

Next, create a name that refers to the department column in your Table.

1. Go to Formulas > Define Name.
2. Enter DeptList in the Name box.

3. In the Refers to box, enter this formula:

   =DeptTable[Department]

4. Click OK.

Step 5: Add the Dropdown List

Now, apply the named range to the cells where you want the dropdown list.

1. Select the required cells.
2. Go to Data > Data Validation.
3. Choose List from the Allow menu.

4. Enter the following formula in the Source box:

   =DeptList

5. Click OK.

Output

Your dropdown list will now show IT, HR, Sales, and Finance.

The useful part is that you do not need to update the dropdown source manually whenever a new department is added. For example, if you enter Admin in the first row immediately below the Table, Excel expands the Table and includes Admin in the named range. The new department will then appear in the dropdown list.

Explanation

An Excel Table automatically expands when you add new data directly below it. The named range DeptList refers to the Department column of that Table. The dropdown list uses this named range as its source, so it can include new departments as the Table grows.

Note: Data Validation may not accept a structured Table reference directly in its Source box. Using a named range is a practical workaround. In Microsoft 365, a spilled range can also be used in supported setups. For example, if cell F2 contains a formula that returns a list of unique values, =$F$2# refers to the entire spilled result.

Method 4: Create a Dependent Dropdown List

A dependent dropdown list changes its options according to the selection in another dropdown list. For example, suppose you select India in the Country dropdown. The City dropdown should display Indian cities. If you select USA, it should display cities in the United States.

Example

First, enter the following data in your worksheet.

Here, the Indian cities are listed under India, and the US cities are listed under USA.

Step 1: Create a Named Range for India

1. Select the cells containing Delhi, Mumbai, and Pune, such as H2.
2. Click the Name Box located to the left of the formula bar.
3. Type India and press Enter.

This creates a named range called India.

Step 2: Create a Named Range for USA

1. Select the cells containing New York and Chicago, such as I2.
2. Click the Name Box.
3. Type USA and press Enter.

This creates a named range called USA.

Step 3: Create the Country Dropdown

1. Select cell A2.
2. Go to Data > Data Validation.
3. Select List.

4. Enter the following in the Source box:

   India, USA

5. Click OK.

Cell A2 will now display a dropdown list containing India and USA.

Step 4: Create the City Dropdown

1. Select cell B2.
2. Open Data > Data Validation.
3. Select List.

4. Enter the following formula in the Source box:

   =INDIRECT(A2)

5. Click OK.

Output

When you select India in cell A2, the dropdown in B2 displays Delhi, Mumbai, and Pune. When you select USA, it displays New York and Chicago.

How to Add an Input Message and Error Alert

Excel lets you add an input message and an error alert through the Data Validation settings. These options help users enter the correct information in a cell.

Input Message

An input message shows a small instruction when someone selects a cell. It helps users understand what they need to enter or select.

For example, if a cell contains a dropdown list for employee attendance, you can display this message:

"Select a status from the dropdown list."

To add this message, open the Data Validation dialog box, go to the Input Message tab, and enter your title and message. Click OK to save the changes.

Error Alert

An Error Alert appears when a user enters a value that does not follow the validation rule.

Excel provides three alert styles:

To configure an alert, open Data Validation, select the Error Alert tab, enter a title and message, and choose the required style.

Use the Stop style when users should select only the values provided in the dropdown list.

How to Edit or Remove a Dropdown List in Excel

You can change an existing dropdown list or remove it when it is no longer needed.

How to Edit a Dropdown List

1. Select the cell containing the dropdown list.
2. Go to Data > Data Validation.
3. Change the values in the Source box.
4. Click OK to save the changes.

If you want to apply the changes to other cells with the same validation settings, select the option Apply these changes to all other cells with the same settings, if it is available.

How to Remove a Dropdown List

1. Select the cell or range containing the dropdown list.
2. Open Data > Data Validation.
3. Click Clear All.
4. Click OK.

This removes the Data Validation rule from the selected cells. It does not normally delete the values already stored in those cells..