Your browser does not support video. Install Microsoft Silverlight, Adobe Flash Player, or Internet Explorer 9.

When you create worksheets that will be used by others, it’s important to make sure they can only enter valid data. Use Excel’s data validation features to make rules to restrict the type of data or values that others can enter into a cell.

  1. Select the cell(s) you want to create a rule for.

  2. Select Data >Data Validation.
    Data validation

  3. On the Settings tab, under Allow, select an option:

    • Whole Number - to restrict the column to accept only whole numbers.

    • Decimal - to accept decimal numbers.

    • List - to pick data from the drop-down list.

    • Date - to restrict the cell to accept only date.

    • Time - to restrict the cell to accept only time.

    • Text Length - to restrict the length of the text.

    • Custom – for custom formula.

  4. Under Data, select a condition:

    • between

    • not between

    • equal to

    • not equal to

    • greater than

    • less than

    • greater than or equal to

    • less than or equal to

  5. On the Settings tab, under Allow, select an option:

  6. Set the other required values, based on what you chose for Allow and Data.
    For example, if you select between, then select the Minimum: and Maximum: values for the cell(s).

  7. Select the Ignore blank checkbox if you want to ignore blank spaces.

  8. If you want to add a Title and message for your rule, select the Input Message tab, and then type a title and input message.

  9. Select the Show input message when cell is selected checkbox to display the message when the user selects or hovers over the selected cell(s).

  10. Select OK.

    Now, if the user tries to enter a value that is not valid, a pop-up appears with the message, “This value doesn’t match the data validation restrictions for this cell.”

Want more?

Discover more courses like this at LinkedIn Learning

Apply data validation to cells

Let's imagine we're about to create a list here. We've got an ID number, Compensation, Status, Department.

The ID Numbers must be six characters. They might be all numbers. They could be all letters.

It could be a combination of either. Let's say it's a combination of letter and numbers in our example here. Let's select column A.

Before using the Data Validation feature, highlight the cells where you want the rule to be in place. These are referred to as data validation rules.

We go to the Data tab in the ribbon, and we have a choice called Data Validation.

Pick from a list of rules to limit the type of data that can be entered in a cell. We're controlling data before it goes into a list.

The Data Validation dialog box has three tabs. A Settings tab gives us some different choices. Text length, Whole umber, Decimal.

We might want to choose Whole number in some case if it were all numbers. We could set it to another Length or make it between a certain set of numbers, something like that.

If it's a mix of letters and numbers as it might be in this case, we can choose Text length, and this will allow numbers as well.

And we can set this to be between certain ranges or exactly equal to. We're going to say equal to here. Maybe in this case, it's seven.

Now that means it has to be exactly seven. If the ID Numbers vary, say, between five and seven, we would say between, get a dialog box like this, and put in a five and a seven.

Let's say in this case it's exact, equal to, And we'll make it be seven. So we're setting up a rule for here.

We can even provide an Input Message.

There's a box right here, and we can give it a Title if want, and we could say caution, warning, reminder, something like that.

We can put in a colon if we wish, not necessary. And then a description.

Must be exactly seven characters long. We could be more specific and say something like, it must begin with a letter.

Now maybe most of the people doing the data entry know that, so we don't necessarily say that.

If you provide an Input Message, and it's optional, you'll see that any time you click in the cells that you selected ahead of time, must be exactly seven characters long.

Well let's try this with only five characters. We get a pop-up message.

This value doesn't match the data validation restrictions defined for this cell. If we click Retry, we're still in edit mode.

If we click Cancel, we're out of here. So I'll click Retry, and maybe I just forgot to type in that final two characters there, the 12.

Looks good now. Enter. It's acceptable. Too many characters, doesn't work.

Now it could be all numbers. That's acceptable. It could be all letters.

That's acceptable, too.

The only restriction we assigned here is seven characters long.

So I think you can see how that will work in different situations.

Learning doesn't stop here. Discover more expert led tutorials at LinkedIn Learning. Start your free trial today, at

Enjoy one month of free access to LinkedIn Learning

Learn from recognized industry experts, and get the business, tech, and creative skills that are most in demand.

  • Get unlimited access to over 4,000 video courses.

  • Receive personal recommendations based on your LinkedIn profile.

  • Stream courses from your computer or mobile device.

  • Take courses for every level – beginner to advanced.

  • Practice while you learn with quizzes, exercise files, and coding windows.

  • Choose a plan for yourself or your entire team.

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!