Locked lesson.
About this lesson
In this lesson, we look at how to use data validation to create a dropdown list in Excel to limit input to pre-set options.
Quick reference
Data Validation: Create a Dropdown List
In this example, we use data validation to create a dropdown list to accommodate limited options and we limit the number of characters in a field and move to limit a field to text only.
When to use
To prevent garbage input or limit input to only certain options, use data validation.
Instructions
Data validation menu
- Data tab, tools grouping, data validation
Username
- Select text length to limit the number of characters, this value must start with an alphabetical character to be seen as text. Numbers may follow the initial alphabetical character
First name
- Use the formula ISTEXT to check if the value in the cell is text or a number. This functions the same as the username field, but does not limit the number of characters
State
- Use the list provided to add validation as a list. This will result in a dropdown created with the options on the list provided.
Clear validation
- Select the cells, go to the normal validation screen and on the bottom left-hand corner, click clear validation
Validation circles
- Data tab, tools grouping, data validation, show invalid data / clear validation circles
- If data exist in the sheet before the validation is applied, then the data stays unaffected, or, if you selected to add the data anyway, the invalid data will be in the sheet
- If you circle invalid data, the circles will indicate the issues, clear will remove circles
- 00:04 In this lesson, we're going to do a few text field validations and
- 00:08 create a drop-down list.
- 00:10 Let's look at a field for username first.
- 00:13 Let's say username is a name that a user chooses for
- 00:17 themself, and it has to be unique.
- 00:19 On the Data tab > Data Validation, we can choose something like text length,
- 00:25 and we can say text length should be less than something random, like 7 characters.
- 00:32 Now, this Text Length option in Data Validation doesn't limit
- 00:36 the type of characters you enter to text-only, or prohibit numbers.
- 00:40 This Text Length option allows numbers and text, but the total character length,
- 00:44 all the characters in that field, should be less than seven.
- 00:48 So if I type 123 in here, it accepts numbers.
- 00:52 If I type in Pat, it accepts letters.
- 00:56 But if I type in Pat1234, seven characters,
- 01:00 Excel will not accept the entry.
- 01:02 It needs to be less than seven characters, so if we make it Pat123, that's good.
- 01:08 If I do want to limit whatever is in a field to rject a numeric entry,
- 01:14 what I can do is add a formula.
- 01:16 So back up here at my First Name field, let's select that and do Data Validation.
- 01:23 We'll select Custom, and to test if the entry is text-only,
- 01:30 you add =ISTEXT(, click on the cell,
- 01:34 close parentheses, and hit Enter.
- 01:38 So if I type 123 for my first name, it will reject the entry.
- 01:43 But I can add as many text characters as I want,
- 01:45 because I have no limitation on that.
- 01:48 Even if I type in Patricia Joy 3rd, and
- 01:52 we use a numeric character in there.
- 01:56 Well, we can do that because the entry started out as text, and
- 02:00 Excel sees this as text.
- 02:01 So that type of entry is acceptable.
- 02:04 Most likely, you'd want to apply the same custom ISTEXT validation
- 02:09 to your Last Name field that you do for the First Name.
- 02:13 But State is a nice opportunity for a drop-down list.
- 02:17 Let's say I have three shops in three different states in the list below.
- 02:21 We can force the user to choose one of those states,
- 02:24 instead of allowing them to type in anything.
- 02:26 So click on the State field, Data Validation, and in Settings, choose List.
- 02:32 And your list source will be the three cells we have listed at the bottom.
- 02:37 Just note the list source, Excel will lock it with dollar signs.
- 02:41 Okay, and now you have a drop-down in your validation cell, and
- 02:45 you can choose whatever state you need from that list.
- 02:48 But let's say at some point, you want to add a shop.
- 02:51 You can click on the Home tab, insert a row.
- 02:54 And let's say with want to add Utah right in the middle of the list.
- 02:59 Well, when we go back to the pull-down, Utah is in there.
- 03:03 But let's say I want to add Wisconsin, and we add it to the bottom of the list.
- 03:08 Well, it won't be included in the Data Validation pull-down.
- 03:12 I need to extend my list by extending the data validation to row 23.
- 03:18 So let's go back to Data Validation,
- 03:21 change the source, down to row 23, hit OK, and
- 03:26 now the pull-down works with Wisconsin included.
- 03:31 All right, let's say something changes, and
- 03:34 you want to get rid of the data validation on a field.
- 03:37 You might run into this if you inherit someone else's spreadsheet
- 03:40 with existing data validation you don't want.
- 03:43 Click on the cell, go to Data Validation, and choose Clear All.
- 03:48 That gets rid of the validation on the selected cell.
- 03:51 Just a word of warning.
- 03:54 If there's invalid data in a cell, and you add data validation after,
- 03:59 like a text length less than seven characters,
- 04:02 it will not reject data that's already in there.
- 04:06 What you can do is go to the Data Validation drop-down,
- 04:10 click on Circle Invalid Data, and it will circle the invalid data you have in there.
- 04:16 Invalid data on this sheet is your four-digit client code and your username.
- 04:23 Let's see if we can fix this by typing in a new username, and
- 04:28 now it doesn't circle that anymore.
- 04:31 And if you don't want those circles, go back up to Data Validation and
- 04:35 click Clear Validation Circles.
- 04:37 All right, happy validating, everyone.
Lesson notes are only available for subscribers.