Check out this post to find out how to make it work. I love Tables, but they don’t work as well with data validation lists as they should. TOP TIP: If you can’t remember the name of the named range, press F3 and select from the Paste Name dialog box. The list of values can be stored in a named range, provided it is a continuous range of rows or columns. If using values in worksheet cells as the source, the list of values can be arranged in rows or columns (but not both at the same time) List input as a named range TOP TIP: If we need to use a comma in the list item text, press ALT+0130 to enter the comma, instead of a regular comma character. The list doesn’t have to be on a worksheet the values can be hardcoded into the source field. The screenshot below shows how to include the options Yes, No and Maybe without using cells each list item is separated by a commaĪ drop-down list created in this way would look like this: There is lots of flexibility in this type of drop-down list. Typing an invalid entry in the cell will result in an error message. We can now only select a value from the list. To use the drop-down, click on the cell containing the data validation list. The data validation dialog box opens. On the Settings tab, select list from the allow box, and enter the range of cells containing the list in the source box.Ĭlick OK to close the data validation dialog box. Then select the cell in which the drop-down should exist.Ĭlick Data > Data Validation (drop down) > Data Validation… If you’ve found this tutorial helpful, like us and subscribe to receive more videos from Eas圜lick Academy.The most common form of drop-down list is a data validation list. Let’s create one of these.Įnter the values to be used in the drop-down list into a range of cells these are known as the ‘list items’. When both options are in, click on OK and the job is done! The simple drop-down list has been created! Then we’ll click on the ‘Data’ tab, go to ‘Data Tools’ and find ‘Data Validation’.Īgain, we’ll select the ‘List’ option, but now we’ll just type into the box two words – ‘Yes’ and ‘No’ – separated with a comma. We’ll have a look the second question, where, in the part ‘Your answer’, there will be just ‘Yes’ or ‘No’.Īs you already know, the first step will be selecting the cell in which we want to create the drop-down list. Well, let’s move on and see how to create a short drop-down menu made of only two options. How to Create a Short Drop-Down List in Excel If anyone tries to type in a different option, Excel will not allow it. Press ‘Enter’ – and you’ve defined the contents of your drop-down list!Ĭlick on OK and you’re done! The column with ‘Your answer’ now contains a drop-down list from which you can choose your favourite month.Īs soon as you create the list, you can pick only from the options you’ve defined. Here we’ll click through to the spreadsheet ‘Months’ and select all months we want to include in the list. Click on the icon on the right of the ‘Source’ box. Now you need to tell Excel where to find the data for the drop-down list. Then click on the tab ‘Data’, go to ‘Data Tools’ and select the option ‘Data Validation’.įind ‘Settings’ in the pop-up window and select ‘List’ from the menu. Now we’ll click back on the spreadsheet ‘Questionnaire’ and create our first drop-down list.Ĭlick into the cell in which you want to create the drop-down menu. I myself added a new spreadsheet, named it ‘Months’, and made a list of months that’ll be used for our drop-down menu. So, if you want to make a drop-down menu with more options, it might be a good idea to create the list somewhere else, for example in a different spreadsheet. The answer to the first question will be a specific month of the year. How to Create a Longer Drop-Down List in Excel Those will be possible answers to our second question.īut let’s start from the beginning now. I’ll show you a way how to create a short drop-down list with only two items – ‘Yes’ and ‘No’. Then we’ll try something a little bit different. They’ll become our options for the answer to the first question. What we can use for drop-down items here are the months of the year. We’ll use this short questionnaire to see how simple it is to create a drop-down menu and you’ll be able to use these steps to create a list with any data you need.įirst, we’ll have a look at how to create a longer drop-down list with more options. 2 How to Create a Short Drop-Down List in Excel
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |