Excel Drop Down List
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 listOn 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:.Creating drop down lists with comma-separated valuesThis is the fastest 3-step way to create a drop-down box in all versions of Excel 2016, 2013, 2010, 2007 and 2003.
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. It's a good idea to sort your entries in the order you want them to appear in the drop-down menu.
Create a named range.You can actually skip this step and create your drop-down list, 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.
Excel 2003 and older require the dropdown list to be on the same worksheet. Apply Excel Data Validation.On the Excel ribbon, navigate to the Data tab and click Data Validation.5. Configure your drop-down list.In the Data Validation window, go to the Settings tab, and choose the following options:. 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 the OK button to finish creating your drop-down list. 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 workbookThe 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. 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. 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!Sourcelist. You have to enclose the workbook's name in apostrophes (') if it contains any spaces.
For example: ='Source Book.xlsx'!Sourcelist 3. Apply Data ValidationIn 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 dynamic dropdown list from another workbookA 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. In the main workbook, apply Data Validation in the usual way.Excel Data Validation does not workThe 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. Mathtype 6.9b product key share. 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.
Excel Drop Down List Not Working
Unlink the table or remove the table formatting, and try again.Additional options for the Excel drop-down boxIn 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 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 listAfter 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 listIf you've created a, 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. 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 cellsIf 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 rangeIf you have created a, 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. To avoid the necessity to update the named range's references after each change of the source list, you can create a. 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. 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. Hello, SvetlanaThank 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.
Excel Drop Down List Tutorial
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. 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 TastyOragne Color GoodSo 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. 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. 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= DateColumn C= Store NameColumn D= Cost in USDColumn 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-Walmart-$26.50-Grocery-Sams Club-$16.50-Grocery-Walmart-$26.25-Grocery-Costco-$6.50-Grocery-Walmart-$10.50-Grocery'Column S' & 'Cell T12'After Entry - T12=$26.50After Entry - T12=$43.00After Entry - T12=$69.25After Entry - T12=$75.75After Entry - T12=$86.25Your help is very much appreciated and look forward to getting a result for this question.Thank you,Sam Arthur. 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.