- Download 0
- File Size 4.00 KB
- File Count 1
- Create Date October 17, 2021
- Last Updated October 17, 2021
1.1 Creating a Drop-down List
Data Validation in Excel lets you control the data that can be entered in a cell. You can restrict the user to enter only a specified range of numbers or text or date.
Data validation is a feature in Excel used to control what a user can enter into a cell. If data isn’t accurate from the start, your results definitely won’t be accurate either. That’s why it’s necessary to verify and validate data before it is used.
Using validation rules to cleanse data ensures the integrity of data before use which goes a long way to ensure the legitimacy of your conclusions.
Validating the accuracy, clarity, and details of data is necessary to mitigate any project defects. Without validating data, you run the risk of basing decisions on data with imperfections that are not accurately representative of the situation at hand.
The focus of this lesson is to ensure that you can use data validation to restrict the type of data or values you enter in a cell during data entry.h There are three situations where you would want to use Data Validation in Excel:
- When you want to restrict data entry to certain numbers/text/dates. Data that does not meet the validation criteria is not allowed.
- When you want to inform user whenever out-of-range data is entered. However, all kinds of data entry is allowed.
- When you guide the user on what data to enter. All kinds of data entries are allowed.
A drop-down list is an excellent way to give the user an option to select from a pre-defined list.
Fig 1: A video on overview of data validation. Source: Microsoft Support
By the time you complete this lesson, you should be able to:
- to create a data validation rule that picks data from the drop-down list
- create a drop-down list by using Data from Cells
- create a drop-down list by entering Data Manually
Using Data Cells to Create a Drop-down List
The video in figure 2 demonstrate how you can create a drop-down list from a range of cells.
Sheet 1 and Sheet 2 are being renamed as Validation Table and Faculty List respectively in figure 2. It's a common practice to type repeatedly some entries (male, regular, religion) when you do entries for gender, status, and faculty.
This tutorial will walk you through anexcellent option to select from a pre-defined lists instead of having to type such entries for each record.
1. Type the entries (list of faculties) you want to appear in your drop-down list. Ideally, you’ll have your list items in an existing Excel table.
2. Select the cell (faculty) in the table where you want the drop-down list.
3. Go to the Data tab ribbon.
4. Click Data Validation.
5. Click List in the Allow box of the Settings tab.
6. Click in the Source box.
7. Select your list range. We have our list in range C1:C4.
8. Check the Ignore blank box if you think it’s OK for users to leave the cell empty.
9. Check the In-cell drop-down box.
10. Click OK.
Fig 2: Using cell range to create dropdown list
Using Data Manually to Create a Drop-down List