Creating a drop down list in Excel: static, dynamic, from another workbook

The tutorial demonstrates 4 quick ways to create an Excel data validation list (drop-down list) - based on a list of values, range of cells, named range and a dynamic dropdown. It also shows how to create a dropdown from another workbook, edit and delete data validation lists.

Excel drop-down list, aka drop down box or combo box, is used to enter data in a spreadsheet from a pre-defined items list. The main purpose of using drop down lists in Excel is to limit the number of choices available for the user. Apart from that, a dropdown prevents spelling mistakes and makes data input faster.

How to create an Excel drop-down list

On the whole, there are 4 ways to make a drop down menu in Excel, and all of them have their own strong and weak points. Below you will find a quick outline of the main advantages and drawbacks as well as the detailed step-by-step instructions for each method:

This is the fastest 3-step way to create a drop-down box in all versions of Excel 2016, 2013, 2010, 2007 and 2003.

1. Select a cell or range for your drop-down list.

You start by selecting a cell or cells where you want a drop-down box to appear. This can be a single cell, a range of cells or the entire column. If you select the whole column, a drop down menu will be created in each cell of that column, which is a real time-saver, for example, when you are creating a questionnaire.
Select a cell or range for your drop-down list.

You can even select non-contiguous cells by pressing and holding the Ctrl key while selecting the cells with the mouse.
Select non-contiguous cells by pressing and holding the Ctrl key.

2. Use Excel Data Validation to create a drop-down list.

On the Excel ribbon, go to the Data tab > Data Tools group and click Data Validation.
Use Excel Data Validation to create a drop-down list.

3. Enter the list items and choose the options.

In the Data Validation window, on the Settings tab, do the following:

  • In the Allow box, select List.
  • In the Source box, type the items you want to appear in your drop-down menu separated by a comma (with or without spaces).
  • Make sure the In-cell dropdown box is checked; otherwise the drop-down arrow won't appear next to the cell.
  • Select or clear the Ignore blank depending on how you want to handle empty cells.
  • Click OK and you are done!

Enter the list items and choose the options for your Excel dropdown.

Now, Excel users simply click an arrow next to a cell containing a dropdown box, and then select the entry they want from the drop down menu.
Excel users click an arrow next to a cell containing a dropdown box, and then select the entry they want from the drop down menu.

Well, your drop-down box is ready in under a minute. This method works well for small Excel data validation lists that are unlikely to ever change. If it's not the case, consider using one of the following options.

This method of creating an Excel data validation list takes a bit more time, but it may save even more time in the long run.

1. Type the entries for your drop-down list.

Select the entries you want to appear in your drop-down menu in an existing worksheet or type the entries in a new sheet. These values should be entered in a single column or row without any blank cells.

For example, let's create a drop-down list of ingredients for your favorite recipes:
Type the entries for your Excel drop-down list.

Tip. It's a good idea to sort your entries in the order you want them to appear in the drop-down menu.

2. Create a named range.

You can actually skip this step and create your drop-down list based on a range of cells, but named ranges really make managing Excel drop-down lists easier.

  • Select all the entries you want to include in the drop down list, right-click them, and choose Define Name from the context menu. Alternatively, you can click Name Manager on the Formulas tab or press Ctrl + F3.
  • In the Name Manager dialog, click New.
  • In the Name field, type a name for your entries, make sure the correct range is displayed in the Refers to box, and then click OK. Be sure your range name doesn't have any spaces or hyphens, use underscores (_) instead.

Give a name to the range and make sure it includes the cells you want.

Tip. A faster way to create a named range in Excel is to select the cells and type the range name directly in the Name Box. When finished, click Enter to save the newly created named range. For more information, please see how to define a name in Excel.
The fastest way to create a named range in Excel.

3. Apply Data Validation.

Click in the cell where you want the drop-down list to appear - it can be a range of cells or the entire column, in the same sheet where your list of entries is located or in a different worksheet. Then, navigate to the Data tab, click Data Validation and configure the rule:

  • In the Allow box, select List.
  • In the Source box, type the name you gave to your range preceded by an equal sign, for example =Ingredients.
  • Make sure the In-cell dropdown box is checked.
  • Click OK.

Configure your Excel drop-down list.

Note. If you are creating a drop-down based on a named range, and that named range has at least one blank cell, selecting the Ignore blank box allows any value to be entered in the validated cell.

If the source list contains more than 8 items, your drop-down box will have a scroll bar like this:
An Excel drop-down list with a scroll bar

Instead of using a regular named range, you can convert your data to a fully functional Excel table (Insert > Table or Ctrl + T), and then create a data validation list from that table.

To do this, you either enter =your_table_name[column_name] in the Refers to field, or select all of the cells without a column header before opening the Name Manager and have the Refers to box filled automatically.
Creating a named based on the table column

Why you may want to use a table? First and foremost, because it lets you create a dynamic drop-down list that will update automatically as you add or remove items from the table.

To create a drop-down box based on a range of cells, carry out these steps:

  1. Type the items in separate cells.
  2. Select the cell where you want the drop-down list to appear.
  3. On the Data tab, click Data Validation.
  4. Place the cursor in the Source box or click the Collapse Dialog icon, and select the range of cells to include in your drop-down list. The range may be in the same or in a different worksheet. If the latter, you simply go to the other sheet and select a range using a mouse.
    Making a drop down box based on a range of cells.

Create a dynamic (automatically updated) Excel dropdown

If you often edit the items in the drop-down menu, you may want to create a dynamic drop down list in Excel. In this case, your list will get updated automatically in all the cells that contain it, once you remove or add new entries to the source list.

The easiest way to create such a dynamically updated drop-down list in Excel is by creating a named list based on a table. If for some reason you prefer a usual named range, then reference it using the OFFSET formula, as explained below.

  1. You start by creating a usual dropdown based on a named range as described above.
  2. In step 2, when creating a name, you put the following formula in the Refers to box.



    • Sheet1 - the sheet's name
    • A - the column where the items of your drop-down list are located
    • $A$1 - the cell containing the first item of the list

As you see, the formula is comprised of 2 Excel functions - OFFSET and COUNTA. The COUNTA function counts all non-blanks in the specified column. OFFSET takes that number and returns a reference to a range that includes only non-empty cells, starting from the first cell you specify in the formula.
Creating a dynamic drop-down list in Excel using the OFFSET formula

The main advantage of dynamic drop-down lists is that you won't have to change the reference to the named range every time after editing the source list. You simply delete or type new entries in the source list and all of the cells containing this Excel validation list will get updated automatically!

How this formula works

In Microsoft Excel, the OFFSET(reference, rows, cols, [height], [width]) function is used to return a reference to a range consisting of a specified number of rows and columns. To force it to return a dynamic, i.e. continuously changing range, we specify the following arguments:

  • reference - cell $A$1 in Sheet1, which is the first item of your drop-down list;
  • rows & cols are 0 because you don't want to shift the returned range either vertically or horizontally;
  • height - the number of non-empty cells in column A, returned by the COUNTA function;
  • width - 1, i.e. one column.

You can make a drop-down menu in Excel using a list from another workbook as the source. To do this, you will have to create 2 named ranges - one in the source book and another in the book where you wish to use your Excel Data Validation list.

Note. For the drop-down list from another workbook to work, the workbook with the source list must be open.

A static dropdown list from another workbook

The dropdown list created in this way won't update automatically when you add or remove entries in the source list and you will have to modify the source list reference manually.

1. Create a named range for the source list.

Open the workbook that contains the source list, SourceBook.xlsx in this example, and create a named range for the entries you want to include in your drop-down list, e.g. Source_list.
Create a named list in the source workbook.

2. Create a named reference in the main workbook.

Open the workbook in which you want the drop down list to appear and create a name that references your source list. In this example, the completed reference is =SourceBook.xlsx!Source_list
Create a name in the main workbook that references the source list.

Note. You have to enclose the workbook's name in apostrophes (') if it contains any spaces. For example: ='Source Book.xlsx'!Source_list

3. Apply Data Validation

In the main workbook, select the cell(s) for your drop-down list, click Data > Data Validation and enter the name you created in step 2 in the Source box.
Apply Data Validation.

A dynamic dropdown list from another workbook

A dropdown list created in this way will get updated on the fly once you've made any changes to the source list.

  1. Create a range name in the Source workbook with the OFFSET formula, as explained in Creating a dynamic drop-down.
  2. In the main workbook, apply Data Validation in the usual way.

Excel Data Validation does not work

The Data Validation option is greyed out or disabled? There are a few reasons why that might happen:

  • Drop-down lists can't be added to protected or shared worksheets. Remove the protection or stop sharing the worksheet, and then try to click Data Validation again.
  • You are creating a drop down list from an Excel table that is linked to a SharePoint site. Unlink the table or remove the table formatting, and try again.

In most cases, the Settings tab's options we've discussed above absolutely suffice. If they don't, two more options are available on the other tabs of the Data Validation dialog window.

Display a message when a cell with the dropdown is clicked

If you want to show your users a pop up message when they click any cell containing your drop-down list, proceed in this way:

  • In the Data Validation dialog (Data tab > Data Validation), switch to the Input Message tab.
  • Make sure the option Show input message when cell is selected is checked.
  • Type a title and message in the corresponding fields (up to 225 characters).
  • Click the OK button to save the message and close the dialog.

Configuring a message to be displayed when a cell with the drop-down list is clicked.

The result in Excel will look similar to this:
A message is displayed when a cell with the drop-down list is clicked.

Allow users to enter their own data in a combo box

By default, the drop-down list you create in Excel is non-editable, i.e. restricted to the values in the list. However, you can allow your users to enter their own values.

Technically, this turns a drop-down list into an Excel combo box. The term "combo box" means an editable dropdown that allows users to either select a value from the list or type a value directly in the box.

  1. In the Data Validation dialog (Data tab > Data Validation), go to the Error Alert tab.
  2. Select the "Show error alert after invalid data is entered" box if you want to show an alert when a user attempts to enter some data that is not in the drop-down menu. If you don't want to show any message, clear this check box.
  3. To display a warning message, pick one of the options from the Style box, and type the title and message. Either Information or Warning will let the users enter their own text in the combo box.
    • An Information message Information icon is recommended if your users are likely to input their own choices quite often.
    • A Warning message Warning icon will induce the users to select an item from the drop-down box rather than enter their own data, though it does not prohibit custom entries.
    • Stop (default) will prevent people from entering any data that isn't in your Excel drop-down list.

    Allow users to enter their own data in the combo box.

    And this is how your customized warning message may look like in Excel:
    An alert is shown when a user tries to enter some data in the combo box other than is in the drop-down list.

    Tip. If you are not sure what title or message text to type, you can leave the fields empty. In this case, Microsoft Excel will display the default alert "The value you entered is not valid. A user has restricted values that can be entered into this cell."

How to edit an Excel drop down list

After you've created a drop-down list in Excel, you might want to add more entries to it or delete some of the existing items. How you do this depends on how your drop down box was created.

Editing a comma separated drop-down list

If you've created a comma separated drop down box, proceed with the following steps:

  1. Select a cell or cells that reference your Excel Data Validation list, i.e. cells containing a drop-down box that you want to edit.
  2. Click Data Validation (Excel ribbon > Data tab).
  3. Delete or type new items in the Source box.
  4. Click OK to save the changes and close the Excel Data Validation window.
    Editing a comma separated drop-down list
Tip. If you want to apply the changes to all the cells containing this drop-down list, select the "Apply these changes to all other cells with the same settings" option.

Editing a drop-down menu based on a range of cells

If you have created a drop-down box by specifying a range of cells rather than referencing a named range, then proceed in the following way.

  1. Head over to spreadsheet containing the items that appear in your drop-down box, and edit the list in the way you want.
  2. Select the cell or cells containing your drop-down list.
  3. Click Data Validation on the Data tab.
  4. In the Excel Data Validation window, on the Settings tab, change the cell references in the Source box. You can either edit them manually or click the Collapse Dialog icon. Collapse Dialog icon
  5. Click the OK button to save the changes and close the window.
    Editing a drop-down menu based on a range of cells

Editing an Excel drop-down list based on a named range

If you have created a named range based drop-down box, then you can just edit your range's items and then change the reference to the Named Range. All drop-down boxes based on this named range will get updated automatically.

  1. Add or delete items in the named range.
    Open the worksheet containing your named range, delete or type new entries. Remember to arrange the items in the order you want them to appear in your Excel drop-down list.

  2. Change the reference to the Named Range.
    • On the Excel ribbon, go to the Formulas tab > Name Manager. Alternatively, press Ctrl + F3 to open the Name Manager window.
    • In the Name Manager window, select the named range you want to update.
    • Change the reference in the Refers to box by clicking the Collapse Dialog icon Collapse Dialog icon and selecting all the entries for your drop-down list.
    • Click the Close button, and then in the confirmation message that appears, click Yes to save your changes.

    Editing an Excel drop-down list based on a named range

    Tip. To avoid the necessity to update the named range's references after each change of the source list, you can create a dynamic Excel drop-down menu. In this case, your dropdown list will get updated automatically in all associated cells as soon as you remove or add new entries to the list.

How to delete a drop-down list

If you no longer want to have drop-down boxes in your Excel worksheet, you can remove them from some or all cells.

Removing a drop-down menu from selected cell(s)

  1. Select a cell or several cell from which you want to remove drop down boxes.
  2. Go to the Data tab and click Data Validation.
  3. On the Settings tab, select the Clear All button.
    Removing a drop-down menu from selected cell(s)

    This method removes the drop-down menus from the selected cells, but keeps the currently selected values.

If you want to delete both a dropdown and the cells' values, you can select the cells and click the Clear all button on the Home tab > Editing group > Clear.

Deleting an Excel drop-down list from all cells in the current sheet

In this way, you can remove a drop-down list from all associated cells in the current worksheet. This won't delete the same drop-down box from cells in other worksheets, if any.

  1. Select any cell containing your drop-down list.
  2. Click Data Validation on the Data tab.
  3. In the Data Validation window, on the Settings tab, select the "Apply these changes to all other cells with the same settings" check box.

    Once you check it, all of the cells referencing this Excel Data Validation list will get selected, as you can see in the screenshot below.

  4. Click the Clear All button to delete the drop-down list.
  5. Click OK to save the changes and close the Data Validation window.
    Deleting a drop-down list from all cells in the current sheet

    This method deletes a drop-down list from all the cells containing it, retaining the currently selected values. If you created a dropdown based on a range of cells or based on a named range, the source list will also remain intact. To remove it, open the worksheet containing the drop-down list's items, and delete them.

Now you know the basics of Excel drop-down lists. In the next article, we will explorer this topic further and I will show you how to create dependent drop down lists with conditional Data Validation and how to create a drop-down box from another workbook. Please stay tuned and thank you for reading!

214 responses to "Creating a drop down list in Excel: static, dynamic, from another workbook"

  1. madhu says:

    i'm looking for a solution. In sheet number 1 i have products name,product part number and description. i'm working in sheet number 2 if i enter part number i want description will be auto generate.
    is any formula is there? Please help me.

    • Hi Madhu,

      Well, this is not exactly the topic discussed in this article... Still, you can retrieve the description using a simple VLOOKUP formula.

      Suppose you have part numbers and descriptions in sheet 1, in columns B and C, respectively, and row 2 is the first row with data. Then if you enter a part number in cell A2 on sheet 2, and copy the below formula in cell B2, you will get the corresponding description in B2:

      Please check out our VLOOKUP tutorial for more information.

      • gabriel says:


        Great website! But I'm really a novice with basic excel knowledge. I'm looking for a way to have a dropdown list with a range of numbers and then have it give me a number for that range.


        Price Fee
        $0-$499 = $115
        $500-$999 = $125
        $1000-$2999 = $180

        and so on. I would like a column to have a drop down list with the price range and another column with the fee associated with the range.

        • Kevin says:


          The drop-down will follow the previous setup for a "static" drop-down, and you should provide the option(s) in the source as follows:

          $0-$499, $500-$999, $1000-$2999

          In the cell you want the fee associated with the range, an IF statement should work just fine (a bit rudimentary, but for what you want it to do, it should work well and not have any real issues). Use this:


          Replace C3 with whatever cell your drop-down selection is, then format the column to your liking (most likely as a currency).

          The IF statement basically checks which drop-down is selected, then assigns a numerical value based on the selection. Hope this helps

      • sunil says:

        i want to know how can i make my drop down box in excel remain forever when i open next time? please assist me

  2. Alex says:

    I am wondering if it is possible to use a drop down list which could be then linked to a column of data. For example, if from the drop down list i choose my first value, a blank column of data that i specify will show which i can then enter data into. If I then select the select value from the drop down list, column of data linked to one disappears and allows me to enter responses for value 2 of my drop down list. If i switch back to value 1 from drop down, the data i entered previously should show up. Is this possible? Or would each response have to entered in a different sheet, then an index match formula be used to call up different column responses based on the drop down value?
    Thanks in advance

  3. Alex says:

    Thank you of trying

  4. Claire coulter says:

    Hi. I want to create a drop down list which shows the text for the user to select, but returns an icon based on what's been selected. Can you help? Thx.

  5. Dipsundar says:

    This artical is very good. I learn alot. Can you please describe the OFFSET formula? Means in which scenarios it use.

  6. Marvin says:

    Hi I found this very helpful, however i am working on a roster and used data validation so that you cant select a day shift after workking a nighshift (because the hours overlap etc) DATA Validation formula below:
    =IF(Offset(Sheet1!$j$40,0,-1)="n", $H$30:$H$11, $H$10:$H$12))
    So... H10="n" (night shift), H11= "p" (afternoon shift) and H12="a" (morning shift)

    My question is... can you refference the current cell without putting in the cell letter and number (Sheet1!$J$40). because I would hate to have to write the different refference in the validation for each cell (28 days times 50+ users). And the truth is that i have simplified the formula above because it involves more ifs within the if to account for how many shifts are on for that day already.

    So again the question is instead of writing Offset(Sheet1!$J$40... can i write something like Offset(CurrentCel... or something like that. so then i can just copy the formula and use if for the 1400 other cells in the roster.
    Thanks in advanced.

  7. Marvin says:

    Whoops i miss typed... the formula is more like:
    IF(Offset(Sheet1!$j$40,0,-1)="n", $H$10:$H$11, $H$10:$H$12))

    Dont know how i hit the 3 key sorry...

  8. Ivan says:

    Hi Svetlana,

    First off thank you for all your advice and effort, its greatly appreciated. I've read through this section as well as some of the links provided herein but have not quite found / understood what I need for my "issue".

    I am trying to create a pay sheet for my staff. We have various staff on different pay grades. We have a separate excel sheet with all our staff names, numbers, paygrade, hourly overtime rate and various other details in rows. e.g. A2=name, B2=number, B3=rate, etc.

    I would like to, using a drop down list, select the staff member by name and then in the subsequent columns have the required information (number, rate, overtime rate) inserted automatically in the subsequent columns in the pay sheet. e.g. in C11 we select the staff member by name. Once selected we require C11, C12, C13 and C14 in that sheet to be filled with the corresponding information of that staff member.

    The rest of the paysheet is fine, it calculates the remuneration with no problems.

    Could you also include the formula for getting the data from a separately saved workbook (we have the staff list stored in the cloud).

    Thanks in advance!

    • Diego says:

      I have a similar issue so hope to get your questions answered.
      We have two options in the drop-down list, Cash and Finance.
      If payment is Cash, subsequent columns to show a price less a $399 discount = Total
      If payment is Financed, subsequent columns to show the price as the Total
      I am debating honing my VBA skills for this one.. Yikes! We will appreciate the assistance!

    • Michael Nixt says:

      Ivan, a lookup() function can be used in this case. so in C12 type "=lookup(C11,[RangeWithName],[RangeWithNumber])" And then C13 would be "=lookup(C11,A2:A999,B2:B999)". I don't know how many people you have, but you get the idea. The function searches in part 2 for what was referenced in part 1 (C11) and prints what is in part 3. These would change dynamically even if you had the names in a table and the table got sorted often or whatnot.

      It would be possible to reference a seperately saved workbook if instead of referencing that cell, you had the other excel file open and clicked that file instead. You can not move the file or change the name after that, otherwise it won't work!

      You don't need VBA to do this.

  9. Francene says:

    Hello! I have two columns in Sheet 1 in a workbook. These are "Partner Name" and "Partner Company". I have a Sheet 2 in that same workbook that also has two columns - "Partner Name" and "Partner Company". I created a dynamic drop-down list so that I can populate the "Partner Name" on Sheet 2 with information from Sheet 1. I would like to populate the "Partner Company" column on Sheet 2 when I populate the "Partner Name" company on Sheet 2. In other words, when I enter "Partner A" in the "Partner Name" column on Sheet 2, I would like to enter the Company Name (as listed on sheet 1 in another column) in the "Company Name" on Sheet 2. I want to populate two columns with one selection on Sheet 2. Is this possible? I was looking at VLOOKUP and think maybe I can use that but am not sure how.

  10. Dion Bright says:

    Thanks for your tutorial ... super clear!

    I've made my list, and insisted on only entries that match those in the list ... but my list is long. Can the Excel list automatically limit the visible list as you begin typing ... so if I type "b" ... the list reduced to only those starting with "b", then if I type "o", now the list would only show those starting with "bo". Can it do this?

    Thanks in advance

  11. your mom says:

    You have a huge mistake in method one. took over a gad damn hour
    you do not put the values as instructed in step 3, you highlight the cells with your input there.....

  12. James M says:

    Hi there. I am making spreadsheet for payroll using the method " Creating an Excel drop-down list based on a named range ". I fulfilled all steps yesterday creating a template however going back today it seems to not have saved. I receive a message when saving saying

    " one or more cells contain data validation rules which refer to values on other worksheets. These data validation rules will not be saved "

    Any help would be appreciated :)

    • Hi James,

      This seems to be a known issue if you are working with .xls workbook in a new Excel. If this is the case, please try the following suggestions:

      The warning is incorrect, there is no loss of functionality. You can save the file without losing the Data Validation. You can also avoid this message by one of the following workarounds:

      1. Save the file in the XLSX format.

      2. Uncheck the "Check compatibility when saving this workbook" option.

      For more info about this issue, pleasr see:

      • Jethro says:

        Hi Svetlana,

        My workbook uses macros, which the XLSX format does not support. If I chose macro-enabled format then the dynamic data validation method does not work. Looks like i have to choose between the 2.

        Any suggestions?

  13. James+M says:

    Thanks Svetlana.

    Saving in the XLSX format done the trick.

    Cheers :)

  14. Pravin says:

    Hi Svetlana,
    Hope you can help me out with the below issue in excel!
    I need to enter the values for a dropdown list in a webpage automatically, once clicking the Command button in excel sheet.
    For each and every replacement in the dropdown list, the values related to that need to be exported as a excel file.
    for further understanding the webpage will look like ( please save the file and open with a browser for the GUI.
    I'm new to excel VBA & Macro, so i cannot make the data in excel sheet to place it in the dropdown list in that webpage.
    Please let me know for further clarification, also if you've any ideas please let me know.
    Thanks in advance !
    Regards/ Pravin

  15. Jijesh says:

    I'm seeking a solutions for the below mentioned task. Please help me.
    I have two xl file called 1 and 2. In this two xl files 1 and 2 include separate working sheet called R1 and R2. In Sheet R2 so many values in A columns (say A0 to A100 - All the 100 cells having values, that means no cells are blank).
    Now I want to link each value from Sheet R2 (A0 to A100) to R1's B column.
    The problem is that while linking I want to get the result like this. A0 to B0, A1 to B3, A2 to B4, A3 to B6 etc.

  16. Rishi says:

    My xls have 3 drop down colums.Now does that causes the xls to store lesser than 65536 rows.Beacause my xls is able to store 21845 rows which means 65536/3=21845.
    On opening the xls having more than 21845 gives error "File is corrupt".
    Please help with the issue??

  17. Sameer Lakhani says:

    Yes!!! got the information how to edit and add more names if required.

  18. SERU VENKANNA says:

    i learn so much with your article

  19. Sheryl says:

    I have created a document with the drop down lists. The data is regarding enquiries and members leaving our facility. It is organised by date range, occasionally we have to insert more rows within the data. When I insert a row it is losing my ability to use the drop down lists. Is there a way around this? Many thanks

  20. KwaanYC says:

    Hi Svetlana,
    This excel drop down article is awesome & very useful.
    I'm using it to create my inventory stock list.
    But how to further sorting my dynamic stock list to more details like below:-

    1st Option: Brand
    2nd Option: Item - Dependent on Brand
    3rd Option: Model - Dependent on Brand & Item
    4th Option: Serial Number - Dependent on Brand,Item & Model.

  21. Narasim says:

    Thank you very much, Its always useful for beginners

  22. Becky says:

    This was great information. I use lists in Data Validation all the time. I'm creating a new Price List that has 3 basic pricelists (Named Range). All customers are based off of 1 of these lists then calculated with specific discounts tailored to each customer. I'd like to use a drop-down menu to choose 1 of the basic lists as a base for that customers calculations. I can make a simple formula using only 1 named range, but I'd like the formula to see the drop-down menu choice and use the correct named range. Is this possible? Any help would be greatly appreciated.

  23. Anirban says:

    Hello Svetlana, Awesome article and much appreciate the responses that you have provided to various users like me.
    My situation seems trivial but not addressed in the responses above
    I need to create a picklist from a bunch of data residing in multiple columns in multiple sheets. To explain , I have data in 1st worksheet column A1:A10 , D1:D10..then in worksheet 2 Column A1:A10 , C1:C8. I want the picklist to be created in worsheet 3,column H , merging the data for all the 4 column ranges mentioned above. Is that possible ? Thanks much in advance.

  24. Omar Hadid says:

    I am preparing an excel sheet and want to create a set of tabs let say maximum 10 and for a project reference I need to show only 6 tabs instead of 10 , all these tabs have the same informations.
    if we need 4 i have to do something to let my workbook show only 4 tabs, next project i need to show 7 so i have to show only 7.
    please could you help on this?

  25. Cathy says:


    I have created a several combo boxes (form control) in my spreadsheet and in the format control, set the input range by selecting a range of cells from Sheet 2 of my document. I have several combo boxes, connected to several different lists. My problem is that if I select the first option from any of the dropdown lists, they do not save. The only way I have found to get around it is to put a blank field at the top of the dropdown list - but not ideal as I don't want people to be able to select the blank option. I could use data validation, but I prefer the look of combo boxes rather than data validation boxes.

    Any ideas ?


  26. John Viritia says:


    I was wondering if I could create a list which will contain codes and have their respective descriptions but only show the codes when selected.

    Please lemme know if its possible.



  27. Becky says:

    I am trying to create a dynamic drop-down list. There are multiple tabs in the workbook - in the first worksheet, users select or enter in information into a column. In the second sheet, I want a drop-down list containing the entries from that column. As users add more entries into the first sheet, the drop-down list on the second sheet needs to reflect that. I tried to create a dynamic drop-down list as described, and it does show additional entries as they are entered, but it also includes all of the blanks from the column on the first sheet, making it a very long list of blanks. How do I fix this and make the drop-down list only contain the information entered by the user?


  28. michael says:

    Create a dynamic (automatically updated) Excel dropdown does not work

  29. Shannon says:

    thank you for this information on this website - wondering when creating a list, where do I go to allow the user to be able to check more than one option from the dropdown?

  30. Amin says:

    I have three drop down list use the name range
    when the first list is category and the others two list depend to show what they contains on the first what, and I realize that I misspelled some category and I correct the name range but the existing cells didn't update
    how I can fix that ?

  31. Ash says:


    I am using the drop down list for a time sheet style workbook where a user continuously enters time information and selects a job from a drop down list, which is then totalled up on a summary tab.

    The problem I have is some jobs change names at some point, so I need to change the name in the list. The name is changed in the drop down list, however all previously entries remain as they were when they were entered (the old job name).

    Is there a way to dynamically update the selections of previous entries?


  32. deepak verma says:


    i have 2 drop down list in excel. in first drop down list value is 1 to 5 and second list A to E. give me solution that if i select "A" in drop down list than value in first drop down list will show "1" and so on for other.


  33. Laurie says:

    I am creating a spreadsheet with multiple columns of drop down lists. The user will sometimes copy and paste their data into the spreadsheet, so I don't want a warning box to come up for every cell of data that doesn't match what is in the drop down. Can I just turn that data red instead of popping up a message? Then, the user can quickly scan for the red data and determine if the data needs to be changed or not. Thank you for your help.

  34. Marcia says:

    Creating a drop down list is new to me but after reading articles, it appears fairly easy to create. My question is how do I make that drop down list automatically appear in every cell in the column to infinity? I believe that there are not only issues with using control C and control V, but also that only copies the cells selected and my worksheet will grow. In addition, does inserting a row work? Or is it better enter the information on the last row and then sort the data?

  35. Jcmar says:

    thank you somuch for this tutorial, this is very helful for me because I am a documents controller, I really need this, thank you...!

  36. Ann says:


    I have a query as below:
    I'm trying to create a pricing spreadsheet that has headings like "Course group", "Course name", number of users, Price etc. Under the heading "course group", I created a drop down list of all the "course groups" that I have like accounting, asset management etc. Now under the next heading "Course Name", I want to get a specific set of options (course names) in the drop down when I select Accounting for example under the heading "Course Group". How can this be done?


  37. gail says:

    thank you for your insight. I am attempting to create a drop down menu that is color coded. How do I do this? I not able to find the instructions. My lists are simply words without the coloring.

  38. waqas says:

    i want to know is it possible for a drop down list to filter data by pressing keys for example i want to see all the word starting from A when i open list and press A and similarly for other alphabets


    Thanks This has really helped me with my work!

  40. azlina says:

    how can i make a drop down list by default show "--SELECT--" when user open the excel file?

  41. Irakli says:

    Hi all

    is it possible to make drop down list with content which will be depended on the input of another drop down list? for example I have two drop down lists, in the 1st list I have "Cars" and "Phones". If I will choose "Cars" in the 1st list, I want in the 2nd list to appear only "BMW", "Toyota", "Mazda"; if I will select in the 1st list "Phones", I want in the 2nd list to appear only "Samsung", "Nokia", Motorola".
    Can anybody help me on this issue?

    Thank you in advance.

  42. Anne says:


    I have the problem that the drop-down list does not work after I sent the excel spreadsheet as a template to my co-workers in order for them to enter their data.

    What can be the reason for this behaviour and how do I fix this?


  43. Christan says:


    I have a large data set for the drop down menu is there anyway to type the name of the specific data I'm looking for instead of trying to scroll down to find it?

    Thank you

  44. Moses Lutaaya says:

    Thank you very much for the postings. I have always leant alot from your work. However am wondering whether there is some source of data you can provide on which we can practice on these steps provided.I am still a student and have not have much access to data on which to practice on. I would be more than greatful to be provided with something. Thanks again.

  45. Emma says:

    thumps up. GREAT!

  46. rajesh says:

    thank you

  47. Naveen says:

    drop doun list in side of box like

  48. Naveen says:

    In search box not a cell

  49. Paul Turner says:


    Great article.

    I am looking to create a drop down list as described. However using this example I want to be able to pick ingredients from a list of ingredient names in the drop down box (flour, eggs etc). On doing so I want it to represent a price to be used in another formula. So for example if flour cost £1 I want to select the word flour but for it to input £1 in the cell.

    Please help.

  50. Jay says:

    This is very helpful.

  51. Paul Catt says:

    I would like to add a message when a value of a drop down list is selected
    is this possible.
    Using MO 2013

  52. Ben says:

    I can't not drop down list next two sheet, so how can I do it?

  53. RABINDRA SINGH says:

    thank you for this information on this website - wondering when creating a list, where do I go to allow the user to be able to check more than one option from the drop down.

  54. Андрей says:

    Hello, Svetlana. The article is very good. I did drop down (answers to the questions) but now I don't know how I can calculate the number for each answer (ex: for "Yes" 10 poins, for "No" 5 poins etc.
    Now I can't connect the answer cell and another cell in which I'd like to the the amount of points.
    Thank you in advance for your answer.

  55. arjit kashyap says:

    hai i want to know about that dropdown list so that it wont pick a name twice. example analyst comes twice in drop down list it should not come please tell


  56. shafini says:

    Great help!!! very useful

  57. Diamondsen Sawod says:

    Thank You

    Great Lesson

  58. Kamil says:

    Is it possible to create a drop down list in footer or headers? Thank You

  59. Yoseva Silaen says:

    Thank you for a very clear explanation about drop down...thank you thank you thank you....

  60. Annie Idul says:

    Hi, I have this problem that keeps me entering duplicate Document Code, How can I prevent this? Data Validation is not functioning I use

    = COUNTIF(A:A,A1)=1


    NT CP-PROC-xxx
    *the code starts here
    NT CP-PROC-001
    NT CP-PROC-002
    *and so on

    The cells are merged into 12

    pls help.

  61. Shey says:


    I am just wondering if its possible to display the contents of the drop down list which is quite similar to the content I am entering to the cell, for example:
    I am typing ERM1500-RPH which is one of the content of my data validation. and there are actually few more data which are quite similar to this. ex: ERM1700-RPH, ERM1800-RPH and etc. I am just wondering if I start to type "ERM" in the cell, is it possible that all the options that starts with "ERM" will appear in the drop down list options.(I hope someone can imagine what I'm trying to say.) lol...

    Please help me with this. Thanks.


  62. Parker says:

    Love this article.

    Is there a way to have the drop down list items disappear as they are selected?

  63. Sarah says:

    Thank you so much for the article- it was very clear! my question is this:

    is there a way to make a drop down table? instead of clicking on a cell and making a list come down, is there a way to make an entire table come down? the cells in the table don't have to be clickable- just drop down.

    thanks again for your amazing article!


  64. Søren W. Byebierggaard says:

    Is it possible to make a list in a list like:
    The dropdown will be e.g. a, b, c, d, e. When you stand on e.g. "a" antoher dropdown menu will appear with e.g. add 1, add 2, add 3....

    add 1
    add 2
    add 3
    add 4
    test 1
    test 2
    test 3

  65. TROY COPPEDGE says:

    Hello, I just moved into a logistics position within my company and I am using sheets that employees that no longer work with the company made. Within one of those workbooks I am having an issue with a drop down box. The way it is set up is by choosing a certain customer's name, it will draw info from mulitple cells and pull that customer's entire address and fill in cells on a diffferent sheet within the workbook. The problem I came across is that the drop down box stopped at a certain row even though there is more rows to be pulled up. I have been able to add these rows, but not the entire data from the row. I keep getting error message stating "The list source must be a delimited list, or a reference to a single row or column."

    The rows (customers name) that was set up prior to my taking the postion works and pulls all info from different columns. The rows that I have added to the drop down box will not pull all info from that cell and I cannot figure out how to do what I need.

    Please assist. Thank you.

  66. Susanta ku Pradhan says:

    Good example

  67. lauralye guitron says:

    data Validation is not avalable to select how do i make it avalable?

  68. Palani says:

    I have 6 sheets in a workbook.. In the 1st sheet(home page) I need to create a Combo box list. The list contains the sheet names 1,2,3,4 & 5. When I select 1 from the combo box it should directly go to the respective sheet 1.

    I don't know how to do this procedure using combo box. Can you help me in this.


  69. Will says:

    I'm developing a testing platform. I'm struggling to come up with a formula in Excel for the following tasks

    Add Pass/Fail/Advisory dropdown list to the spreadsheet with some functionalities.

    When Fail/Advisory selected from the dropdown list. The user have to have a few lines with the outcome results. All the results then should be linked to a different Excel file (report page) where the outcome can be displayed.

    The report page needs to be capable of being saved independently of any other pages which it is pulling data in from so we can email the report.

    I can't find formula to update Excel sheet with data from different spreadsheets. Could anyone help me out please.


  70. Phil says:

    My values in the drop-down menu don't match the values that appear in my worksheet. How do I make adjustments so I get what I expect? Thanks.

  71. Yusuf says:

    I want to learn look up

  72. Citra says:

    Thank you so much...

  73. chuck says:

    Hi svetlana,
    I'm trying to create a drop down list in a form of states.
    I'd like it to be like an online form where as you type the letters in the box it automatically takes you to that state example: As I type o 3 times it takes me to oregon, the third state beginning with o.
    Can that be done in excel or is that an html thing that only works online?

  74. NazirUllah says:

    Hi svetlana,
    I appreciate you by given quick response to every user.Please help regarding dropdown list with some parameters(Dynamic chart),as i created successfully the list but parameters value also display on the graph but limits not automatically updated with the each parameters. Thanks in advance for your support.

  75. Pavan says:

    Hi Svetlana,

    I am looking for the filter in excel like if we type any character which are in the dropdown list, that should auto populate in the below of the cell.

    Like if you find any city in When you enter the 1st letter "D" to search for "New Delhi". It auto populates all the entries which are starts with "D".

    If you could give me your mail id. I can send you the screenshot for better understanding.

    Please help me with the solution.

  76. Lippun says:

    Hay there,

    i gotta a problem....
    actually i wanna find out the name easily from drop down list by type first letter of that word by which it will be appeared the words those started with the letter i typed. kindly suggest me.

  77. Gary says:


    I want to have a drop down list of say Towns which contains data for each town, say like population, number of bars, etc

    When i click on a town the data changes

    How is this done?

  78. Tahir Ali says:

    Really helpful. I think now i dont need to search for any other website for excel help. Thanks

  79. Rahul Gomez says:

    Does not make any sense - these tutorials should be easy for everyone

  80. Amanda says:


    I am looking at having a simple "drop down box" with items to select (i know how to do this) and then if I made another "drop in box" and used the same list again, I want the "item" chosen in the first "Drop Down box" unable to be selected.


  81. Michele F says:

    Hi Svetlana,
    I am using Excel 2007, but am not experienced. I have set up an Invoice Template on worksheet no.1, and have keyed client information (names and addresses) under worksheet no.2. However I would like to be able to type in a clients name in the Invoice billing area and have a dropdown list which shows my clients names and addresses from worksheet 2 and then click on a name and the invoice automatically prefils with this information. I just don't know where to start. I have tried looking everywhere for information on how to do this which is easy to understand, but have not had any luck

    Please can you help me?

    Many thanks, Michele F

  82. MOHD says:

    Iam working in construction company make labour wages in excel we want to know formula how can we feed information like website can generate information by entering just personal number please send formula link Svetlana Cheusheva

  83. nancy says:


    how to insert a check box as an option in a drop down list. Thanks in advance

  84. Amit says:


    First of all, thank you very much for this article.
    My Question is : Can we give default name to cell instead of showing in via input message. eg. Select from Dropdown.

  85. Kriz says:

    I need to create a drop down list in below format
    if colom A1 "Student Name" then colom B1: need to show 3 category like " School fee" or "Transport Fee" or "Food Fee . Please help me

  86. Kriz says:

    I need to create a drop down list in below format

    1.if colom A1 "Student Name" then colom B1: need to show 3 category like " School fee need to enter" or "Transport Fee need to enter" or "Food Fee .


    2. if colom A1 "Teacher Name" then colom B1: need to show 3 category like " Salary need to Enter" or "Allowance need to enter" .

    Please help me

  87. Kriz says:

    I need to create a drop down list in below format

    In Colom A1 drop down
    1. Student Name
    2. Teacher Name

    1.if select colom A1 "Student Name" then colom B1: need to show 3 category like " School fee need to enter" or "Transport Fee need to enter" or "Food Fee .


    2. if select colom A1 "Teacher Name" then colom B1: need to show 3 category like " Salary need to Enter" or "Allowance need to enter" .

    Please help me

  88. Kevin says:

    I would like to know how to use a "clear" button for a dropdown menu. I have a dropdown menu that lists hockey players. But if I want to re-use this tab to select another group of players, it would be awesome if I could click one button to clear all tabs and revert to the top selection word "Forward". When you select a name from the drop down, it populates cells to the left of the drop down. When you scroll to the top of the drop down and click "Forward" or "Defensemen", it defaults the cells to the left back to normal (no name or team or salary number). I'm pretty sure I'll have to create a button and use a macro but not super knowledgeable on how to do this. Your help would be greatly appreciated.

    Thanks very much.

  89. Сергей says:

    Спасибо,Светлана,за подробную статью. По-русски не публикуетесь, где-нибудь. Буду признателен за ссылочку.

    • Добрый день, Сергей. Приятно знать, что к нам заглядывают и соотечественники :) У нас пока есть только английская версия сайта, и поэтому и все мои статьи тоже на английском. В перспективе возможно появится и русская версия, и тогда переведем самые популярные статьи.

  90. Kory Bush says:

    I have made the drop downs and they work great. However you can still type in them how do I make it so you can only choose from the drop down?

  91. Abhijit says:

    Suppose I have different columns (say A,B,C) with different values for fixed fields (say X,Y,Z). How do I create a drop-down of columns A,B and C such that when I select A the corresponding values of X, Y and Z is displayed?

    • Lam3388 says:

      By far this is one of the most detailed drop down list tutorials.
      For below scenario, can a drop down list be created depending on the first column selection?
      For example
      Column A Column B
      1 Amy Amy
      2 Bob Bob
      3 Bob Bob_wife
      4 Chris Chris

      Provided Bob is selected, a drop list will show only "Bob, Bob_wife"
      Much appreciated for your time.

  92. Nandhini says:


  93. smobaidul says:

    Many Many Thanks

  94. g-h says:

    It is make some of schedule.

  95. g-h says:

    It is make some of schedule. for work or class. Thank you too much.

  96. stg says:

    These are really great tips. But is there an easy way to make drop-down list drop when clicking or double-clicking anywhere in the cell, not just on the arrow?

  97. Vicky says:

    I created a Dropdown list, and it worked out well. But when I save the sheet, close and open it again I don't see the dropdown lists I created, and I'm not able to figure it out why, Please help me out.

  98. din says:


    I have created four cloumn names called a,b,c and d.I set Data Validation by selecting the Allow field as LIST and Source field as Yes,NO,N/A for B column.

    My quetion is here : Whenever I select "yes" from the drop down list in B column , it should show the Dialogue box as "Fill the coulmn C and D".

    Could you please let me know how to create this in excel.

  99. embran says:

    Thank you very much

  100. ram says:

    I am looking for a requirement like I have a,a,b,c,d,c,d in a column and now I want the whole columns unique values to be as a dropdown/list in another cell.
    Is it possible?

Post a comment

Thank you for your comment! When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to your query. We cannot guarantee that we will answer every question, but we'll do our best :)