The tutorial demonstrates 4 quick ways to create an Excel 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. 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 , , and 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.

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. However, this is not the best way to create an Excel drop-down list, especially if you want the same list to appear in multiple cells, and here's why If you want to edit your Excel drop down list at some point in the future, you would have to change every cell that references the Data Validation list.

This may not be a big deal if you have just a few such cells residing on the same sheet, but if you have dozens or hundreds scattered across different worksheets, it may take quite a while to locate and edit them all.

Anyway, if you decide to stick with this easiest approach, see the detailed guidance on how to edit a comma-separated drop-down list. This method of creating a drop down list in Excel takes a bit more time, but it may save even more time in the long run. 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: 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. Why you may want to use a table? First and foremost, because it lets you create a dynamic drop-down list and sets you free from the necessity toupdate the named range reference when you remove or add new items to the source list.

Simply click in the cell where you want to have the drop-down list. This can be in the same sheet where your list of entries is located or in a different worksheet. You can also select a range of cells, or the entire column. On the Excel ribbon, navigate to the Data tab and click Data Validation. In the Data Validation window, go to the Settings tab, and choose the following options:.

If the source list contains more than 8 items, your drop-down box will have a scroll bar like this: Creation of an Excel drop-down box based on a range of cells is very similar to creating a drop-down list based on a named range with only 2 differences:. 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. 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 Data Validation list will get updated automatically! In Microsoft Excel, the OFFSET function returns a reference to a range consisting of a specified number of rows and columns.

You can use it in various scenarios when you need to get a dynamic, i. 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 the Data Validation list.

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. Open the workbook that contains the source list, SourceBook. Open the workbook in which you want the drop down list to appear and create a name that references your source list.

A dropdown list created in this way will get updated on the fly once you've made any changes to the source list. The Data Validation option is greyed out or disabled? There are a few reasons why that might happen:.

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:. The result in Excel will look similar to this: By default, the drop-down list you create in Excel is non-editable, i. However, you can allow your users to enter their own values in the box. 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.

And this is how your customized warning message may look like in Excel: 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. If you've created a comma separated drop down box , proceed with the following steps:. 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.

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. If you no longer want to have drop-down boxes in your Excel worksheet, you can remove them from some or all cells. This method removes the drop-down menus from the selected cells, but keeps the currently selected values.

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. 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! Hi i'm looking for a solution. In sheet number 1 i have products name,product part number and description. 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. 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.

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. 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:. 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.

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.

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 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. 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? If you need a cascading drop-down where values in the second drop-down box depend on the selection in the first one , you'll know how to do it in my next article that will be published tomorrow: If you want something different, please send a sample workbook with your data to support ablebits.

We'll try to help. Just want to let you know that the "Cascading dependent Excel drop down list" tutorial is published:. Hi Svetlana, The article is great, very cool indeed but I was looking for something alittle different if at all possible.

I sent an email to support ablebits. I got it, thank you. I've consulted our Excel gurus and regret to tell you that your task cannot be fulfilled using formulas, at least we do not know any way. Sorry for not being able to help you with this. 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.

This artical is very good. Can you please describe the OFFSET formula? Means in which scenarios it use. You can use it in various scenarios when you need to get a dynamic i. I have just added a bit more details about OFFSET, please check out the newly added section: The OFFSET function in more detail.

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: 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! Whoops i miss typed Sorry, I don't quite understand why you use Offset in this formula. If you can send a sample workbook at support ablebits. First off thank you for all your advice and effort, its greatly appreciated.

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. 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.

Once selected we require C11, C12, C13 and C14 in that sheet to be filled with the corresponding information of that staff member. Could you also include the formula for getting the data from a separately saved workbook we have the staff list stored in the cloud. I have a similar issue so hope to get your questions answered. We have two options in the drop-down list, Cash and Finance. We will appreciate the assistance! Ivan, a lookup function can be used in this case.

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!

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. I was looking at VLOOKUP and think maybe I can use that but am not sure how.

I've made my list, and insisted on only entries that match those in the list Can the Excel list automatically limit the visible list as you begin typing Can it do this? As far as I understand you want to have a drop down list with search suggestions like in Google. If so, you can find the steps and the formulas to create such a drop-down in this article: You have a huge mistake in method one.

In fact, you can do either way - type directly in the Source box or select the cells with your entries. The latter approach is described in " Drop down list based on a range of cells ". Or, are you talking about something different? 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. These data validation rules will not be saved ". This seems to be a known issue if you are working with. 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:. For more info about this issue, pleasr see: 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. 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. Please let me know for further clarification, also if you've any ideas please let me know. Hai, I'm seeking a solutions for the below mentioned task. 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 A - All the cells having values, that means no cells are blank.

Now I want to link each value from Sheet R2 A0 to A 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. My xls have 3 drop down colums. Now does that causes the xls to store lesser than rows. On opening the xls having more than gives error "File is corrupt". Please help with the issue?? 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? I'm using it to create my inventory stock list. But how to further sorting my dynamic stock list to more details like below: Item - Dependent on Brand 3rd Option: 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. Any help would be greatly appreciated. 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: I want the picklist to be created in worsheet 3,column H , merging the data for all the 4 column ranges mentioned above.

Thanks much in advance. Hello, 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. 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. 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. Hi, 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? 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?

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. 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. 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? 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? 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. 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? 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.

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 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. 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.

Excel Create Pivot Table & Chart

On doing so I want it to represent a price to be used in another formula. Hello I would like to add a message when a value of a drop down list is selected is this possible.

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: 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. Hi, I have this problem that keeps me entering duplicate Document Code, How can I prevent this? Data Validation is not functioning I use. 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 ERMRPH which is one of the content of my data validation. ERMRPH, ERMRPH 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. Is it possible to make a list in a list like: The dropdown will be e. When you stand on e. 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. Hi I have 6 sheets in a workbook.. In the 1st sheet home page I need to create a Combo box list. When I select 1 from the combo box it should directly go to the respective sheet 1.

I'm developing a testing platform. I'm struggling to come up with a formula in Excel for the following tasks. 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. 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? You may find our Excel VLOOKUP tutorial useful.

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?

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. 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 http: When you enter the 1st letter "D" to search for "New Delhi". It auto populates all the entries which are starts with "D". I want to have a drop down list of say Towns which contains data for each town, say like population, number of bars, etc. 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.

Hi Svetlana, I am using Excel , but am not experienced. I have set up an Invoice Template on worksheet no. 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. 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.

First of all, thank you very much for this article. Can we give default name to cell instead of showing in via input message. I need to create a drop down list in below format if colom A1 "Student Name" then colom B1: I would like to know how to use a "clear" button for a dropdown menu. I have a dropdown menu that lists hockey players.

Create dependent drop down lists containing unique distinct values in excel | Get Digital Help - Microsoft Excel resource

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. 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? 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?

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? Hello, 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?

You need a special VBA macro that will spot when you click on a cell and open the drop-down list. Hi, 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. I have created four cloumn names called a,b,c and d. 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". 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.

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? For us to be able to help you better, please send a sample table with your data in Excel to support ablebits.

Please add the link to this article and your comment number. 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.

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? PLEASE HELP and Thank You. 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. In a list of names I have to select a name starting with "t", how can i do that without scrolling from mouse.

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.

Hi, I have created a yes no drop down cell but now I can't fill it with a colour. 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. And I am forced to right click and choose from drop-down. Would like a button when I click on the field. Thank you Alexander, Please speak any other option if it is not possible in standard excel drop down.

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. 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. 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. I have a combo boxes and I want to use it to be entry data into another worksheet serially. 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.

How will I do that? Hello, Svetlana Thank you, great blog if you could help with one more problem I would be grateful. No cells in table are blank i. Is there any way to make a Zip Code selection based on the City?

The table has 9 States and 38 Cities and approximately Zip Codes. Please look at the following article, it should help: 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?

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.. How to make multiple entries show up? Any help will be greatly appreciated. Again, thank you so much for the great blog. I am keeping attendance. 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? Excel's dropdown allows selecting only one item. So, in your case creating two drop down lists with the same items sounds quite reasonable.

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 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. 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:. 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. I want to make a list of name with drop down list but searchable. Can you help me on this? This has been super helpful in creating my drop down menu We are trying to make it quicker and more accurate for myself when entering payroll.

Using with drop down list sheet1, Sheet2, Sheet3 , is it possible to jump active sheet to another sheet as described in drop down list. 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? 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? I have static drop down messages in cells A1 to A5.

If I wanted to enter free form text in cell A6? Using comma dont work for list, i had to use ";" in between the values. This comprehensive set of time-saving tools will help you accomplish any task impeccably without errors or delays. These 8 tools will boost your inbox productivity and simplify your emailing routine.

Creating a drop down list in Excel: 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 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 , , and Select a cell or range for your drop-down list.

Use Excel Data Validation to create a drop-down list. 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. Optionally, select Ignore blanks if you want to allow your users to leave the cell empty.

Click OK and you are done! Creating an Excel drop-down list based on a named range This method of creating a drop down list in Excel takes a bit more time, but it may save even more time in the long run.

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

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.

Excel and older require the dropdown list to be on the same worksheet. For the drop-down list from another workbook to work, the workbook with the source list must be open. You have to enclose the workbook's name in apostrophes ' if it contains any spaces. 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.

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.

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.

September 24, at Hi Madhu, Well, this is not exactly the topic discussed in this article February 26, at 2: September 14, at 7: Gabriel, 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: March 26, at 7: September 26, at 8: September 29, at 4: Hi Alex, If you need a cascading drop-down where values in the second drop-down box depend on the selection in the first one , you'll know how to do it in my next article that will be published tomorrow: September 30, at 1: Alex, Just want to let you know that the "Cascading dependent Excel drop down list" tutorial is published: September 30, at 5: October 1, at 9: Alex, I got it, thank you.

October 2, at 4: October 8, at October 10, at Hello Claire, You need a special macro for this task. October 19, at 9: October 20, at 1: Hello Dipsundar, This is a very good question! The OFFSET function in more detail Thanks again for your great question! October 24, at October 27, at 4: Hello Marvin, Sorry, I don't quite understand why you use Offset in this formula.

October 29, at Hi Svetlana, First off thank you for all your advice and effort, its greatly appreciated. The rest of the paysheet is fine, it calculates the remuneration with no problems. January 7, at April 25, at 3: You don't need VBA to do this. November 6, at 4: November 11, at 9: Thanks for your tutorial November 12, at Hi Dion, As far as I understand you want to have a drop down list with search suggestions like in Google.

November 12, at 4: 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. If this is the case, please try the following suggestions: You can also avoid this message by one of the following workarounds: Save the file in the XLSX format.

Uncheck the "Check compatibility when saving this workbook" option. May 26, at Hi Svetlana, My workbook uses macros, which the XLSX format does not support. Saving in the XLSX format done the trick.

November 20, at 7: November 23, at 8: November 28, at 7: November 28, at 8: November 29, at 5: December 2, at 3: December 5, at 7: December 5, at 9: December 8, at December 8, at 9: December 9, at Hi, 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.

December 10, at 6: Hi, 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. December 10, at 4: December 15, at 2: Create a dynamic automatically updated Excel dropdown does not work. December 18, at 2: January 28, at 3: February 2, at Hi, 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.

Is there a way to dynamically update the selections of previous entries? February 8, at 8: Hi, i have 2 drop down list in excel. February 11, at 7: February 21, at 6: February 23, at March 1, at 2: Hi, I have a query as below: March 2, at 9: March 3, at 2: March 9, at 2: March 10, at 1: March 11, at 8: Hi all is it possible to make drop down list with content which will be depended on the input of another drop down list?

How to make a dependent (cascading) drop-down list in Excel

Thank you in advance. March 18, at 5: Hi, 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? March 18, at 6: Hello, 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? March 24, at 3: March 25, at 9: March 25, at 2: March 28, at 5: March 31, at 9: April 1, at April 11, at 8: April 25, at 8: April 26, at 6: May 7, at 6: May 13, at 7: May 19, at 5: May 21, at May 25, at Is it possible to create a drop down list in footer or headers?

Thank you for a very clear explanation about drop down May 28, at June 2, at 2: Hi, 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: Please help me with this.

June 10, at 4: Is there a way to have the drop down list items disappear as they are selected? June 11, at 1: Thank you so much for the article- it was very clear! June 23, at June 25, at 1: Susanta ku Pradhan says: June 28, at 5: July 1, at 5: July 8, at 7: I don't know how to do this procedure using combo box.

Can you help me in this. July 13, at 4: July 20, at 9: July 29, at 7: July 29, at Hello Yusuf, You may find our Excel VLOOKUP tutorial useful. August 7, at 3: August 9, at 3: August 14, at 6: August 15, at 5: 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.

If you could give me your mail id. I can send you the screenshot for better understanding. Please help me with the solution. August 17, at 8: Hay there, i gotta a problem August 18, at 4: Hi 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?

August 21, at 2: I think now i dont need to search for any other website for excel help. August 25, at 5: Does not make any sense - these tutorials should be easy for everyone. August 26, at 4: Hi, 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.

August 30, at 6: 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. August 31, at September 5, at 7: Hi how to insert a check box as an option in a drop down list. September 7, at Hello, First of all, thank you very much for this article. September 18, at I need to create a drop down list in below format 1.

I need to create a drop down list in below format In Colom A1 drop down 1. September 25, at 3: September 26, at September 28, at 2: October 7, at 4: November 8, at 9: January 9, at 4: November 24, at November 30, at 4: It is make some of schedule. Thank you too much. January 28, at Hello, You need a special VBA macro that will spot when you click on a cell and open the drop-down list.

December 14, at 4: Hi Vicky, Please specify the format you use to save your workbook. Is it xls, xlsx, csv? December 18, at 7: Hi, I have created four cloumn names called a,b,c and d. Could you please let me know how to create this in excel.

Hi Din, We don't know a formula for this task. Most likely you need a VBA macro. December 22, at 2: December 22, at 1: January 10, at 7: Hello, Farhan, Most likely you need a VBA for this task. Sorry we cannot help you with it. January 12, at 1: Hello, Pisey, For us to be able to help you better, please send a sample table with your data in Excel to support ablebits.

February 12, at March 5, at 9: I want to make a drop down list for example on D3 1 to 5 by entering just 5 on B2 cell. March 15, at 6: March 24, at 6: How to put shapes instead of txet or numbers in drop down list in Excel? March 27, at 7: March 30, at 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. In a list of names I have to select a name starting with "t", how can i do that without scrolling from mouse Regards,.

April 23, at 3: May 27, at 6: Sorry can tell Me About make Attendance For summery list excel thank. June 14, at July 6, at 9: July 31, at OK I must be missing something. August 30, at Hello Jagadish, Unfortunately, it is not possible in standard Excel drop-down lists. August 31, at 9: September 3, at 1: September 19, at September 26, at 9: September 27, at 9: 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.

September 28, at 8: 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. September 28, at 9: October 1, at 5: October 15, at 6: October 16, at 9: November 22, at Hi John, Please look at the following article, it should help: October 20, at 3: November 4, at 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..

November 4, at 8: November 9, at 7: November 10, at 8: Hello Roxy Excel's dropdown allows selecting only one item.

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. I've looked at the IF formulas but am unable to find the correct combination. Thank you for your advice. Hi Troy, Please look at the following article, it should help: Thanks thats exactly what I needed! November 29, at 6: December 13, at 7: Hi, i need help on drop down list issue below: December 13, at 6: 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". January 10, at 9: HI, Thanks for the detailing about DropDown. January 12, at 2: Hi Svetlana, I want to make a list of name with drop down list but searchable. January 12, at Hi There, This has been super helpful in creating my drop down menu I believe DEE see above is asking the same question.

Thank you for your help! January 31, at 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. February 13, at February 23, at 3: March 3, at 8: