- Data validation rules help limit the data your users can enter into a cell, but they can be frustrating if the rules aren't clear. One of the clearest ways to require users to enter one of a set of options is to define a list of acceptable values for a cell. This is the start of a worksheet meant to capture room reservation data, and it's based on the Landon Hotel's loyalty program. I will start by creating a validation list for RoomType and then do a similar but slightly different operation for GuestLevel.
So I'll click in cell C2, which is where I want to create my room type list. And then I'll go to the Data tab on the ribbon and click Data Validation. That displays the Data Validation dialog box. I'll click the Allow control's down arrow. And from the selection of rules, I'll click List. Doing so displays the controls that I need to use. I will clear the Ignore blank checkbox because I do want to require data entry in this cell.
And I will leave the In-cell dropdown checkbox selected. That way when the user clicks the cell, they will know that they can click a down arrow and select from the available values. Now I need to identify the source of my data, so I will click the Source box's collapse dialog button and select cells F1 through F4, and I'll click the expand dialog button. Everything looks good, so I can click OK. And you can see that a down arrow has appeared to the right of cell C2.
And if I click it, I get a list of acceptable values. And in this case, I'll just pick the first one, Cambridge. But now let's say that the list that I want to use exists on another worksheet. This can happen if you have a worksheet that contains multiple parts. And if it's not easy to copy that data back to the active worksheet, you can still create your list. So I'll click the GuestLevel cell, that's cell D2. And click Data Validation.
And once again I'll click the Allow button and click List. To select a range on another sheet, I will click the collapse dialog button, click the sheet tab of the sheet that contains the data, in this case, Sheet2, and select the data that I want to use. We have five levels, they are Non-participant, Red, Blue, Green, and Purple. With that selection, I can go back to the Data Validation dialog box, click the expand dialog button, and click OK.
That takes me back to sheet number one, and it has applied the rule to cell D2. I'll click the down arrow, and this is one of our best customers, so I'll click Purple. Data validation lists are powerful tools that can help prevent spelling errors and limit your users' data entry to a list of approved values. Be sure to listen to your users, though, to find out if they need to add or edit items on the list that reflect their needs and the realities of your business.
Author
Updated
2/18/2016Released
8/7/2015- Customizing the Ribbon
- Formatting worksheets, cells, and cell data
- Sorting and filtering data
- Working with formulas
- Detecting formula errors
- Creating charts
- Importing data
- Inserting objects and graphics
- Using PivotTables
- Recording macros
- Sharing workbooks
Skill Level Beginner
Duration
Views
Q: This course was updated on 02/18/2016. What changed?
A: We updated one tutorial, "Managing objects using the Selection pane." The new Selection pane, released in a January 2016 Office update, allows Excel for Mac users to more easily rearrange worksheet and slideshow objects.
Related Courses
-
Introduction
-
Welcome59s
-
-
1. Getting Started with Excel
-
Getting help in Excel3m 11s
-
2. Managing Workbooks
-
Setting workbook properties2m 45s
-
3. Working with Worksheets, Cells, and Cell Data
-
Creating named ranges5m 58s
-
Creating an Excel table5m 9s
-
4. Sorting, Filtering, and Managing Worksheets
-
Sorting worksheet data3m 6s
-
Creating a custom sort order4m 49s
-
Filtering worksheet data3m 55s
-
Managing worksheets5m 4s
-
-
5. Summarizing Data Using Formulas and Functions
-
Adding a formula to a cell3m 56s
-
6. Analyzing Data and Formulas
-
Rounding cell values4m 14s
-
Managing scenarios7m 1s
-
7. Formatting Worksheet Elements
-
Managing text alignment4m 46s
-
Copying cell formats3m 29s
-
Managing cell styles4m 10s
-
Managing Office themes5m 45s
-
8. Working with Charts
-
Creating pie charts2m 25s
-
Creating line charts3m 11s
-
Creating XY (scatter) charts2m 38s
-
Creating stock charts2m 42s
-
Adding trendlines to charts3m 31s
-
Creating sparkline charts4m 17s
-
9. Working with External Data
-
Using hyperlinks4m 18s
-
10. Working with Objects
-
Adding and adjusting images4m 58s
-
Manipulating text boxes3m 20s
-
Creating SmartArt graphics4m 19s
-
Creating WordArt2m 55s
-
11. Exploring PivotTables
-
Applying a PivotTable style2m 26s
-
12. Reviewing and Sharing Your Spreadsheets
-
Checking spelling2m 55s
-
Managing workbook comments3m 29s
-
Exporting to other formats2m 48s
-
Protecting a workbook3m 23s
-
-
13. Automating Workbooks Using Macros
-
Running an existing macro4m 31s
-
Recording a macro2m 46s
-
Adding comments to a macro2m 23s
-
-
Conclusion
-
Next steps1m 8s
-
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Using lists to limit data entered into a cell