Create a drop-down list in Excel

Excel’s data validation options include creating a drop-down list that restricts the data that can be entered in a particular cell to a predefined list of items. When a drop-down list is added to a cell, an arrow appears next to it. Clicking the arrow opens the list and you can select one of the list items to enter the cell.

The data used in a list can be on the same sheet as the list, on a different sheet in the same workbook, or in a different workbook.

Content
  1. Data Entry Tutorial
  2. Create two named ranges
  3. Using a list to validate data
  4. Change the drop-down list
  5. Dropdown Security Options

Data Entry Tutorial

In this tutorial, we will create a drop-down list of items in another workbook. The benefits of using a list of records that are in another workbook include centralizing the list data if it is shared by multiple users and protecting the data from accidental or intentional modification.

When storing list data in a separate workbook, both workbooks must be open for the list to work.

By following the instructions below, you will learn how to create, use, and modify a drop-down list similar to the one in the image above. As mentioned, the dropdown data for this tutorial is in a different workbook than the dropdown.

Open two Excel workbooks

  1. Start by opening two blank Excel workbooks.

  2. Save one book with a name data-source.xlsx – this workbook contains the data for the drop-down list.

  3. Save the second workbook with a name dropdown.xlsx – this book will contain a drop-down list.

  4. Leave both workbooks open after saving.

Data Entry Tutorial

  1. Fill in the details as shown below cells A1 A4 books data-source.xlsx as shown in the image above.

  2. Save the workbook and leave it open.

  3. Fill in the details as shown below cell B1 books dropdown.xlsx .

  4. Save the workbook and leave it open.

Data for cells A1-A4 in data-source.xlsx:

  • A1 gingerbread
  • A2 Lemon
  • A3 oatmeal raisin
  • A4 chocolate chip

Data for cell B1 in dropdown.xlsx:

  • B1 Cookie type:

Create two named ranges

A named range allows you to refer to a specific range of cells in an Excel workbook. Named ranges have many uses in Excel, including their use in formulas and when creating charts. In all cases, a named range is used instead of a range of cell references that indicate the location of the data on the worksheet.

When used in a drop-down list in another workbook, two named ranges must be used. One is created for the list items in the workbook that contains the data. The second is created in the workbook where the drop-down list is located – this named range refers to the named range in the first workbook.

former range

  1. Select cells A1 A4 books data-source.xlsx to mark them.

  2. Press field name” located upstairs column A .

  3. Enter Cookies in the field Name .

  4. Press key Enter on keyboard.

  5. cells by A1 before A4 books data-source.xlsx now have a range name Cookies .
  6. Save book.

Second mentioned range

Second named range does not use cell references to workbook dropdown.xlsx . Instead, as mentioned, it will contain a reference to the name of the range Cookies in the book data-source.xlsx .

This is necessary because Excel will not accept cell references from another workbook for the named range. However, it will except the other range name.

Therefore, creating the second named range is not finished with name and using the parameter To define Name located in the formulas . tab on the adhesive tape .

  1. Press cell C1 in the book dropdown.xlsx .

  2. Click Formulas > Define Name on the adhesive tape to open the dialog Define name .

  3. Press the button To create to open the dialog New name .

  4. In line Name enter Facts .

  5. In line Refers to a type = ‘datasource.xlsx’! Cookies

  6. Click Okay to complete the named range and return to the Define Name dialog box.

  7. Click close to to close the dialog Define name .

  8. Save book.

Using a list to validate data

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 can be entered into specific cells on a worksheet.

  1. Press cell C1 workbook dropdown.xlsx to make it the active cell – this dropdown contains:

  2. Press Facts tab on the menu ribbons above the worksheet.

  3. Click the icon Data Validation on the adhesive tape to open the drop-down menu.

  4. Press the button Data Validation in the menu to open the dialog box Data Validation .

  5. Click on the tab Settings in the dialog box Facts Exam .

  6. Press down arrow at the end of the line To allow to open the drop-down menu.

  7. Click List to select the drop-down list to validate the data in cell C1 and activate the line A source . in the dialog box.

Since the data source for the drop-down list is in a different workbook, the second named range created earlier is entered in the string A source in the dialog box.

  1. Click on a line A source .

  2. Enter = data in line A source .

  3. Click Okay to populate the drop-down list, and close to dialog box Data Validation .

  4. little icon down with an arrow should now be to the right of cells C1 Clicking the down arrow will open a drop-down list with the four cookie names entered cells A1 A4 by data-source.xlsx workbook.

  5. By clicking on one of the names in the drop-down list, you must enter this name cell C1 .

Change the drop-down list

Since we were using a named range as the source for our list items and not the actual list names, by setting the cookie names in the named range to cells A1 on the A4 of the book data-source.xlsx immediately changes the names in the drop-down list.

If data is entered directly into the dialog box, you must return to the dialog box and edit the source row before making any changes to the list.

In this step we will change: Lemon on the sand cookies in the drop-down list by changing the data to cell A2 named range in workbook data-source.xlsx .

  1. Press cell A2 in the book data-source.xlsx to make it the active cell.

  2. Enter shortbread v cell A2 and press the key Enter on keyboard.

  3. Click down arrow for the drop-down list in cell C1 v drop-down.xlsx workbook.

  4. Element 2 the list should now read shortbread instead of Lemon .

Dropdown Security Options

Because our data is on a sheet other than a drop-down list, the following options are available to protect the list data:

  • Protect a blade that causes blockage cells A1 on the A4 on the sheet 2 .

  • Require a password to change a workbook.

  • Open the book as only to read .

Leave a Reply

Your email address will not be published.