Excel’s data validation options include creating a drop-down list that limits the data that users can enter into a cell to a predefined list of entries. This feature helps keep the data consistent and filters columns based on each entry in the list. For example, if you’re using a spreadsheet to track responses for an event, you can filter this column by Yes, No, and Unanswered.
These instructions apply to Excel 2019, 2016, 2013, 2010 and Excel for Microsoft 365.
When you add a drop-down list to a cell, an arrow appears next to it. Clicking the arrow opens the list and you can select one of the items to enter in the cell.
Enter data to create a drop-down list
In this example, the drop-down menu uses a list of items that are in another workbook. The benefits of using a list of entries in another workbook include centralizing the data in the list when multiple users need to access it and protecting the data from accidental or intentional modification.
If the list data is in a separate workbook, both must be open for the list to update.
The following steps will walk you through creating, using, and modifying the drop-down list similar to the image below.
- Open two blank Excel workbooks.
- Save a workbook with the name datasource.xlsx – This workbook contains the data of the drop-down list.
- Save the second notebook with the name dropdown.xlsx – This workbook contains the drop-down list.
- Leave both notebooks open after saving.
- Enter the data as shown below cells A1 at A4 out datasource.xlsx as can be seen in the picture above.
- Save the workbook and keep it open.
- Enter the data as shown below B1 cells out dropdown.xlsx workbook.
- Save the workbook and keep it open.
Data for cells A1 through A4 in data-source.xlsx
- A1 – Gingerbread
- A2—Lemon
- A3 – Oat Raisins
- A4 – Chocolate Chips
Data for cell B1 in dropdown.xlsx
Creating two named ranges
A named range lets you refer to a specific range of cells in an Excel workbook. Named ranges have many uses in Excel, including in formulas and creating charts. In any case, you should use a named range instead of a range of cell references that indicate the location of data on a worksheet.
If you’re using a drop-down list from another workbook, you must use two named ranges. One is for list items and the other is in the workbook where the dropdown is located. This named range refers to the one in the first workbook.
The first named range
- select cells A1 at A4 out datasource.xlsx to highlight them.
- Click on that name field is at the top Columns A.
- Type Cookies in which name field.
- Press the Enter on the keyboard.
-
cells A1 at A4 out datasource.xlsx now have the realm name of Cookies.
- To protect the workbook.
The second area named
The second named range does not use cell references from the dropdown.xlsx workbook. Rather, it is related to that Cookies on behalf of the area datasource.xlsx This is necessary because Excel does not accept cell references from another workbook for a given range. However, it only accepts the name of another realm.
The creation of the second named range is therefore not done via the name field but with the Define noun is on the website of formulas tab out Ribbon.
- Click on cells C1 in which dropdown.xlsx workbook.
- Click on Formulas > Set Name on the Ribbon to open the insert name dialog box.
- Click on that art nouveau to open the New name dialog box.
- Type Data in which noun Line.
- In which Refers to row type = « Data source.xlsx »!Cookies
- Click on OK to complete the named range and return to the Set Name dialog box.
- Click on Close to close the insert name dialog box.
-
To protect the workbook.
Using a data validation list
All data validation options in Excel, including drop-down lists, are set using the data validation dialog box. In addition to adding drop-down lists to a worksheet, data validation in Excel can also be used to control or restrict the type of data that users can enter into specific cells in a worksheet.
- Click on cells C1 out dropdown.xlsx to make it the active cell – that’s where the drop-down list is.
- Click on that Data tab out Ribbon above the worksheet.
- Click on that data validation on the icon Ribbon to open the dropdown menu. Choose data validation Possibility.
- Click on that settings tab in which Data validation dialog box.
- Click on that down arrow At the end of To allow to open the dropdown menu.
- Click on listing to select a data validation drop-down list cells C1 and activate the source in the dialog box.
- Because the data source for the drop-down list is in a different workbook, the second named range belongs in the source in the dialog box.
- Click on that source Line.
- Type =data in which source Line.
- Click on OK to complete the drop-down list and close the data validation dialog box.
- A small below Arrow should appear on the right side of the icon cells C1. Clicking the down arrow opens the drop-down list with the four cookie names entered cells A1 at A4 out datasource.xlsx workbook.
- Clicking on one of the names in the drop-down list prompts you to enter that name cells C1.
Changing the drop-down list
Because this example uses a namespace as the source for our list items instead of actual list names, we’ll change the cookie names in the namespace to cells A1 at A4 out datasource.xlsx immediately changes the names in the drop-down list.
If data is entered directly into the dialog box, changes to the list must return to the dialog box and change the source row.
Follow the steps below to change lemon at shortbread in the drop-down list by changing the data to cells A2 of the area named in the datasource.xlsx workbook.
- Click on cells A2 in which datasource.xlsx to make it the active cell.
- Type shortbread in cells A2 and press the Enter on the keyboard.
- Click on that down arrow for dropdown list in cells C1 out dropdown.xlsx workbook.
-
point 2 in the list should now read shortbread Instead of lemon.
Options to protect the drop-down list
Because the data in this example is on a different worksheet than the drop-down list, the available options for protecting the list data include