Data Validation in Excel: 101 Beginner’s Guide

Data Validation in Excel is a feature to limit data entry to a cell, row, column, or range according to user needs.

This feature can help you reduce data entry errors if you want to limit the type of data entry in a cell.

Important! Please visit the Excel Range Guide if you don’t know what range, cell, column, and row are.

What is the point of this data validation?

For example, I want cell A4 only to be filled with valid dates or certain numbers/text. Or I want cell A4 to use a dropdown list.

So, for that purpose, start studying this Data Validation Guide.

Benefits of Using Data Validation in Excel

There are 5 main benefits that you can get when using Data Validation in Microsoft Excel as follows:

1. Limit data filling in a cell, row, column, and range with certain text/numbers. You can even limit filling in a certain number/date or time range.
2. Limit data filling in a cell/range with a formula (for example IF Formula or SUM Formula etc.).
3. Create a Dropdown list to limit data entry to certain cells/ranges.
4. Displays a message about instructions on how to fill in data.
5. Displays an error message if the user enters an incorrect value.

Of these five benefits, of course, there are many other benefits that you can get. To better understand the Data Validation feature in Excel, please download the practice file and follow the examples in this guide until the end.

Add Data Validation in Excel

To add data validation in Excel, please follow the following steps:

1. Select the cells that will apply data validation.
2. Click the Data Tab on the Ribbon, then click Data Validation in the Data Tools group.
3. Define validation criteria.
4. Customize the Input Message that will be displayed to the user.
5. Customize Error Alerts as reminders to users to input the correct data type.
6. Click OK to apply it.

Important! There are currently 8 types of validation data in Excel. I will explain it in the next section.

In general, the Data Validation feature in Excel will provide the same benefits for you.

The following dropdown list type data validation example will show you the features you can use when applying data validation:

• Filling Instructions, you can change this filling instruction message as needed to provide charging instructions to the user.
• Dropdown list, this section is a type of data validation. With this, cell B1 can only be filled with the list in the dropdown.

If the user enters incorrect data, an error message will appear as follows:

You can also modify this error message according to your needs. With the right error message, users who enter the wrong data can know what data can be filled in that cell.

You can even make data validation synchronized with each other like the following multilevel dropdown list:

The 2 examples above are only for 1 type, namely the Dropdown list. You can use data validation for other types. Below I explain the types of Excel Data Validation.

Types of Data Validation in Excel

Try clicking the Data Tab, and then clicking Data Validation. The Data Validation Window will appear as shown below:

Note: You can also open the Data Validation dialog using the keyboard shortcuts. The method is to press the Alt, A, V, V keys sequentially.

Currently, there are 8 types of Excel data validation which are quite popular. These eight types are useful for certain purposes.

For example, you can’t use the Whole Number type to limit filling data by date.

So, that there are no mistakes in using it, let’s learn about the uses of each type of data validation:

#1 Any Value

Any Value is useful if you don’t want to limit the contents of a cell. With this type, users can enter any data (numbers/text/characters) into cells.

There are no special settings for the Any Value type. However, you can also use the filling instructions feature.

#2 Whole Number

Use the Whole Number type if you want to limit filling cells/data ranges with positive or negative whole numbers. For example -3, -2, -1, 0, 1, 2, 3, etc.

For the Whole Number type, you can use the following 8 criteria:

1. Between, that is, users can only fill in cells with round numbers between the minimum and maximum values.
2. Not Between, that is, the user cannot fill cells with whole numbers between the minimum and maximum values.
3. Equal to, that is, the user can only fill in cells with whole numbers that exactly match this criterion.
4. Not Equal to, that is, the user cannot fill in cells with whole numbers that exactly match this criterion.
5. Greater Than, that is, the user can only fill in cells with whole numbers that are greater than the minimum value that can be determined.
6. Less Than, that is, the user can only fill cells with whole numbers that are smaller than the maximum value that can be determined.
7. Greater Than or Equal to, that is, the user can only fill cells with whole numbers that are smaller than or equal to the minimum value that can be determined.
8. Less Than or Equal to, that is, the user can only fill cells with whole numbers that are less than or equal to the maximum value that can be determined.

#3 Decimal

The Decimal type is similar to the Whole Number type. However, the Decimal type is more specifically for validating numbers in the form of negative or positive decimal numbers.

Just like the Whole Number type, the Decimal type also has 8 criteria that have the same meaning, namely Between, Not Between, Equal to, Not Equal to, Greater Than, Less Than, Greater Than or Equal to, and Less Than or Equal to.

#4 List

The List type is useful if you want to limit data filling to a list (special list).

In this type of list, you can fill in special data using a dropdown list or not.

You can also use Excel formulas/functions such as IF formulas, etc. to create data entry lists.

#5 Date

Use the Date type if you want to limit data entry based on valid dates.

Just like the Whole Number and Decimal types, you can use the Date type to filter data based on 8 criteria (Between, Not Between, Equal to, Not Equal to, Greater Than, Less Than, Greater Than or Equal to, and Less Than or Equal to ).

#6 Time

If the Date type limits data entry based on date, the time type limits data entry based on time (hours, minutes, and seconds).

You can also use 8 criteria for this time type.

Important! Before you try any further, I suggest you understand the Excel Number Format. Please visit the Cell Formats Chapter and all its subchapters.

#7 Text Lenght

Use the Text Length type if you want to limit data input based on the number of characters.

For example, as in the image above, I want to limit the filling-in of cell C1 to a minimum number of characters of 5 and a maximum of 10.

If I type Rolan M@rd@n! in cell C1, Excel will give an error message like the following image:

Why?

Try counting the number of characters (including spaces) in the words Rolan M@rd@n!, there are 13 characters.

However, if I type Rolan M@rd, then the data is valid because the number of characters = 10 according to the maximum number as shown in the following image:

What do you think?

Has your understanding changed? If yes, let’s continue.

#8 Custom

For this Custom type, you can create special data validation with criteria that you can determine yourself using Excel Formulas or Functions.

This type will produce more complex data validation. Make sure you understand the writing rules and the meaning of each function/formula that will be used.

Circle Invalid Data in Excel

You can also tell Excel to find out which cells contain invalid data.

For example, I implemented Data Validation on the range A1:A10 using the Dropdown List type. I use the criteria numbers 1 to 10 in the Dropdown List as shown in the following image:

However, cells A1, A4, A6, and A8 contain invalid data – they don’t meet the data validation criteria.

The example above is just a small amount of data. Of course! You can spot invalid data in the blink of an eye.

What if you have hundreds of thousands of rows of data? Your time will be wasted just looking for invalid data.

Yeah! Excel will circle cells that contain invalid data based on data validation criteria. You can use the circle invalid data feature in Excel by following these steps:

1. Click any cell in your Worksheet, then click the Data Tab.
2. Click the arrow icon next to Data Validation, then click Circle Invalid Data. Results:

You will find red circles on cells that have invalid data. With this, you can update the data to comply with the data validation criteria.

That is easy!

I will discuss examples and how to use Excel data validation in full in the next Sub-Chapter.

Please use the following Excel Data Validation Guideline Shortcut. Or visit M Jurnal Excel Guide Center to learn all about Microsoft Excel.