Excel Data Validation (Table of Contents)

• Data Validation in Excel
• Purpose of Excel Data Validation
• How to Use Data Validation in Excel?
• Examples of Excel Data Validation

Data Validation in Excel

Data validation is a feature available. Excel data validation allows you to – make a list of the entries that are allowed/ not allowed to be entered in a worksheet cell, create an error/alert/warning message to explain more about the allowed values in a worksheet cell, create messages to be shown when an invalid data is entered in a cell.

Purpose of Excel Data Validation

1. The purpose of data validation in excel is to restrict users from entering the invalid data values in the worksheet.
2. data validation in excel is also useful to quickly gather, sort and study the data having said that the values have a criterion defined and can be seen via the Data Validation window.
3. Data Validation in Excel acts a guideline to the users while entering the data as custom messages mentioning the valid data format is displayed when a worksheet cell is selected.

How to Use Data Validation in Excel?

Here we are going to discuss the steps to create data validation in excel.

1. The first step to create data validation in excel is to select the cells or column containing data.

2. Goto Data tab and click on Data Validation

3. Choose option 1 i.e. Data Validation from the list of options.

5. Once you click on Data Validation, a new window will appear as shown in the following screenshot.

6. 1^st tab is Settings. It contains validation criteria and the data format which is to be allowed.

7. One can choose to ignore blank values by clicking ‘Ignore blank’ checkbox.

8. 2^nd tab is Input Message where one can enter the message to be displayed when a cell is selected.

9. 3^rd tab is Error Alert. This is used to configure the error message/s to be displayed whenever an invalid data entry is made in the configured cells or column. The types of error and hence the sign displayed for the error can be Stop, Warning or Information.

Different Data Formats of Allowed Data

1. The whole Number – states that the format to be allowed is the whole numbers.
2. Decimal – states that the format to be allowed is decimal numbers.
3. List – states that the format to be allowed is a drop-down list.
4. Date – states that the format to be allowed is the dates.
5. Time – states that the format to be allowed is the time values.
6. Text Length – states the allowed length of the text to be entered in a cell.
7. Custom – indicates any custom formula.

Different Criteria for Excel Data Validation

1. between – states that the value should be between the specified minimum and maximum numbers.
2. not between – states that the value should not be between the specified minimum and maximum numbers.
3. equal to – states that the value should be equal to the given number.
4. not equal to – states that the value should not be equal to the given number.
5. greater than – states that the value should be greater than the given number.
6. less than – states that the value should be lower than the given number.
7. greater than or equal to – states that the value should be greater than or equal to the given number.
8. less than or equal to – states that the value should be less than or equal to the given number.

Different Alert Styles in Excel Data Validation

1. Stop – It stops users from entering invalid data in a worksheet cell. The Stop alert window gives two options to the user – Retry and Cancel.

2. Warning – It warns users that the invalid data is entered. However, a warning does not stop the user from entering the invalid data. It gives three options to the user – Yes, No and Cancel.

3. Information – It informs user that the data entered is invalid. Like Warning, it does not stop the user from entering the invalid data. It displays two options to the user – OK and Cancel.

Examples of Excel Data Validation

Excel Data Validation is very simple and easy to use. Let understand the working of Data validation in excel by some examples.

Example #1 – Number Validation

As shown in figure. 1, -2.5 is marked red indicating an invalid data. A tooltip with message – ‘Enter number greater than zero’ is shown. A rule assigned to column. B is as follows.

As shown in figure. 2, the validation criteria entered is, the number “decimal” number should be greater than or equal to zero. Meaning, negative values are restricted.

Things to Remember About Data Validation in Excel

1. Excel data validation is not full proofed and be breached easily.
2. If the data from cells with no-validation is copied into the cells with validation in place, then the entered data is not validated and excel data validation overrides the data validation resulting into no validation on the copied data.

You can download this Data Validation Excel template here – Data Validation Excel Template

Recommended Articles

This has been a guide to Data Validation in Excel. Here we discuss how to create Data Validation in excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

• Sensitivity Analysis in Excel
• Convert Numbers to Text in Excel
• PERCENTILE Excel Function
• Paste Special Shortcuts

The post Data Validation Excel appeared first on Learn Investment Banking: Financial Modeling Training Courses Online.