Daniel Fischer

I often do not remember how to create simple drop down menus in Excel and so I decided to write a short note here. The thing I want to have:

1. In one tab, I want to have a column with possible values for my drop down menu, e.g my project names
2. In another tab, I want to have in each button of a column a drop down button that allows me to chose from these values.

This is in principle rather easy to achieve:

Step 1: Prepare the List on Another Sheet

1. Open your Excel file and go to the sheet where you want to store the drop-down values (e.g., Projects).
2. Enter the list of values in a column (e.g., A1:A10 in Projects).

Step 2: Name the List (Optional but Recommended)

1. Select the range of values in Projects (e.g., A1:A10 or the whole column).
2. Click on the Formula tab → Define Name.
3. Enter a name (e.g., MyProjects) and click OK.

Step 3: Create the Drop-Down List

1. Go to the sheet where you want the drop-down (e.g., Tasks).
2. Select the cell(s) where you want the drop-down.
3. Click on the Data tab → Data Validation.
4. In the Allow box, choose List.
5. In the Source box:
   • If you named the range: enter =MyProjects
   • If not: enter e.g.  =Projects!A1:A10
6. Click OK.

In case you have in the first line a header (e.g. with column names) you want to remove this line from the drop down options. You can do that like this:

Method 1: Remove Data Validation from One Cell (Header Only)

1. Click on the first cell of the column (e.g., A1).
2. Go to the Data tab → Click Data Validation.
3. In the pop-up, click Clear All → Click OK.