Search Contextures Sites

Contextures Excel news by email Email: Name (optional): We respect your email privacy	Excel Data Validation -- Introduction What is Data Validation? Video: Create a Drop Down List in a Cell Create a Drop Down List - Instructions Video: Create Drop Down From List on Different Sheet Use a Deliimited List Allow Entries Not in Drop Down List Download the Sample File For the Excel 2003 video and instructions, click here. What is Data Validation? In Excel, the data validation feature helps you control what can be entered in your worksheet. For example, you can: --create a drop down list of items in a cell --restrict entries, such as a date range or whole numbers only --create custom rules for what can be entered In this tutorial, you'll see how to create a drop down list of choices in a cell. Video: Create a Drop Down List in a Cell To create a drop down list in Excel 2010, you can name a list of items, based on a named Excel table. Then, use that list as the source for the Data Validation drop down list. If you don't want to create a named table, you can follow the instructions in the named range section below. Watch the steps in this short video, and the written instructions are below the video Create a Drop Down List - Instructions With Data Validation, you can create a dropdown list of options in a cell. Although you can type the list directly into the Data Validation dialog box, it's best to use a table on the worksheet, as described below. Note: Data validation is not foolproof. It can be circumvented by pasting data into the cell, or by choosing Clear > Clear All, on the Ribbon's Home tab. Create a Table The easiest way to create and maintain the list of options, is to type them on a worksheet. You can do this on the sheet that will contain the drop down lists, or on a different sheet. In this example, the list will be stored on a sheet named Lists. Type a heading for the list -- Employees in this example Immediately below the heading cell, in single column, type the entries you want to see in the drop down list. Do not leave blank cells between the entries. Select a cell in the list, and on the Ribbon's Insert tab, click Table Add a check mark in "My table has Headers" and click OK The table is now a Named Excel Table. Name the List Range Next, you will create a named range that does not include the heading cell in the table. This named range will be dynamic -- it will adjust automatically if items are added to or deleted from the list. Click at the top of the heading cell, to select all the cells in the list (without the heading). Click in the Name box, to the left of the formula bar Type a one-word name for the list, e.g. EmpNames. Press the Enter key, to complete the naming process. (After you press Enter, the name will disappear, and the Table name will appear in the name box.) Apply the Data Validation Now that you have created a named range, you can use that to create a drop down list in one or more cells Select the cells in which you want the drop down list On the Ribbon's Data tab, click Data Validation. From the Allow drop-down list, choose List Click in the Source box, and type an equal sign, and the list name, for example: =EmpNames OR, press the F3 key, to see a list of names, click on a name, and click OK Click OK to close the Data Validation dialog box. Video: Create Drop Down From List on Different Sheet If you prefer not to create a named Excel table, you can create a named range, and use that as the source for a drop down list. The drop down lists can be on the same sheet as the source list, or on a different sheet. Please watch this video to see the steps. Use a Delimited List Instead of referring to a list of items on the worksheet, you can type the list in the Source box, separated by commas. For example: Yes,No,Maybe Note: This method of Data Validation is case sensitive -- if a user types YES, an error alert will be displayed. Allow Entries Not in Drop Down List To allow users to type items that are not in the list., turn off the Error Alert. Download the Sample File You can download the sample 2010 file here: Drop Down List Excel 2010  More Data Validation Tutorials Data Validation Basics Excel 2003 Create Dependent Drop Down Lists Dependent Dropdowns from a Sorted List   Dependent Lists With INDEX   Hide Previously Used Items in a Dropdown List Display Data Validation Messages to the User Display Input Messages in a Text Box  Use a Data Validation List from Another Workbook Data Validation Criteria Examples Data Validation Custom Criteria Examples Data Validation Tips Data Validation Documentation Data Validation With Combo Box      Data Validation Combo Box - Named Ranges Data Validation Combo Box -- Click Data Validation - Add New Items

 

Home     Excel Tips     Excel Files     The Excel Store     Blog     Contact
Privacy Policy		Contextures Inc., Copyright ©2013 All rights reserved.	Debra Dalgleish