Our Recommendation for You Search your Query, You can find easily. for example search by book name or course name or any other which is related to your education

Followers

Data Validation - examples


Example, how to create a drop-down menu using Excel Data Validation

Although, I had already written several tutorials on the drop-down menus that you can create using Data Validation, here is a brief illustrative of making the drop-down list in three ways. This example relates to the cell 'A1' and creates a drop-down menu in the cell.
The aim is to, when we select the cell, we want to be in the cell appears button to open the drop-down menu, where we can choose a specific item and is set as data in a given cell. For more information on specific items in the tutorial below you can visit the links that are found in the text of the tutorial.

Source - direct entry of items drop-down menu

In the first picture below you see cell 'A1' and has already created a drop down menu with all items contained in the menu. This menu has been created in the Data Validation Excel features. I'm on the Settings tab of the criteria in the Allow: choose the item "List" in the field "Source" I entered all the items that I want to have in the drop-down list. These items are separated by a separator semicolon (;)
Depending on the settings of your Windows operating system you may need separator semicolon (;) replaced with a comma (,)

Apples;Pears;Plums

Example drop-down list in Excel direct entry of items in the drop down menu

Source - a range of cells for the list of items drop-down menu in the Data Validation


In the picture below you will see that I am in this example in the "Source" field entered the Absolute Address range of cells that contains all the items you want to have the list of drop-down menu.

=$K$1:$C$3
How to create a drop down list in the Excel using Data Validation, using a range of cells with absolute addresses

Source - named the range of cells for the list of items drop-down menu in the Data Validation

In the picture below you will see that I am in this example in the "Source" field entered a Named Range of data that contains all the items you want to have the list of drop-down menu.

=fruit_juices
Drop-down list in Excel using a named range of cells

Data Validation and two conditions

In this example, Data Validation we have two conditions. Our goal is to limit the user to fulfill the conditions when entering text in a range of cells 'A1:A5'. The first condition is the initial of the first name of the person who has to be the letter "M". The second condition is the number of characters in the name of the person. So a person's name must contain 6 characters.
  • On the "Settings" to "Custom" item in the "Source" field is set formula =COUNTIF(A1;"M?????")=1
  • In the "Input Message" inserted text => "Type the name that begins with the letter" M "and has six characters"
  • In the "Error Alert" inserted text => "You must enter a name that starts with the letter" M "and has 6 characters"
To create these conditions and set data Validation steps: select all the cells you want to apply Excel Data Validation rule. Open the Data Validation dialog box and enter the above formula and the necessary texts.

Data Validation and two conditions for entering text in a cell

Data Validation and condition of the first letters in the text

In the following example, Data Validation we have one condition. Our goal is to restrict users so that the user enters a condition when entering text in a cell that is located in the a range of cells "A1: A5. The condition is the first letter of the text, this must be the letter "B".

  • On the "Settings" to "Custom" item in the "Source" field is set formula =LEFT(A1)="B"
  • In the "Input Message" inserted text => "Enter a word that starts with the letter 'B'"
  • In the "Error Alert" inserted text => "You must enter a word that starts with the letter 'B'"

To create these conditions and set the Data Validation steps: select all the cells you want to apply Excel Data Validation feature. Open the Data Validation dialog box and enter the above formula and the necessary texts.

How to condition the first letter of the text input in a cell using Data Validation

Data Validation and condition entry of a single text or ban entry duplicates

In the following example Data Validation, we have one condition. Our goal is to limit the user to fulfill a requirement when entering the number in the cell that is in the range of cells 'A1:A5'. A condition is a unique number in the range of cells.
  • On the "Settings" to "Custom" item in the "Source" field is set formula =COUNTIF($A$1:$C$5;A1)=1
  • In the "Input Message" inserted text => "Enter the unique number"
  • In the "Error Alert" inserted text => "You have entered a number that already exists in the table!"

To create these conditions and set the Data Validation steps: select all the cells you want to apply Excel Data Validation feature. Open the Data Validation dialog box and enter the above formula and the necessary texts.

Prohibition, duplicate entries in the Excel spreadsheet and allowing entry only unique values

Data Validation and condition following a number that must be greater than the previous number

In the following example Data Validation we have one condition. Our goal is to limit the user to fulfill a requirement when entering the number in the cell that is in the range of cells 'A2:A5'. The condition is the following code, which must be greater than the previous one.
  • On the "Settings" to "Custom" item in the "Source" field is set formula =A2>A1
  • In the "Input Message" inserted text => "Enter the following value or number"
  • In the "Error Alert" inserted text => "You have entered a number that is smaller from the last above"

To create these conditions and set the Data Validation steps: Select the cell 'A2:A5' to which you want to apply Excel Data Validation feature. Open the Data Validation dialog box and enter the above formula and the necessary texts.

How conditional enrollment, the next larger number in the Excel column

Data Validation and how to condition only enter text in the Excel cells

In the following example Data Validation, we have one condition. Our goal is to limit the user to fulfill a requirement when entering text in a cell that is located in the a range of cells 'A1:A5'. The condition is the only text in the cell.
  • On the "Settings" to "Custom" item in the "Source" field is set formula =ISTEXT(A1)
  • In the "Input Message" inserted text => "Enter text"
  • In the "Error Alert" inserted text => "The cell is only allowed enrollment text"

To create these conditions and set the Data Validation steps: Select the cells you want to apply Excel Data Validation feature. Open the Data Validation dialog box and enter the above formula and the necessary.

How to restrict users to only be entered text in a cell

Data Validation and how to prevent or allow input values in Excel cell provided

In the following example Data Validation, we have one condition and that is that adjacent cell is not empty. So in the next cell in column 'A' must contain a value to Excel allowed input values in column 'B'. Our goal is to limit the user to enter a value in the cell column 'B' when the condition in one of the cells.
You notice that I am here and use Conditional Formatting to us visually indicates if allowed entry into the cell. If the cell is green background then is allowed to enter a value, if the cell is red background was not allowed to enter a value in a cell. Of course, you do not have to set conditionality using Conditional Formatting.
  • On the "Settings" to "Custom" item in the "Source" field is set formula =NOT(ISBLANK($A1:$A$2))
  • In the "Input Message" inserted text => "Enter a value"
  • In the "Error Alert" inserted text => "You can not enter a value, because the cell on the left side is empty"
To have created such conditions set Data Validation steps: Select the cells you want to apply Excel Data Validation feature. Open the Data Validation dialog box and enter the above formula and the necessary
If you want to use Conditional Formatting then the red cells in which he denied entry values set the formula =ISBLANK($A1:$A$2)
For cells with green background in which the user is allowed to enter, because the condition match, set the following formula =NOT(ISBLANK($A1:$A$2))

How to prohibit the entry value in the the cell if another Excel cell is empty

How to clean, delete, erase or remove more Data Validation on one worksheet in a single action

To remove all Data Validation settings on a worksheet, do the following. On the ribbon, click Home tab and then the Find&Select button and then the command "Go To Special ...". At the newly opened dialog window check the radio button Data Validation => and then check the radio button All and then click on the OK Button.

How to delete, clean, erase or remove all Data Validation messages in the Excel worksheet

Now you have selected all the cells that contain the Data Validation settings. On the ribbon select Data Tab => then click the Data Validation command and you will see a new pop-up warning like this in the picture below "Erase current settings and continue?". To confirm the deletion of all Data Validation settings on the worksheet, click the OK button.

Erase current settings and continue in Data Validation to all selected cells of active Worksheet


Removing all Data Validation settings in the active Workbook via VBA macro

In as much in the active workbook, you have to set up multiple worksheets Data Validation, and you want in a single step to delete or remove or clear all set Data Validation, you can do it using VBA macro.
Copy VBA code from this web site which is located below, and Paste to general Module1 to your Excel VBE.

Position the cursor on some active worksheet

Start a VBA macro via ALT+F8 key combination or through VBA button. The newly-open dialog box select the name of the VBA procedures in the field "Macros in" select "This Workbook". Then click the Run button. VBA macro will remove all Data Validation, which were placed in a workbook at all worksheets.

Sub RemoveDataValidation()
'removing of all set Data Validation in the workbook to all worksheets
Dim ws As Worksheet 'declare Sheet
For Each ws In Worksheets 'for each Sheet
ws.Cells.Validation.Delete 'delete Data Validation on the declared Sheet
Next ws 'next Sheet
End Sub  
 
Delete All Data Validation by VBA in the active Excel workbook