How do I make an automatic drop-down list?

Sep

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

by | updated on July 26, 2021

Nội dung chính Show

Creating a drop down list in Excel: static, dynamic, from another workbook
How to create an Excel drop-down list
Creating drop down lists with comma-separated values
Creating an Excel drop-down list based on a named range
Excel data validation list based on a table
Making a drop down box based on a range of cells
Create a dynamic (automatically updated) Excel dropdown
Creating a drop-down list from another workbook
A static dropdown list from another workbook
A dynamic dropdown list from another workbook
Excel Data Validation does not work
Additional options for the Excel drop-down box
Display a message when a cell with the dropdown is clicked
Allow users to enter their own data in a combo box
How to edit an Excel drop down list
How to delete a drop-down list
Removing a drop-down menu from selected cell(s)
Deleting an Excel drop-down list from all cells in the current sheet
You may also be interested in
221 comments to "Creating a drop down list in Excel: static, dynamic, from another workbook"
Post a comment

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 a drop-down list in Excel
Make a drop-down list from another workbook
How to fix Excel data validation not working
Additional options for the Excel drop-down box
- Display a message when a cell with the dropdown is clicked
- Allow entering other data in a combo box
How to edit an Excel drop down list
How to delete a drop-down box

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:

Drop-down box based on a list of values
Drop down box based on a named range
Excel data validation list from table
Drop down list based on a range of cells
Create a dynamic dropdown in Excel

Creating drop down lists with comma-separated values

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.

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

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.

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!

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.

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.

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

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:

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.

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.

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.

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:

Excel data validation list based on a table

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. Here's how:

Create a named range for a column of data, not including the header cell in the table. To refer to the data cells, you can use one of the following methods:
- Point to the top of the heading cell, and when a black down arrow appears, click to select all the cells in the table column without a header. Then, type the name for the selected range in the Name box.
- Open the Name Manager and enter a structured reference such as =Table[Column] in the Refers to box:
Create a data validation list based on a named range like explained in the previous example.

Why may you 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.

Making a drop down box based on a range of cells

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

Type the items in separate cells.
Select the cell where you want the drop-down list to appear.
On the Data tab, click Data Validation.
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.

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.

You start by creating a usual dropdown based on a named range as described above.
In step 2, when creating a name, you put the following formula in the Refers to box.
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
Where:
- 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.

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.

Creating a drop-down list from another workbook

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.

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.

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

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.

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

Create a range name in the Source workbook with the OFFSET formula, as explained in Creating a dynamic drop-down.
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.

Additional options for the Excel drop-down box

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.

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.

The result in Excel will look similar to this:

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.

In the Data Validation dialog (Data tab > Data Validation), go to the Error Alert tab.
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.
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
  is recommended if your users are likely to input their own choices quite often.
- A Warning message
  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.
And this is how your customized warning message may look like in Excel:
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
Editing a drop-down menu based on a range of cells
Editing an Excel drop-down list based on a named range

Editing a comma separated drop-down list

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

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.
Click Data Validation (Excel ribbon > Data tab).
Delete or type new items in the Source box.
Click OK to save the changes and close the Excel Data Validation window.

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.

Head over to spreadsheet containing the items that appear in your drop-down box, and edit the list in the way you want.
Select the cell or cells containing your drop-down list.
Click Data Validation on the Data tab.
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.
Click the OK button to save the changes and close the window.

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.

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.
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
  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.
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)
Deleting a drop-down list from all cells in the current sheet

Select a cell or several cell from which you want to remove drop down boxes.
Go to the Data tab and click Data Validation.
On the Settings tab, select the Clear All button.
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.

Select any cell containing your drop-down list.
Click Data Validation on the Data tab.
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.
Click the Clear All button to delete the drop-down list.
Click OK to save the changes and close the Data Validation window.
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!

You may also be interested in

Making a cascading (dependent) Excel drop down list
How to create calendar in Excel (drop-down and printable)
Data validation in Excel: how to add, use and remove
Video: how to create drop-down lists in Excel worksheet
How to add, edit and delete checkboxes and drop-down lists in Google Sheets

Excel: featured articles

Merge multiple sheets intoone
Combine Excel files into one
Compare two files / worksheets
Merge 2 columns in Excel
Compare 2 columns in Excel for matches and differences
How to merge two or more tables in Excel
CONCATENATE in Excel: combine text strings, cells and columns
Create calendar in Excel (drop-down and printable)
3 ways to remove spaces between words in Excel cells

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

Older Comments

afzal sohail says:
November 24, 2021 at 10:34 am
Hello,
It is a great article, I am from Pakistan when i am making a list like a1,a6,a12,a20 using this range hold the Control Button but this range not accepted in the list and giving a error I also used semi colon(;), but is also not accepted, it is only accepted continuous range like a1:a20 so please help
Reply
MORAGUDI PRASANNA KUMAR says:
September 15, 2021 at 7:15 am
Very very helpful artical..
Thank you so much..
Have all success in your life.
Reply
JC Provost says:
June 19, 2021 at 9:51 am
VERY, VERY good & useful explanations. Thank you for taking the time to do this.
Reply
Joe says:
May 9, 2021 at 2:14 am
Hello Svetlana,
Thank you for a great tutorial and your through analysis and step by step presentation. You also did a wonderful job to explain the different options and pros and cons of each.
The only step that did not work for me was when I put the Table Name as the Data Source. The Database is in Sheet1, and the Table1 is in Sheet2 (column A, rows 2:30 with header on row 1).
Your input will be much appreciated. Thank you.
Joe
Reply
- Svetlana Cheusheva (Ablebits Team) says:
  May 12, 2021 at 4:49 pm
  Hi Joe,
  Thank you for your kind words about this article! You are right, the table method does not work in the current Excel version, though I can swear it did work back in 2014 when the tutorial was written.
  Anyway, you can use the following workaround:
  - After creating a table, create a named range for a column of data without including the heading cell. For example, you can define the name "Ingredients" that refers to =Table1[Ingredients].
  - And then, create a data validation list based on the name.
  This extra step may sound like a needless complication, but it does make your drop-down list expandable - when a new item is added to the table, it will appear in the list automatically.
  I will update this part of the tutorial ASAP. Thank you for drawing my attention to this! :)
  Reply
luke says:
March 19, 2021 at 8:42 pm
hello svetlana. Is this article the Italian translation of your article?
https:// excelacademy.it/6623/creare-un-elenco-discesa-excel/
Reply
- Svetlana Cheusheva (Ablebits Team) says:
  March 22, 2021 at 8:47 am
  Hi Luke,
  I do not speak Italian, but the contents seem very much alike :)
  Reply
Anne says:
January 12, 2021 at 1:31 pm
Another great article, thank you so much!
I've been using data validation based on table columns as my standard method since quite a while. However, there's one thing that annoys me: You have to create a separate named range for the table column (as you cannot put the table column reference as source into the data validation directly). Let's say I call the named range "Dropdown" and it refers to "Table[Column]" and has workbook scope.
Now, when I copy any sheet within the workbook a new instance of Dropdown is created automatically that has the new sheet as scope. I didn't find any way to avoid that. It even happens when I copy a sheet that does not use Dropdown at all.
Do you know how that can be avoided?
Reply
Elmer Milan says:
September 25, 2020 at 1:01 am
Thank you very much sir, for the simplest explanation on how to edit items on the list. It made me reduce my stress for editing a form needed ASAP. Thank you very much.
Reply
SKG says:
September 1, 2020 at 1:31 pm
Drop down list is not appearing on selection of arrow from begining.
Reply
Jeanette says:
June 10, 2020 at 1:48 pm
Hi. Please have a look at the first "drop-down via comma-separated" option. You mention in step 3 to use a comma (,), however this should be a semi-colon (;).
Otherwise thanks for the great help.
Reply
- Alexander Trifuntov (Ablebits Team) says:
  June 11, 2020 at 11:49 am
  Hello Jeanette!
  The use of a semicolon or semicolon in formulas is determined by the regional settings of your Windows.
  Reply
Gaurav Mishra says:
April 24, 2020 at 7:32 pm
Great explanation. It solved my query in minutes. Thanks a lot!
Reply
Alvaro Gavilán says:
April 20, 2020 at 12:28 pm
Thank you for this very useful post! I was searching in the internet about providing a table as a source for the drop-down list. It solved my problem.
Reply
Ben says:
August 8, 2019 at 5:32 pm
Dynamic drop down does not work, as prescribed.
Reply
Anil Chhabra says:
May 29, 2019 at 5:00 pm
I created a drop down list for expense type and created vlookup for tax rate based on expense type. However if user picks any expense type and then delete the entry then it gives an error message of NA in tax amount column. How can it be taken care of?
Reply
Mallika says:
May 27, 2019 at 9:59 am
I have a query.
I have already populated some values in the drop down box.But now I want to insert a value in between the list of drop down values.
example,
I have suppose list of values 1,2,3,4....in my drop down list.
but now I want 1,2,6,7,3,4....(It means that I have to Insert two new values in between)how to do this? I have analysed all the ideas . Could anyone help in this?
Reply
Sridhara Rao says:
January 2, 2019 at 6:08 am
Text size for drop down list is coming small. Is there a way to change the text size of the drop down list items.
Reply
Mike_B says:
October 19, 2018 at 10:57 am
I'm in the end. I didn't find solution to my problem with drop down menu.
The problem and question is, how to update already selected values from drop down menu, after that I changed original values in source table for drop down menu?
Because in Excel I have a list of hardware in many worplaces. And from time to time when workplace will change name I need to change it in Excel too.
And easiest way is to change/edit it in table for drop down menu, not to change it by add new workplace to table for drop down menu and then find hardware and select new work place from drop down menu. But now, when aj change some values in table for drop down menu, this change is not reflected in my list.
Just for sure: I'm not talking about adding new rows for drop down table.
Reply
Florence Matsveru says:
September 29, 2018 at 2:24 pm
Great lesson indeed! Thank you. Is there a way of choosing more than one item from the drop down menu. I do not understand VBS or anything that is outside Windows at all!
Reply
Joseph Awuol says:
September 14, 2018 at 5:49 pm
Good stuff!
Reply
Alex Findlay says:
August 1, 2018 at 11:15 am
Hi,
Awesome article!
However, when doing the dynamic one I copied the code in and changed the sheet name accordingly. However, it will only give me 88 rows and anything after this gets cut off.
Any idea what I am doing wrong?
Reply
Steven says:
July 14, 2018 at 6:18 pm
Dear all,
I have a problem with defining the source of the drop down list.
When I click on another sheet for referencing the table where the list is, I can't. Excel just annoys me with an error sound and nothing shows. It doesn't go anywhere. I can't reference anything. What am I doing wrong? What should I do to overcome this. I'm using excel 2007.
Reply
Shashika says:
June 1, 2018 at 7:34 am
thank you very much for your suport
Reply
BENJAMIN says:
March 8, 2018 at 5:14 pm
I want to create a drop down in a table form.
eg. code budget actual variance percentage
Reply
MickeyB says:
March 1, 2018 at 3:37 pm
Great job on this link. I was able to create a list on one workbook referencing another workbook and it worked perfectly. I now use the list to select parts and then populate other columns using VLOOKUP. Thanks for the article.
Reply
Carl says:
February 20, 2018 at 8:46 pm
Hi,
You are very helpful!!!
I have a drop-down list with 8 items. When I open the drop-down, only 7 items appear and I have to scroll up to see the first item. Is there a way to always have the entire list show each time? I'm afraid my users will miss an option on the list.
Thanks.
Carl
Reply
Jackie says:
February 9, 2018 at 7:36 pm
Someone created a table for me but I need to add items to one of the lists which is located on a separate sheet. When I add the items to the list, they do not appear on the dropdown on my input sheet; only the original list appears. How do I add extra cells? I tried to do DataValidation but it does not add them. Thanks
Reply
Ahmad says:
January 28, 2018 at 12:56 pm
Hi
I have a Worksheet_SelectionChange event procedure in a worksheet which make drop-down list automatically by selecting a specified cell in a column. But, after creation some drop-down list and selection of other cells for new drop-down list creation, I couldn't find the previous dro-down lists location.
Thanks
Reply
cham says:
January 15, 2018 at 1:57 pm
Hello,
How do you keep drop down box in the shared workbook. It works well in the unshared mode.
Thanks
Reply
vener says:
November 24, 2017 at 12:58 am
Is it possible to create a conditional based data validation and how?
Reply
Nikkas says:
October 9, 2017 at 8:58 pm
Hi
If I want to be able to change chosen list item in a list, can i do this non-vbs?
Reply
Betty Goulart says:
September 5, 2017 at 10:16 pm
Hi,
I want to create a drop down list that when selected shows both the item number and description but once selected would only show the item number.
For example, I would select the 2 - Blue but once selected, the cell would only show 2.
Drop Down List
1 - Green
2 - Blue
3 - Yellow
4 - Brown
Please let me know if it is possible.
Thanks,
Betty
Reply
- Natalia Sharashova (Ablebits Team) says:
  September 6, 2017 at 7:23 am
  Hi, Betty,
  I believe you need a VBA code for your task. But I'm afraid we won't be able to help with that.
  I can only suggest you asking around Mr. Excel forum for it. They have a section there where they help with such codes.
  Sorry I can't assist you better.
  Reply
Robine Ogwal says:
September 5, 2017 at 12:35 pm
Perfect , Thanks a lot. now i feel like a genius.
it worked well on office 2016.
Reply
Nick says:
August 29, 2017 at 9:31 am
Hi,
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 a value, for the next row I want to disallow the selected value from first row, or I want the selected values to disappear for the drop down list in the next rows?!
Thanks in advance
Reply
- Natalia Sharashova (Ablebits Team) says:
  August 29, 2017 at 10:03 am
  Hi, Nick,
  please look at this article to learn more about creating dependent drop-down lists.
  Hope you'll find the answer to your question in one of the examples.
  Reply
Samuel Arthur says:
July 10, 2017 at 6:01 pm
Hi Svetlana Cheusheva,
This article is super easy and very good. I created the drop down option from Data Validation options and it works perfect - I don't have to enter the category again and again anymore.
I have a question regarding drop down list and auto fill to a different cell. My excel sheet is as below:
Column B= Date
Column C= Store Name
Column D= Cost in USD
Column E= Purpose (This is where my drop down list is choosen from, the Validation criteria list is obtained from Column S (List has Rent, Grocery (Cell=T12), Utility etc)
So, I would like the Grocery Cell (T12 in column S) to fill automatically and get the cost updated ( up to date cost). Such as below:
Data--Store Name--Cost--Purpose
07/01/2017--Walmart--$26.50-Grocery
07/04/2017--Sams Club--$16.50-Grocery
07/06/2017--Walmart--$26.25-Grocery
07/10/2017--Costco--$6.50-Grocery
07/11/2017--Walmart--$10.50-Grocery
"Column S" & "Cell T12"
After 07/01/2017 Entry - T12=$26.50
After 07/04/2017 Entry - T12=$43.00
After 07/06/2017 Entry - T12=$69.25
After 07/10/2017 Entry - T12=$75.75
After 07/11/2017 Entry - T12=$86.25
Your help is very much appreciated and look forward to getting a result for this question.
Thank you,
Sam Arthur
Reply
- Samuel Arthur says:
  July 10, 2017 at 9:08 pm
  Correction:
  "Column T" & "Cell T12" value to be updated.
  Reply
  - Samuel Arthur says:
    July 16, 2017 at 5:48 am
    Actually, I got the solution. Its just a formula to use instead of Vlookup. Thanks.
    Reply
Mozelle says:
June 7, 2017 at 7:55 am
15 Nick Williams Jersey.Cheap 2014 New Cheap Jerseys Sale,
MLB/NBA/NFL/NHL/NCAA Jerseys From China Free Shipping, NHL Jerseys, NBA Jerseys, inexpensive jerseys From factory.
Reply
Reza says:
May 3, 2017 at 12:49 pm
Using comma dont work for list, i had to use ";" in between the values.
Ex:
Yes;No;Maybe
Did MS change this? I'm using 2013
Reply
Sumant says:
April 4, 2017 at 7:23 pm
If I have an EXCEL drop down list and I want to enter free form text in one of the drop downs, and let it be part of the drop down?
Example : I have static drop down messages in cells A1 to A5. If I wanted to enter free form text in cell A6?
Thank you.
Sumant
Reply
RAVI KUMAR says:
March 31, 2017 at 11:04 am
the list in "Target Book" works only when the "Source Book" is open.
how i solve it
Reply
Jerry says:
March 23, 2017 at 1:36 pm
Thanks. Great article
Reply
deepak kumar singh says:
March 9, 2017 at 2:37 pm
how to add multiple formulae drop down list in on the cell where we can choose sum, min, max or more function .please provide me solutions.
like cell a is heading and a2:a20 id numeric data but we need a list of formula where we can choose sum, min, max or more function.
Reply
Michelle A says:
March 3, 2017 at 8:05 pm
I have created a dependent drop down list. But when I click on the drop down arrow, it shows the values in reverse order. My sheet has 1, 2, 3 in numerical order. But the drop down shows 3, 2, 1. Any way to fix this?
Reply
Nick beswick says:
February 23, 2017 at 3:32 pm
Hi Svetlana
is it possible to link a value to a drop down list ie oven cure 1 = 5hrs
oven cure 2 = 3hrs so this can be intergrated into a cure schedule
Reply
Aravind says:
February 13, 2017 at 10:07 pm
hello Svetlana
from the drop down list if i select some, it should display in the every particular selected cell of the column, can this be done, thank you..
Reply
Vijay says:
January 31, 2017 at 12:36 pm
Hi Svetlana,
Using with drop down list(sheet1, Sheet2, Sheet3), is it possible to jump (active sheet) to another sheet as described in drop down list.
Thanks
VIJAY
Reply
Lindsay says:
January 12, 2017 at 10:59 pm
Hi There,
This has been super helpful in creating my drop down menu... my only wish is that my employees could start typing an address and have it come up from the drop down menu? We have multiple addresses we need them to select from on a daily basis but I thought there may be a way for them to, for instance, type into the drop down menu (almost like a search, 1343 w_____ would bring up 1343 Whitby- from the drop down menu?
We are trying to make it quicker and more accurate for myself when entering payroll. so that the guys stop making typo's on their addresses when entering their timesheets to which job they were working .
I believe DEE (see above) is asking the same question.
Thank you for your help!
Lindsay
Reply
dee says:
January 12, 2017 at 2:12 am
Hi Svetlana,
I want to make a list of name with drop down list but searchable. Can you help me on this? Thanks.
Reply
ANKIT says:
January 10, 2017 at 9:55 am
HI,
Thanks for the detailing about DropDown.
I need to know, what if, I don't want to leave the cell Blank.
I want my cell to display 'Select' before selecting the option from drop down.
Please suggest.
Reply
Destine says:
December 13, 2016 at 6:49 pm
Hello,
I was wondering is there a way to create your drop list where in the drop down it has the Ingredient and description of Ingredient but once I choose my option it just display the Ingredient and not the description. For example, in the drop list (when the arrow is clicked on) it has:
Eggs - yellow yoke
Broccoli - green vegetable
but once I choose Eggs, it just display "Eggs" in the cell and not "yellow yoke".
Is it possible?
Reply
Nick says:
December 13, 2016 at 7:14 am
Hi, i need help on drop down list issue below : -
I got a file from others, there is cell containing drop down list and i need help how to edit the item name inside the drop down list because i couldn't find where is the source. Tq
Reply
Reshma says:
November 29, 2016 at 6:12 am
This is very useful to my office works.
Thanks a lot.
Reply
Troy says:
November 22, 2016 at 10:44 am
Hello,
Thank you for this great source of information!
I would like a second list to populate information depending on the selection of the first list.
For example in the first list column A we have male and female. Second list column B has Pregnancy Test and NA. So if user selects Male then the only available item in list B should be NA.
I've looked at the IF formulas but am unable to find the correct combination.
Thank you for your advice.
Troy
Reply
- Renat Tlebaldziyeu (Ablebits Team) says:
  November 22, 2016 at 10:56 am
  Hi Troy,
  Please look at the following article, it should help:
  https://www.ablebits.com/office-addins-blog/2014/09/30/dependent-cascading-dropdown-lists-excel/
  Reply
  - Troy says:
    November 22, 2016 at 12:31 pm
    Amazing! Thanks thats exactly what I needed!
    Best Regards,
    Troy
    Reply
Roxy says:
November 9, 2016 at 7:25 pm
This is incredibly helpful. But is there a way for more than one item on the list to be selected? For your example of the ingredients - would someone be able to select both flour and eggs? Or would you suggest two columns both with the same drop down list?
Reply
- Svetlana Cheusheva (Ablebits Team) says:
  November 10, 2016 at 8:33 am
  Hello Roxy
  Excel's dropdown allows selecting only one item. So, in your case creating two drop down lists with the same items sounds quite reasonable.
  Reply
Marita says:
November 4, 2016 at 8:05 pm
my drop down list contains the months of the year. when i click a different month can the body change to the corresponding month? I am keeping attendance. so when i click to december i want the record to start blank.
Reply
Excel_Noob says:
November 4, 2016 at 10:06 am
Hello there, this is a great post! Thank you so much.
I have a query - What if there are multiple entires of first Cell in the table with different dependant values?
For Ex:
Orange | Fruit | Tasty
Oragne | Color | Good
So the when I select first column as Orange, it should show up both Fruit and Color in second drop down.. but since you INDEX(), it only shows first match. (Fruit) .
How to make multiple entries show up? Any help will be greatly appreciated.
Again, thank you so much for the great blog.
Reply
Bharath says:
October 20, 2016 at 3:19 pm
Excellent post!
Reply
John Jackson says:
October 16, 2016 at 9:31 pm
Hello, Svetlana
Thank you, great blog if you could help with one more problem I would be grateful. I am trying to populate a data validation list based on a table that works as follows Col A. reps a State, Col B. City, Column C. Postal ZIP Code. No cells in table are blank i.e. A2:A5 = Mississippi then B2:B5 = Tupelo then C2 though C5 are different. Is there any way to make a Zip Code selection based on the City?
The table has 9 States and 38 Cities and approximately 122 Zip Codes.
Thank you again
Reply
- Renat Tlebaldziyeu (Ablebits Team) says:
  November 22, 2016 at 11:02 am
  Hi John,
  Please look at the following article, it should help:
  https://www.ablebits.com/office-addins-blog/2014/09/30/dependent-cascading-dropdown-lists-excel/
  Reply
Jhay says:
October 15, 2016 at 6:38 am
Hi, I have a dropdown list wherein it allows user to select their names that will reflect the time that they have finished doing the task. My question is, I want to prevent other users from selecting other people's name. in short, they should only select their own name. How will I do that?
Reply
joel says:
October 1, 2016 at 5:30 pm
i need help. I have a combo boxes and I want to use it to be entry data into another worksheet serially. the post above is very simple and understandable. good work thanks
Reply
Pilofito says:
September 28, 2016 at 8:51 am
Dear Svetlana Cheusheva
This post is so nice and simple for me to learn how to do and useful for me to finish my work Thank a lot.
Be successful in you work
Best Wishes,
Pilofito
Reply
- Svetlana Cheusheva (Ablebits Team) says:
  September 28, 2016 at 9:24 am
  Dear Pilofito,
  Thank you so much for your kind words!
  Reply
Patricia Brice says:
September 26, 2016 at 9:44 pm
I am trying to create a worksheet with each column being a month of the year but then making that month into 4 weeks within a drop down box. I have done that but when I go to change the weeks, the data does not change with the box I choose. I am not sure how to fix this.
Please Help
Patricia
Reply
Swati Kanodia says:
September 26, 2016 at 9:59 am
Hi..the post is really useful.. i have one small question..what do i so if i have multiple words in my list..for eg: if i have to type full names?
Reply
- Svetlana Cheusheva (Ablebits Team) says:
  September 27, 2016 at 9:02 am
  Hello Swati,
  No problem, just type the names like usual with spaces in between in some range, and then create a drop-down list based on that range.
  Reply
ESAYAS says:
September 19, 2016 at 10:11 am
THANK YOU!!!
Reply
Charles Kipkemoi says:
September 3, 2016 at 1:43 pm
Your demonstration most helpful. Keep it up!!
Reply
Jagadish Dash says:
August 30, 2016 at 11:39 am
hi,
i made a drop down list, but want to type the starting alphabet to select the item, but it is only working by pressing arrow.
please help..
Reply
- Alexander Frolov (Ablebits Team) says:
  August 30, 2016 at 12:43 pm
  Hello Jagadish,
  Unfortunately, it is not possible in standard Excel drop-down lists.
  Reply
  - Jagadish Dash says:
    August 31, 2016 at 9:40 am
    Thank you Alexander,
    Please speak any other option if it is not possible in standard excel drop down.
    Reply
    - Sid says:
      September 30, 2020 at 7:45 pm
      He has to write.
      Reply
Daniel says:
July 31, 2016 at 10:16 pm
OK I must be missing something.
I have a data sheet with all my drop-downs built, unfortunately it only works on the data sheet. But on page where I need to use them on page One the old lists are still their. I want to remove the old lists and put the new list with/button on Pg 1.
And I am forced to right click and choose from drop-down. Would like a button when I click on the field.
Reply
Carl Morgan says:
July 6, 2016 at 9:52 pm
Hi,
I have created a yes no drop down cell but now I can't fill it with a colour.
Can anyone help?
TIA
Carl
Reply
- Sid says:
  September 30, 2020 at 7:44 pm
  Go to cell style to change color.
  Reply
Judas says:
June 14, 2016 at 12:59 pm
interesting and helpful
Reply
Chhorn kheng says:
May 27, 2016 at 6:13 am
Sorry can tell Me About make Attendance For summery list excel
thank .
Reply
Bob James says:
April 23, 2016 at 3:17 am
Is there a way to have a drop-down that is list of text descriptions, but once selected it is an ordinal number? For example, "Don't know", "Limited", "Meets", "Exceeds" and puts 0,1,2,3 so I can add the values. I am using this to provide a ranking but the ranks have different descriptions for the different columns of criteria.
Reply
Ghizali Ahmed says:
March 30, 2016 at 12:11 pm
Hi,
Quite a thorough tutorial, but I am trying to create a list where i can jump to specific item using keyboard in a long list (e.g. In a list of 1000 names I have to select a name starting with "t", how can i do that without scrolling from mouse)
Regards,
Reply
Randy says:
March 27, 2016 at 7:37 pm
MY COMBOBOX KEEPS PUTTING TEXT IN A NUMBER CELL AND CHANGING THE CELL TEXT AND NOT A NUMBER. Is there a fix for this? It is a activex combobox I have ZIP codes being pulled up in a INDEX & MATCH =INDEX(info,MATCH(E10,ZIP,0),6) E10 is where I drop the zip code in. If I type the zip code in it works perfect but if you I use the combobox it changes all my fields to N/A (city,state,etc). Each time I use the combobox to put the value in E10 I get a error symbol next to E10 "this cell is formatted as text" there is a drop down to convert it to a NUMBER and once I do that all the N/A in the fields goes away. PLEASE HELP and Thank You
Reply
Fawzi says:
March 24, 2016 at 6:20 am
How to put shapes instead of txet or numbers in drop down list in Excel?
Thank you,
Reply
Nelly says:
March 15, 2016 at 6:18 pm
I have a list of actors and list of their respective films in a sheet, in another sheet I have the column Actors and Films column. The user selects an actor from the dropdown list (about 4 lines long). Then a film is chosen from a dropdown list (about 15 lines long) of films about the chosen actor.
I would like that , when a title film is selected this title will not be seen next time the user choose the same actor, but should be available for the next time the file is open or the 15 lines run out.
Ideally a message should flash like "That film has already been chosen, try another one". Any help will be much appreciated. Thank you
Reply
uday says:
March 5, 2016 at 9:08 pm
I want to make a drop down list for example on D3 1 to 5 by entering just 5 on B2 cell.
Reply
Karen says:
February 12, 2016 at 11:27 pm
I have the drop down list working. But, is there any way to be able to start typing until the correct choice pops up, move on the the next drop down list.
Reply
Pisey says:
January 12, 2016 at 1:37 am
Hello!
I create a drop down list for 10 column and then I want to add more column but I can't insert or delete the column. The only way I can do is drag it down to more make more column. SO what's the problem? and what can I do to insert column beside drag down?
Thank you.
Reply
- Maria Azbel (Ablebits Team) says:
  January 28, 2016 at 10:52 am
  Hello, Pisey,
  For us to be able to help you better, please send a sample table with your data in Excel to . Please add the link to this article and your comment number.
  Reply
Farhan says:
January 10, 2016 at 7:59 am
Hi,
Is it possible in Excel that the value list (drop-down list) shows like (Value Description) which are in two columns, but when user clicks the value, only value is selected in the destination cell
Reply
- Maria Azbel (Ablebits Team) says:
  January 28, 2016 at 10:53 am
  Hello, Farhan,
  Most likely you need a VBA for this task. Sorry we cannot help you with it.
  Reply