Blue Flower

Data Validation in Excel - basic

Probably you are using Excel calculations in spreadsheets, sometimes asked how you can prevent or limit other user, who uses your Excel spreadsheet application, when entering data in a cell or range of cells. On the Microsoft web of Data Validation written and widely discussed, but here I want to shorten the information on your layperson picturesque way to approach and explain Excel users of Data Validation in Excel.

This feature Data Validation in Excel allows you to author Excel application to restrict or prevent the application user to enter incorrect data in Excel cell. Also to inform the user applications on how to enter data in a cell. Also this feature can allow entry of incorrect data or values but in this case, you can alert the user that the application is data entry invalid. Furthermore, by using the Data Validation you can give instructions, user application how the data should be entered in a cell. You can warn users that the data in a given cell is not deleted because it contains a formula, and the like. This warning appears in a pop-up box when the user selects the respective cell.

You can use Data Validation to create a drop down list to have or choice of certain data in the relevant Excel cell

Data Validation is located on the tab ribbon DATA command in the 'Data Tools' group.

Data Validation toolbar on the ribbon in Excel

Data Validation feature is available in Microsoft Excel

Which allows you to do the following:

  • In the cell or range of cells we can create a list of just some items that are allowed to enter the cell.
  • In the cell or range of cells we can create an alert message with instructions on the type of data that is allowed to enter the cell.
  • In the cell or range of cells we can create an alert message appears when a user entered incorrect information.
  • In the cell or range of cells, we can check if an error occurs entered data using the toolbar Formula Auditing.
  • In the cell or range of cells, we can define a series of numerical values that can be entered into the cell or range of cells.
  • In a given cell to check whether the entry is correct in relation to the calculation of other cells.
  • etc ...

Where to use Data Validation?

Data Validation we can use in each Excel Workbook, on each worksheet or Excel application where we want to define certain rules when entering data into specific cells, as well as prevent or restrict the user's Excel Workbook that incorrectly entered data into cells. Also using the Data Validation we can indicate instructions for entering data types, through a pop-up box when the user selects the respective cell. Frequent use Data Validation is when you want to create a drop down list in certain cells.

Available messages in the Data Validation

The features of Data Validation settings, there are three tab and two types of messages that are displayed when a user selects a particular cell to which it is applied Data Validation rule.

  1. Tab 'Settings' - Validation criteria
  2. Tab 'Input message'
  3. Tab 'Error Alert' message

1. Settings Data Validation criteria and Excel cell

In this part of the Data Validation dialog box on the Settings tab, we set the rules relating to the currently selected cell or range of cells. You notice that I am on the cell 'A1', set the rule or criterion to the respective cell may enter only number that consists of one digit.

Set criteria for data validation in Excel - Data Validation Settings Tab

On this Data Validation the Settings tab in the "Allow" is drop-down list where we can choose from the list one of the options. Depending on your intent, you'll use one option from the list.

  • Any value (input any information)
  • Whole number (If we want to allow the entry of an integer then we choose "Whole number")
  • Decimal (If we want to require mandatory registration of a decimal number then we choose "Decimal")
  • List (If we want to create a drop down list then we choose "List")
  • Date (If we want to tie the user to the cell may enter only date then we will choose "Date")
  • Time (If we want to tie the user to the cell may enter only during that time we will choose "Time")
  • Text length (If we want to restrict users to only be entered by a number of text characters or the length of the text then we choose "Text length")
  • Custom (If we want to use a formula for further calculations and calculations in cells or range of cells then we choose "Custom")

2. Input messages during the selection of Excel cells (Validation criteria - Input Message)

In this part of the Data Validation dialog box tab Input Mesasage, I set up the input message. When user selects the respective cell will appear in a pop-up box and in it the text that will alert the user and provide him with information relating to a selected cell. User can remove this message by pressing the ESC key while the cell is selected.

Input message when selecting cells using the Data Validation - Input Message Tab

3. Items of warning messages when you enter specific data into a cell (Data Validation - Error Alert)

In this part of the dialog box Data Validation, Error Alert tab, I set up a warning message. When the user, in the respective cell enters a single digit Excel will not respond. When a user types a two-digit number or the three-digit number or text or something else Excel will respond and display a warning message. Actions that the user can do after a warning message, depending on the type of option or messages that we've specified in Error Alert.

How to set up a warning message on the Data Validation Excel features

Types of icons and style options on Data Validation Error Alert tab

STOP style icon in Excel Data Validation

If you use the Stop style/icon, Excel will prevent the entry of incorrect data in a cell. This pop-up message with the 'Help' button has 2 button, or two options.

a) - Excel will not allow to enter incorrect data and will appear this message, then the user can click on the Retry button continues to be positioned on a given cell, given that the respective cells still selected then the user can modify the data that corresponds to the request set rules, according to the criteria that we set in the Settings tab.

b) - The user can cancel the input data in the respective Excel cell (Cancel button).

Stop messages for incorrect data entry into the cell via the Excel Data Validation

WARNING style icon and Data Validation

If we use the Warning style/icon, Excel will warn the user of incorrect entry of data into a cell but it will not prevent that data is entered into the cell. When it displays the pop-up message, the user has three options.

a) - The user can cancel the input of incorrect information by clicking on the NO button, but continues to be positioned in the respective cell so the user can enter other data

b) - The user can regardless of the alert execute incorrect data entry by clicking on the YES button. Excel will allow the user to input incorrect data after entering the selection to move one cell below.

c) - After this warning the user can change his mind, and give up the input data into a cell by clicking on the CANCEL button. Excel automatically cancels a data input or the respective cells still selected.

Invalid input data into a cell - a warning message

INFORMATION style icon and Excel Data Validation

If we use the Information style/icon, Excel will warn the user of incorrect data entry into the cell, but will not prevent the user to enter data into a cell. When it displays the pop-up message, the user has two options.

a) - If the user clicks the OK button, Excel will allow data entry and after entry of data to select the next cell below.

b) - If the user clicks the Cancel button, Excel will ignore the entered data, Excel will then cancel the data and the user remains on the respective cell.

Pop-up information messages entering incorrect data in Excel cell

Please see another related tutorial with Data Validation Examples.