How to Use Data Validation in Excel: Full Tutorial (2024)

Sometimes we have to share our Microsoft Excel files with multiple users to fill in data.

The problem with sharing Excel files is that other people might input the wrong data. Which in turn screws up filtering, lookups, and other types of features and functions.

To prevent this, use data validation!

And I’ll walk you through how to do that here, step-by-step, in this table😊

Microsoft Excel data validation table

If you want to tag along, download the practice workbook here.

What is data validation in Excel?

Excel data validation helps to check input based on validation criteria. 🤔

That means data validation can be used:

  • To check if a value is a number, a date, a time, a text with a specified length or
  • To show a dropdown menu to the user with set options.

As a result, it can help users enter only valid data into a cell.🤗

You can also show a custom message to the user before entering data into a cell.

You can also display a warning message if the users enter invalid data.

This will simplify data entry and minimize input and typing errors. 😊

Before using Excel’s data validation feature on our table, we shall become familiar with it.👍

How to add data validation

  1. To start, you have to select one or more cells in the Excel file for data validation.
  2. Then, go to the data tab.
Go to the data tab
  1. Click the data validation button, in the Data Tools Group, to open the data validation settings window.
Click data validation button in the data validation group of the data tab

This is how the data validation window will appear.

Data Validation Window
  1. The first tab in the data validation window is the settings tab.

You can create rules for data validation in this tab.

Settings tab to set data validation rules.

For example, we can specify that the date in the first column must be a future date.

In the following part, we will go through all of the data validation rules in depth🤗.

  1. Go to the input message tab to create an input message.
Input message tab

Put a tick mark for the “Show input message when cell is selected”.

Input messages can be a guide to user input data in the correct format and reduce entering invalid data.

Add a title for the input message.

Entering input message title

Enter a custom message for the input message.

Typing the input message

When you select a cell with data validation, you will see an input message similar to this.

Input message
  1. Finally, go to the error alert tab to create an error message for invalid data.

When a user enters an invalid entry or invalid value, you can show an error alert.

Error alert tab to create error alerts

Put a tick mark next to the “show error alert after invalid data is entered” in the error alert tab.

Put a tick mark

You can select 3 different styles for the error message when a user inputs invalid data.

Next, give a title to your error alert.

Entering a title for the error message

Then, enter a custom error message.

Entering the custom error message

This error message will show up if a student enters a date that has passed.

Show error alert

Let’s apply data validation rules to our Excel data table.

Types of data validation rules

Dates and time validation rules

Date data validation rule

Students can make an appointment date only for a future date.

Let’s learn how to avoid entering an invalid date in the date column.

  1. Select “date” from the allow box in the settings tab.

By selecting “date”, restrict data entry types to date in the validation cell.

Kasper Langmann, Microsoft Office Specialist
  1. Select a condition under the data drop down list and specify data validation criteria in the start date box.
Condition for data validation

Students can now only enter future appointment dates in the first column of the Excel table.

Time data validation rule

Next, we want to make sure that students enter appointment times between 8 a.m. and 5 p.m.

  1. Select “Time” as the data validation criteria from the allow box.

By selecting “Time”, all other entries other than time will become invalid entries in data validation cells.

Kasper Langmann, Microsoft Office Specialist
  1. Select a condition under the data drop down list in the data validation dialog box.

Then enter the data validation rule for the time column.

Entering condition for data validation in the data validation dialog box

Pro Tip!

How to change data validation settings for many cells easily?

No need to copy data validation one by one.

You can check the below box to apply the changed data validation settings to all cells with the same settings.

Apply changed data validation settings to all cells with the same settings

Number validation rules

Whole number data validation rule

We want to make sure that no more than three students attend the appointment.

In other words, we need to create an error message as invalid data if the data entry is not a whole number or is more than 3.

Let’s learn how to apply the whole number validation rule.

  1. Select the “Whole number” as the data validation criteria from the settings tab.
  1. Select a condition under data in the data validation dialog box.

I select “between” and give 1 & 3 as the minimum and maximum values.

Condition for data validation

Students can enter only 1, 2, or 3 in column C.

Decimal data validation rule

We would like to get the group results when they are scheduling the appointment.

The results should be between 0% to 100% (i.e., the results should be between 0 to 1).

Let’s learn how to do data validation in excel for decimal values.

  1. Select “Decimal” as the data validation criteria from the settings tab.
  1. Select a condition from the list under data and apply validation criteria.
Select a condition for data validation

Text validation rule

Do you think we can have data validation in Excel only for numeric values?🤔

No. We can apply for data validation in excel for text length as well.

When the user inputs a text that has a different length to the given data validation that data entry can be recognized as invalid data.

In this table, the group name should be less than 15 letters.

Let’s look at how we can validate the group name.

  1. Select the “Text length” as the data validation criteria from the settings tab.
  1. Select a condition for the data validation from the data box.
Insert condition for data validation

Drop-down lists

In the last column, students must enter either “Batch 1” or “Batch 2”.

We can give those 2 options in a cell dropdown box.

If the user selects “Batch 1”, it will show in that cell.

Let’s learn how to create a drop down list.

  1. Select the “List” as the data validation criteria.
  1. Enter the items for the drop down list in the source box or give a specified range for the drop down list in the source box.

To give a cell reference, click the upside arrow in the source box.

Enter the items for the drop down list

Pro Tip!

Is it difficult to update drop down list, if the number of options for drop down list are changing every time?😕

Don’t worry.

Give the cell reference of the source box to a Table in Excel.

The table automatically updates your list.😍

With the drop down list, users can easily select the batch.

Drop down list

Since drop down list is a very common data validation feature, it is good to learn this in a very detailed manner.

I highly recommend checking out my drop-down tutorial here to learn all about it!

Now users can enter only valid entries into our Excel table.

How to remove data validation

Now you know how to apply data validation in excel.👏

Do you want to know how to remove data validation?

It is very simple!🙂

  1. Select the cells that contain data validation you need to remove.
  2. Go to the data tab
  3. Click on the data validation icon.
  4. Click “Clear All”.
Remove data validation

That’s it – Now what?

Now, you can easily add data validation to your cells, making it almost impossible for other users to mess up data input 😊

Pretty great, right?

But messed up data is only a problem if there’s something relying on that data.

Functions like IF, SUMIF, and VLOOKUP rely on data being neat and orderly. And those functions are the backbone of most important spreadsheets.

If you don’t master them yet, click here to learn IF, SUMIF, and VLOOKUP in my free 30-minute online course.

Other resources

If you want to use more advanced date and time functions as validation conditions, read our article about Excel’s Date and Time Functions.

After applying data validation, it is quite simple to narrow down your data to find what is relevant with Excel Filters. Read our How to Filter in Excel article to find out more information about the Excel filter tool.

And not just that! When you have complete data, you can easily rearrange it using Pivot Tables.