Using Data Validation in Excel

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: How to Customize the Quick Access Toolbar in Excel

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:01 What Is Data Validation?
  • 1:00 Whole Numbers Within Limits
  • 2:50 Text of a Specified Length
  • 4:12 Types of Alert Messages
  • 5:34 Lesson Summary
Add to Add to Add to

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Login or Sign up

Timeline
Autoplay
Autoplay

Recommended Lessons and Courses for You

Lesson Transcript
Instructor: Karen Sorensen

Karen has a Bachelors in Communications. She has 25 years of experience in Information Systems, Adult Learning and Virtual Training.

Have you ever wanted to restrict what type of data is entered into an Excel spreadsheet as well as how it's entered? Excel's data validation feature can help you do just that. This lesson will explain data validation and demonstrate how to set limitations on data entered on Excel worksheets.

What Is Data Validation?

So, the question is 'What is data validation?' The data validation feature in Excel is a way to define how the data can or should be entered. This feature is helpful when you will be sharing the workbook and multiple people will be entering data. With data validation, you can keep the information consistent, uniform and valid. Data validation in Excel allows for many different types of restrictions, such as:

  • Whole numbers within limits
  • Text of a specific length
  • To restrict data entry to values in a drop-down list
  • Dates within a time frame
  • Calculate what is allowed based on the content of another cell

This lesson will guide you through the steps to using data validation using the two most common ways, which are whole numbers within limits and text of a specified length.

Whole Numbers within Limits

Let's begin with whole numbers within limits. One example of data validation is to restrict the entry to a whole number and assign a limit to the numbers that can be entered. For example, you could create an evaluation asking people to give their opinion on the success of a seminar or a training event. You could create the form in Excel and email it to the participants following the session. Imagine this evaluation is based on a scale of 1 to 5 and you would like to restrict the entry to only those numbers. So let's take a look.

I have an example evaluation form with a column for questions, one for the ratings and one for comments (please see the video at 01:40). We want to apply the validation rule to the column for the ratings:

  1. So we'll start by selecting the cells for which we want to apply the validation rule (or you can select one cell).
  2. Then, go to the Data menu in the ribbon.
  3. Look in the Data Tools grouping of commands.
  4. Then, click on Data Validation.
  5. Then, click on the Settings tab and look in the Validation Criteria section.
  6. In the Allow options box, select Whole Number from the pick-list.
  7. Leave Between in the Data option box.
  8. And then enter the number 1 in the Minimum box and 5 in the Maximum box.
  9. And then just click OK.

Our data validation rule has now been applied to these cells. Only numbers 1-5 will be accepted. And notice in the lesson video, if you try to enter a 6, you receive an error message that the value you entered is not valid (please see the video at 02:44). Pretty slick!

Text of a Specified Length

Let's move on to text of a specified length. This rule is fairly straightforward - you limit how many characters can be entered into the cell. For instance, let's go back to our evaluation form. One of the columns is for comments. Imagine you will be using this spreadsheet to upload the data to a system that keeps records of ratings and comments for instructors. However, this database has a limit on how many characters can be used for comments. So let's take a look. The first five steps are the same steps we followed for whole numbers:

  1. You want to select the cells that you want to apply the validation rule to.
  2. Then, go to the Data menu in the ribbon.
  3. Look in the Data Tools grouping of commands.
  4. Click on data validation.
  5. Make sure the Settings tab is selected and look in the Validation Criteria section.
  6. In the Allow options box, select Text Length from the pick-list.
  7. Leave Between in the Data option box.
  8. And then enter the number 5 in the Minimum box and 50 in the Maximum box.
  9. And then click OK.

To unlock this lesson you must be a Study.com Member.
Create your account

Register for a free trial

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Free 5-day trial

Earning College Credit

Did you know… We have over 160 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it free for 5 days!
Create an account
Support