Excel: DATA VALIDATION

Data validation feature allows you to set up certain rules that dictate what can be entered into a cell.

It allows you to do the following:

  • Make a list of the entries that restricts the values allowed in a cell.
  • Create a prompt message explaining the kind of data allowed in a cell.
  • Create messages that appear when incorrect data has been entered.
  • Check for incorrect entries by using the Auditing toolbar.
  • Set a range of numeric values that can be entered in a cell.
  • Determine if an entry is valid based on calculation in another cell.

Create Data Validation Rule

To create the data validation rule, execute the following steps.

  • Select cell C2.
  • On the Data tab, in the Data Tools group, click Data Validation.

On the Settings tab:

  • In the Allow list, click Whole number.
  • In the Data list, click between.
  • Enter the Minimum and Maximum values.

 

Types of Validation Criteria You Can Apply

The Settings tab of the Data Validation dialog box enables you to specify a wide variety of data Validation criteria. The following options are available in the Allow drop-down box. Keep in mind that the other controls in the Settings tab vary, depending on your choice in the Allow drop-down box.

Any Value: Selecting this option removes any existing data validation. Note, however, that the input message, if any, still displays if the check box is checked in the Input Message tab.

Whole Number: The user must enter a whole number. You specify a valid range of whole numbers by using the Data drop-down list. For example, you can specify that the entry must be a whole number greater than or equal to 100.

Decimal: The user must enter a number. You specify a valid range of numbers by using the Data drop-down list. For example, you can specify that the entry must be greater than or equal to 0 and less than or equal to 1.

List: The user must choose from a list of entries you provide. This option is very useful, and I discuss it in detail later in this article (see “Creating a drop-down list”).

Date: The user must enter a date. You specify a valid date range by using the Data drop-down list. For example, you can specify that the entered data must be greater than or equal to January 1, 2007, and less than or equal to December 31, 2007.

Time: The user must enter a time. You specify a valid time range by using the Data drop-down list. For example, you can specify that the entered data must be greater than 12:00 p.m.

Text Length: The length of the data (number of characters) is limited. You specify a valid length by using the Data drop-down list. For example, you can specify that the length of the entered data be 1 (a single alphanumeric character).

Custom: To use this option, you must supply a logical formula that determines the validity of the user’s entry (a logical formula returns either True or False). You can enter the formula directly into the Formula control (which appears when you select the Custom option), or you can specify a cell reference that contains a formula. This article contains examples of useful formulas.

The Settings tab of the Data Validation dialog box contains two other check boxes:

Ignore Blank: If checked, blank entries are allowed.

Apply These Changes to All Other Cells with the Same Setting: If checked, the changes you make apply to all other cells that contain the original data validation criteria.

Remember that data validation does not apply to the calculated results of formulas. In other words, if the cell contains a formula, applying conditional formatting to that cell will have no effect.

Input Message

Input messages appear when the user selects the cell and tell the user what to enter.

On the Input Message tab:

  • Check ‘Show input message when cell is selected’.
  • Enter a title.
  • Enter an input message.

Error Alert

The Error Alert tab allows you to define what happens when a user enters an invalid value.  You can customize the error alert dialog box Title and Error message.  You can also pick a Style.

You’ll notice there are three choices for the alert style:

  • Stop
  • Warning
  • Information

Each alert style gets its own icon. But more importantly, each style defines the behavior of this feature, and specifically, what message is displayed and which buttons are available in the alert dialog.

Stop

If you set the error alert style to Stop, then you are asking Excel to prevent the user from typing in an invalid value. A value that does not meet the condition specified on the Settings tab will be rejected, and will not be stored in the cell. This is the default style. The error alert dialog that pops up when an invalid value is entered is displayed below.

The default message to the user is “The value you entered is not valid.  A user has restricted values that can be entered into this cell.”  You can see the user has the option to click Retry, Cancel, Help, or to close the dialog box with the standard “X” button in the upper right of the dialog. None of these choices cause Excel to store the invalid value.

Warning

The other two alert styles, Warning and Information, allow the user to override the data validation settings and enter invalid data. When the Warning style is selected, an invalid entry brings up the dialog box below.

The default message to the user is “The value you entered is not valid.  A user has restricted values that can be entered into this cell.  Continue?”  You can see that the user has the option to click Yes, which will instruct Excel to store the invalid value in the cell. The No button is selected by default, so if the user is working quickly and disregards the message and hits the Enter key on the keyboard, the No button is selected and the invalid value is not stored.

Information

When the Information style is selected, an invalid entry brings up the dialog below

The default message to the user is similar, except, you’ll notice the user can click the OK button.  The OK button causes Excel to store the invalid value.  The OK button is the default button, so if the user is working quickly and disregards the message and hits the Enter key, the invalid value is stored to the cell.

For example :  how to enter a Whole Number

  1. Select cell A5.
  2. On the Data menu, click Validation and click the Settings tab.
  3. In the Allow list, click Whole number.
  4. In the Data list, click between.
  5. In the Minimum box, enter 1.
  6. In the Maximum box, enter 10.
  7. You can use cell references for Steps 5 and 6 to specify cells that contain the minimum and maximum values.
  8. Click OK.
  9. Enter the value 3 in cell A5. The value is entered without error.
  10. Enter the value 33 in cell A5.Because the data validation settings you created for cell A2 (an Information Alert) do not apply to those for cell A15, you receive a Stop Alert message (which is the default value) and your only options are to click Retry or Cancel.

Data Validation for Date

  • Select cell C4, and on the Excel Ribbon, click the Data tab
  • Click Data Validation (click the upper section of the command)

  • On the Settings tab of the data validation dialog box, from the Allow drop down, click Date

  • In the Data drop down, leave the default setting of Between, because we want to limit the entries to dates between specific start and end dates.

How To Make a List of Entries Allowed in the Cell

You can make a list of the entries you will accept for a cell on a worksheet. You can then restrict the cell to accept only entries taken from the list by using the data validation feature.

To create a drop-down list and restrict values in the cell to these entries, follow these steps:

  1. Select cell A1.
  2. On the Data menu, click Validation.
  3. On the Settings tab, click List in the Allow drop-down list.
  4. By default, the Ignore blank and In-cell Dropdown check boxes are selected. Do not change them.
  5. In the Source box, type a,b,c. Click OK.NOTES:
    You can also enter a named range or cell reference if it contains a list of values. Both must be preceded by an equal sign.
    There is a 255 character limitation for this dialog.
  6. Cell A1 now has a drop-down list next to it and you can use this list to select the value to enter in the cell.
  7. Click the drop-down list and then click any item it contains.

This value will be entered in the cell.

NOTE: You can manually enter “a”, “b”, or “c”, (without quotation marks) in the cell; you do not have to select these from the list. If you try to manually enter anything other these values, a stop message appears and you are unable to keep the value in this cell. Your only options are Retry or Cancel.

Example of how to use Custom

  • Select cell B3
  • On the Ribbon, click the Data tab, then click Data Validation
  • Choose Allow: Custom
  • For the formula, enter: =SUM(B1:B6)

 

Data Validation Result

 

Note: to remove data validation from a cell, select the cell, on the Data tab, in the Data Tools group, click Data Validation, and then click Clear All. You can use Excel’s Go to Special feature to quickly select all cells with data validation.

 

Create a Message That Appears When Incorrect Data Is Entered

The style of the error message Microsoft Excel displays when you type incorrect data determines whether the restrictions are enforced. You can display a message that stops you from entering data values except those specified. You can also display explanatory and warning messages that let you enter out-of-range data, or you can set limits on the data but not display any messages.

follow these steps:

  1. Select cell A1.
  2. On the Data menu, click Validation and then click the Error Alert tab.NOTE: Make sure the Show error alert after invalid data is entered check box is selected.

Create a Stop Alert Message

If you create a Stop Alert message and enter invalid data in the cell, your only options are Retry or Cancel; you are not allowed to enter invalid data in the cell.

    1. In the Style list, click Stop.
    2. In the Title box, type Warning for cell A1. This is the title for the message box that appears.
    3. In the Error Message edit box, enter The only allowable values for cell A1 are a, b, or c. This is the body of the message that appears and is limited to 225 characters.
    4. Click OK.

3. Manually enter t, in cell A1.

The Stop Alert message you created appears and your only options are
Retry or Cancel.

4. Click Cancel on the Stop Alert message box.

5. Select cell A1.

6. On the Data menu, click Validation and then click the Error Alert tab.

NOTE: Make sure the Show error alert after invalid data is entered check box is selected.

  To apply the circles, select the cells you want to evaluate and go to Data > Data Tools > Data Validation > Circle Invalid Data.

  To quickly remove data validation for a cell, select it, and then go to Data > Data Tools > Data Validation > Settings > Clear All.

  To find the cells on the worksheet that have data validation, on the Home tab, in the Editing group, click Find & Select, and then click Data Validation. After you have found the cells that have data validation, you can change, copy, or remove validation settings.

Application of Data Validation are as follows

  • Data validation, is a technology that uses process information and mathematical methods in order to automatically correct measurements in industrial processes.

 

  • Data validation allows for extracting precise and reliable information about the state of industry processes from raw measurement data and produces a single consistent set of data representing the most likely process operation.

Limitations of Data Validation are as follow

1.Item Limit in Drop Down List

2.The font size in a data validation list can’t be changed, nor can its default list  length, which has a maximum of eight rows.

3.If you type the items into the data validation dialog box (a delimited list), the limit is 256 characters, including the separators.

About Madhulika:

Madhulika is B.E in Telecommunication. Currently she is working as an Analyst Intern with NikhilGuru Consulting Analytics Service LLP, Bangalore. She has prior worked for around 2 Years with CAN Pvt. Technology & Nokia.

Please follow and like us:
error

Be the first to comment on "Excel: DATA VALIDATION"

Leave a comment

Your email address will not be published.


*


error

Subscribe for Data Analytics Edge Newsletter & Share..:-)

error: Content is protected !!