1.1 Creating a Drop-down List

Overview

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

Learning Outcomes

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.