10 Data Validation Rules & Techniques – Excel & Google Sheets

Written by

Editorial Team

Reviewed by

Laura Tsitlidze

Last updated on June 30, 2023

Data Validation is a tool in Excel that lets you restrict which entries are valid in a cell. Here are 10 rules and techniques to help you make the most out of Data Validation and its many features.

  1. Use Drop-Down Lists
  2. Data Validation Based on Another Cell
  3. ToolTips and Error Messages
  4. Options and Settings
  5. Date and Time Formats
  6. Set a Character Limit
  7. Validate Phone Numbers and Emails
  8. Custom Formulas
  9. Change or Remove Data Validation Rules
  10. Issues and Fixes


limit restrict cell values 3


The most useful type of data validation is the drop-down list feature. For instructions on creating a drop-down list, see Create or Add a Drop-Down List in Excel and Google Sheets.


DDList intro


There are plenty of uses for drop-down lists and many ways to create them. For example, a simple yes/no drop-down list like the one below can be added as a quick and dependable indicator in a spreadsheet.


YesNo Intro


You can also use VBA to create a drop-down list in a cell. Enter this code in the VBA editor:

Sub DropDownListinVBA()
Range("A2").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
End Sub

Dynamic Drop-Down Lists


dynamicdropdown intro


  • Go a step further with a cascading drop-down list based on the value that is selected in a different list. In the example below, the user has selected LG as the Phone Make. The list of Phone Models then shows only the LG models. If the user were to select either Apple or Samsung, then the list would change to show the relevant models for those makes of phone.





drop down list if statement Main


Filter Data With a Drop-Down List

You can use Excel’s filter tool alongside a drop-down list for extended functionality.

  • Filters and drop-down lists are similar and even use the same keyboard shortcut (CTRL + SHIFT + L). In a data validation context, the shortcut activates a drop-down menu.


Toggle Autofilter



filter intro


Drop-Down List Formatting and Display Tools


DropDown Intro


  • When you create a drop-down list, the default value of the cell where you have placed the list is usually blank. It can be useful to pre-populate this cell with either a default value or a message to the user, such as Select…


DD Default SelectList



DropDownSort Intro



drop down subcategories final data


AutoComplete Drop Down

AutoComplete is a useful feature of Excel in that it can reduce the number of characters you need to type in each cell. The standard data validation drop-down functionality does not support AutoComplete. However, you can learn here how to approximate autocomplete with a drop-down list. Then, when you start typing the first letters of an entry in the drop-down list, the rest of the entry automatically shows up.


AutoComplete intro


Drop Down Populates Another Cell

  • Selecting an item from a drop down can populate a different cell in Excel and Google Sheets. For example, in the graphic below, Patel is selected from the list, and the cell below is then populated with the value 34. You can learn how to do this by clicking here.


DropDownPopulates intro



ChangePicture Int


Data Validation Based on Another Cell

When entering values in Excel, you can restrict the data that is entered into any range of cells based on text or a value held in another cell.


DataValidation Example


ToolTips and Error Messages

  • When you create a drop-down list, you can write a custom input message that is seen on the screen when a cell is selected. This input message can also be referred to as a ToolTip.


input message data validation 4a


  • You can also customize the output (error alert) message that is returned to the user if invalid data is entered into a cell.


enable error alert data validation final


Options and Settings

  • There are seven different data types you can select from the Allow box of the Validation criteria: Whole number, Decimal, List, Date, Time, Text length, and Custom.
  • The Ignore blank option is only available if you are actually editing a cell that has that option switched off.


ignoreblanks checked


  • You can also choose, when editing a data validation rule, whether or not to apply the changes to other cells with the same settings.

Date and Time Formats

You can use Data Validation to restrict the format entered into a cell to ensure it is a date, and also to restrict the entry to be set between a start date and an end date.


DatavalidationDates intro


Set a Character Limit

In Excel, the number of characters allowed in a single cell is 32767. However, you can set your own character limit for a text cell.


set a character limit data validation final


Validate Phone Numbers and Emails


validate phone numbers final data


  • You can also use Data Validation to ensure that an email address contains a @ sign and does not have any spaces or commas in it.


EmailDataValid Intro


Custom Formulas

You can write a custom formula to make your own data validation rules. In the example below, the formula ensures that the data in a cell begins with certain text.


data validation must begin with


Custom Formula Examples

  • You can use the INDIRECT Function in Excel in conjunction with a data validation drop-down list to create a cell reference from text.


INDIRECT Main Function


  • Use the ISNUMBER Function to check that the data inputted is in fact, a number. Data Validation has a built-in Whole number option, but you can generalize further with a custom formula.


data validation numbers


  • Use a the ISTEXT Function to check for text. The example below allows only text values in the cells with data validation applied.


Validate Data Validation



mega article drop down



prevent duplicate entries data validation final


  • A risk score matrix is a matrix that is used during risk assessment in order to calculate a risk value by inputting the likelihood and consequence of an event. You can use a data validation drop down in conjunction with the VLOOKUP and MATCH Functions to create a risk score matrix.

Change or Remove Data Validation Rules


restricted values data validation



DataValidationCopy intro


  • If you already have rules set up in a worksheet, you can amend the validation rules should your requirements change.


change data validation


  • You can amend the contents of a drop-down list by following the instructions in this tutorial.


dropdown intro


  • You can easily update (change, extend, or have fewer items) a drop-down list by adding or deleting items from the list’s source range.


update drop down list 1


  • If data validation on a range is no longer required, you can remove it.


datavalidation remove cleared


  • If you no longer require a drop-down list, you can easily remove the data validation settings by following the instructions in this tutorial.


RemoveValidation Intro


Issues and Fixes

  • If you have a value in Google Sheets that does not match the items in your drop-down list (an invalid item), you get a red triangle in the top left corner of the cell. To get rid of this triangle, you can follow the steps in this tutorial.


red triangle visible


  • There are a number of reasons why your data validation rule or drop-down list is not working correctly. You can troubleshoot these issues by referring to this tutorial.


data-validation error drop down


  • A mismatch error can occur when you run VBA code. The error stops your code from running completely and flags the error by means of a message box. One of the ways you can prevent this from happening is to use Data Validation on the spreadsheet to prevent the user from causing worksheet errors in the first place. Only allow them to enter values that don’t cause worksheet errors.


PIC 01

AI Formula Generator

Try for Free

See all How-To Articles