The tutorial demonstrates 4 quick ways to add a dropdown in Excel. It also shows how to create a dropdown from another workbook, edit, copy and delete data validation lists. Show
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 make drop down list in ExcelOn the whole, there are 4 ways to create a drop down menu in Excel using the Data Validation feature. 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:
Create drop down list with comma-separated valuesThis is the fastest way to add a drop-down box in all versions of Excel 2010 through Excel 365. 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:
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. Add drop-down list from a named rangeThis 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.The fastest 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:
If the source list contains more than 8 items, your drop-down box will have a scroll bar like this: Note. If your named range has at least one blank cell, selecting the Ignore blank box allows any value to be entered in the validated cell. Make dropdown list from Excel tableInstead 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. Why may you want to use a table? First and foremost, because it lets you create an expandable dynamic drop-down list that updates automatically as you add or remove items from the table. To add a dynamic dropdown from an Excel table, follow these steps:
For this example, we make a dropdown from the column named Ingredients in Table1:
Insert drop down in Excel from a range of cellsTo insert a drop-down list from a range of cells, carry out these steps:
Create a dynamic (automatically updated) Excel dropdownIf 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 Excel validation list will get updated automatically! How this formula worksIn 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:
How to create drop-down list from another workbookYou can make a drop-down menu in Excel using a list from another workbook as the source. To do this, you will have to create 2 named ranges - one in the source book and another in the book where you wish to use your Excel Data Validation list. Note. For the drop-down list from another workbook to work, the workbook with the source list must be open. A static dropdown list from another 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. 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: 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.
Excel Data Validation does not workThe Data Validation option is greyed out or disabled? There are a few reasons why that might happen:
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. Display a message when a cell with the dropdown is clickedIf 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: Allow users to enter their own data in a combo boxBy 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.
How to copy drop down list in ExcelIn case you want a picklist to appear in multiple cells, you can simply copy it like any other cell content by dragging the fill handle through the adjacent cells or by using the copy / paste shortcuts. These methods copy all the contents of a cell including Data Validation and the current selection. So, they are best to be used when no item is selected in the dropdown yet. To copy a drop down list without the current selection, use the Paste Special feature to only copy the Data Validation rule. 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.
Modify a comma separated drop-down listIf you've created a comma separated drop down box, proceed with the following steps:
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. Change a drop down 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.
Update a drop-down list from a named rangeIf 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.
How to delete a drop-down listIf 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)
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 sheetIn 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.
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 cascading (dependent) drop down list with conditional Data Validation. Please stay tuned and thank you for reading! You may also be interested inWhere is the breaks button located?On most keyboards, the Break key is on the right side of the top row of keys. On a typical U.S. keyboard, it is the alternate function of the Pause key. If so, pressing Ctrl + Pause may be required to activate Break.
What is the purpose of the location placeholders in a predesigned cover page?The placeholders found in the predesigned cover pages are locations where you can enter specific information. Text that appears at the top of every page is called a header; text that appears at the bottom of every page is called a footer.
Which of the following Page Setup dialog box tabs includes an option to set margins?To set margins in Excel worksheets before printing, click the “Page Layout” tab in the Ribbon. Then click the “Page Setup” dialog box button in the lower-right corner of the “Page Setup” button group. Then click the “Margins” tab within the “Page Setup” dialog box.
Which of the following controls how text aligns between the left and right margins?Horizontal alignment determines how the left and right edges of a paragraph fit between the margins, while vertical alignment determines the placement of the text between the upper and lower margins.
|