How to Create a Drop Down List in Excel

Опубликовал Admin
1-06-2019, 13:00
2 918
0
Updated: March 29, 2019 Having a drop-down list in your Microsoft Excel spreadsheet can often improve the efficiency of your data entry, while also limiting data entry to a specific set of items or data made available in the drop-down list. Follow the steps below to learn how you can create a drop-down list in Excel 2013 and all earlier versions.

Excel 2013

  1. Open the Excel document in which you want to create a drop-down list.
  2. Go to a blank or new sheet within the Excel document.
  3. Type a list of items you want displayed in your drop-down list. Each item should reside in its own cell, and be displayed in the same column. For example, if you're creating a drop-down menu that contains a list of sports, type "baseball" in cell A1, "basketball" in cell A2, "football" in cell A3, and so on.
  4. Select all of the cells that contain the drop-down items you entered.
  5. Click on the Insert Menu. Select Name, then select Define.
  6. Type a name for your drop-down items in the "Name" field, then click on "OK." The name you choose will be for reference only, and will not be displayed in your Excel spreadsheet.
  7. Click directly on the cell in your spreadsheet in which you want the drop-down list to appear.
  8. Click on the "Data" button, and select "Data Validation." The Data Validation menu will then appear on your screen.
  9. Click on the "Settings" tab, then select "List" from the "Allow" drop-down menu.
  10. Go to the "Source" box, and type an equals sign, followed by the name you chose for your drop-down list. For example, if you named your drop-down items "Sports," then type "=Sports."
  11. Place a checkmark next to "In-cell dropdown."
  12. Place a checkmark next to "Ignore blank" if you want to provide users with the option to select none of the items in your drop-down list.
  13. Click on the tab labeled "Error Alert."
  14. Place a checkmark next to "Show error alert after invalid data is entered." This will prevent users from entering data that is not part of your drop-down list. If you want users to have the ability to enter their own data into the drop-down list, then omit the checkmark from this field.
  15. Click on "OK." Your drop-down list will now be made available in your spreadsheet.

Excel 2010, 2007, and 2003

  1. Open the Excel file in which you want a drop-down list created.
  2. Click on a blank or new sheet within your Excel file.
  3. Enter a list of items you want displayed in your drop-down list. Each item should be entered into its own separate cell in just one column. For example, if you're creating a drop-down menu that contains a list of fruits, type "apple" in cell A1, "banana" in cell A2, "blueberry" in cell A3, and so on.
  4. Select the cell range that contains all the drop-down items you entered.
  5. Click within the "Name" box in your Excel toolbar, which is located to the left of the formula bar.
  6. Type a name that describes the drop-down items into the "Name" box, then press "Enter." The name you choose will only be for your reference, and will not show up on your Excel spreadsheet.
  7. Navigate to and click on the cell in your Excel document in which you want the drop-down list to display.
  8. Click on the "Data" tab in your spreadsheet, and select "Data Validation" from the "Data Tools" group. The Data Validation dialog box will display on your screen.
  9. Click on the "Settings" tab within the Data Validation dialog box.
  10. Select "List" from the "Allow" drop-down box.
  11. Go to the "Source" field, and type an equals sign, followed by the name of your drop-down list. For example, if your drop-down list is called "Fruits," then type "=Fruits."
  12. Place a checkmark next to the field labeled "In-cell dropdown."
  13. Place a checkmark next to the field labeled "Ignore blank" if you want users to have the option of selecting zero items from the drop-down list when they use the spreadsheet.
  14. Click on the "Error Alert" tab.
  15. Place a checkmark next to the field labeled "Show error alert after invalid data is entered." This option prevents users from entering unique data that does not appear within the drop-down list. If it's acceptable for users to enter unique data into the drop-down list, then leave this field blank.
  16. Select "OK." The drop-down list will now be displayed within your spreadsheet.

Tips

  • After you finish creating your drop-down list, open the drop-down list to make sure all the items you entered display properly. In some cases, you may need to widen the cell in order to display all your items fully.
  • When typing the list of items for your drop-down list, type them in the order in which you want them to appear in the drop-down menu. For example, type entries in alphabetical order to make it easier for users to find certain items or values.

Warnings

  • You will not be able to access the "Data Validation" menu if your worksheet is protected or shared, or linked to a "SharePoint" website. If any of these cases apply to your spreadsheet, remove the protection, unshare the spreadsheet, or unlink your spreadsheet from the SharePoint website, then try to access Data Validation again.
Теги:
Information
Users of Guests are not allowed to comment this publication.