Data validation is a process that helps to maintain the integrity of data by ensuring inputted values satisfy all conditions. Without it, data can quickly get out of hand, and a worksheet can become ridden with erroneous data that makes it unmanageable.
It is especially important to do this with collaborative projects. Some people are more lax than others with data input, so constraints can help to maintain high levels of accuracy and consistency.
Google Sheets provides several forms of data validation to ensure that only permissible values are accepted:
- cell value checks
- dropdown lists
- checkboxes
- date pickers
There are also data types that can be applied to a cell. This is so different kinds of values are catered for, such as numbers, text and dates.
This article explains all the available options and how they can be used, so you are fully equipped to ensure your worksheets are robust and error-free.
Example Workbook
Download the Sheets file containing all the examples:
Most of the examples are based on sample data found at Free Sample Data.
Data Validation Window
To access the data validation options, go to Data ➜ Data validation.
The sections are:
Cell range
This is the cell range you want to validate. By default, the cells in the active range are what the validation will be applied to.
Criteria
Choose how you want to validate the range.
Choices include:
- List from a range
- List of items
- Number
- Text
- Date
- Custom formula is
- Tick box
Each of these is explained in the next section.
On invalid data
On invalid data determines what should happen when a cell contains an invalid value. The following appear for all Criteria options.
There are two choices:
- Show warning
Accepts the input but displays a red warning triangle in the cell. When you hover over this, a message displays alerting you of the invalid value.
- Reject input
This rejects the input and displays an overlay window with a standard message informing you of the invalid entry.
For the sake of convenience, you will only see the Reject Input option demonstrated once in the workbook, as the rest of the examples use Show warning.
Appearance
Ticking Show validation help text allows you to customise the message that appears when invalid data is inputted. This only works when the Reject input option is selected.
Removing Validation
To remove the validation from an active cell or range, access the data validation window and press Remove validation.
Adding and Sorting Items
Add a new item to the bottom of the existing list for it to be included in the dropdown range. You must ensure the input field range covers the new cell, as it will not update automatically when you add a new entry.
However, by using a range like A5:A, the whole of the A column minus the first three rows is covered in the range, preventing the need to update it in the future.
Dropdowns are also clever enough not to include duplicate items. Even if you include large gaps between the entries, it will not mess anything up.
When referencing a list from another worksheet, it is necessary to include the sheet name inside single quote marks followed by an exclamation mark followed the cell reference.
'List from a range'!A4:A14The above example references the range A4:A14 in the List from a range worksheet.
You will rarely need to do this manually, as it is an automatic process when selecting a range, but it is worth being aware that a standard reference will always refer to the active worksheet.
As an alternative to cell references, you can give your lists meaningful names by saving them as named ranges.
In the menu bar, go to Data ➜ Named ranges and click on Add a range. Choose the name, reference the list and press Done. Simply state its name after the equals sign (=) to use it in a formula.
If your list is unsorted, you may want to sort it to make it easier to find the item you are looking for. Do this by nesting the reference of the original list inside the SORT function. This will spill the items down in ascending order. If you want descending order, you have to set the sort_column and is_ascending arguments to 1 and FALSE, respectively.
Absolute References
If you are copying cells with data validation and want to avoid references from shifting as you drag the fill handle down, it is good practice to lock the cell references by making them absolute. Do this by placing a dollar sign ($) before the column letter and row number. For example, $A$4:$A$14.
You can only use the F4 key to change between relative and absolute if you are working in the formula bar. It will not work in the Data validation window.
Not doing so can lead to unintended results, such as a dropdown not including all the items it should. In the example shown, the relative reference of the last dropdown is A14:A24 instead of the $A$4:$A$14 it needs to be.
Number Formats
If you are familiar with Excel, you will probably know you can apply data types to cells to properly represent the contents. Custom formatting is also an option, but this only affects the visual output of the value and has no impact on the actual result.
One of the downsides of data types is that they do not always work as intended. A string composed of numbers deliberately stored as text can suddenly become a number unknowingly. This occurs when editing the cell and reconfirming the value.
Sheets is better at handling custom cell formats. Unlike Excel’s, their behaviour is less erratic. Data types affect validation as well, so choosing the right one is important.
All the cell formats are found in the Format menu ➜ Numbers. Additional options can be found in the More formats submenu.
The types are:
- Automatic – automatically assigns the data type based on the value given.
- Plain text – value appears exactly how it was entered.
- Number – a numeric value displayed to two decimal places for mathematical calculations.
- Percent – a numeric value formatted as a percentage.
- Scientific – a numeric value formatted in scientific notation.
- Accounting – a monetary value displayed to two decimal places and formatted with the currency symbol (based on your location) on the far left. Negative figures are displayed in brackets.
- Financial – a monetary value to two decimal places without a currency symbol. Negative figures are displayed in brackets.
- Currency – a monetary value to two decimal places with a currency symbol. Negative figures are preceded by a minus sign.
- Currency (rounded) – a rounded monetary value with a currency symbol. Negative figures are preceded by a minus sign.
- Date – a date value in the format dd/mm/yyyy (dependent on location).
- Time – a time value in the format hh:mm:ss.
- Date time – a date and time value in the format dd/mm/yyyy hh:mm:ss.
- Duration – a duration value in the format hh:mm:ss.
- More Formats
- More Currencies – opens a window containing a full list of currencies you can use. Whichever ones you have selected previously will appear in the More Formats dropdown.
- More date and time formats – opens a window containing a list of pre-defined date and time formats. Customised ones can be created as well.
- Custom number format – opens a window containing a list of pre-defined number formats. Customised ones can be created as well.
In the dropdown space above More Formats, the last three formats used will show up, allowing for easy access.
By default, the numeric formats display to two decimal places. To change this, use the Increase decimal places and Decrease decimal places toolbar buttons.
Most of the time there is not a reason to set specific data types because Sheets does a good job of setting them automatically. However, sometimes you will need to because data can get misinterpreted. For example, setting a cell containing a telephone number as Plain text is prudent because they often start with 0, but are mistaken for numeric values. This leads to the first character getting chopped off.
Excel will not immediately change the data type of a cell when you select a different one, whereas Sheets will. If you are ever in any doubt about what Sheets is storing as a value, it is worth doing a quick check.
To test whether a cell is indeed a number, use the ISNUMBER function. For text, use ISTEXT. Each will produce a TRUE or FALSE result.
Keyboard Shortcuts
You do not have to use the mouse to interact with dropdowns. Sometimes it is more convenient to use the keyboard.
- Using the directional keys, navigate to the cell containing the dropdown.
- Press Enter to display the choices.
- Highlight the desired item and press Enter to select it.
Examples
A worksheet is present for each option in the Criteria dropdown.
List From a Range
If you want to restrict the values a user can select, this is ideal as you can choose a range of values to form a list. It is best suited to categorical data.
In the example, a list of counties makes up the source data, so A4:A14 is stated as the Criteria range.
Checking Show drop-down list in cell ensures the dropdown feature is available in the cell. Without it, the same criteria rules apply, but you must type a permitted value, as you do not get the convenience of selecting a choice.
Including a Blank Option
Often, you might want a blank item in your dropdown to indicate that nothing has been selected. Sheets does not make this particularly straightforward, as you cannot just include an empty cell at the top of your range – it will get ignored.
The easiest way is to go to a website like Empty Character, press the Copy to clipboard button in Method 1, and paste it into the blank cell in the range.
List of Items Criteria
Instead of referencing a range, the names of the items can be stored in the data validation window.
Each name must be separated by a comma. To precede the items with a blank option, use a double comma (,,).
Number Criteria
There are several ways to validate a numeric value:
- between – value must be between (and including) x and y.
- not between – value must not be between (and including) x and y.
- less than – value must be less than x.
- less than or equal to – value must be less than or equal to x.
- greater than – value must be greater than x.
- greater than or equal to – value must be greater than or equal to x.
- equal – value must be equal to x.
- not equal to – value must not be equal to x.
If any non-numeric characters are found in a cell – mixed with numbers or not – it is automatically deemed invalid.
Text Criteria
The options for validating a text value are:
- contains – accepts values containing any instance of the specified string.
- does not contain – prohibits values containing any instance of the specified string.
- equals – value must match the specified string.
- is a valid email – value must be an email address in the format [name]@[host].[suffix].
- is a valid URL – value must be a web address. The minimum requirement is the address includes the domain name. For example, howtoexcel.com would be acceptable. It does not require the https://www part.
Valid and invalid text inputs are shown in the example.
Date Criteria
The options for validating a date value are:
- is a valid date – date must be in the format dd/mm/yyyy.
- equal to – date must be the same as x.
- before – date must be older than x.
- on or before – date must be the same or older than x.
- after – date must be more recent than x.
- on or after – date must be the same or newer than x.
- between – date must be between (and including) x and y.
- not between – date must not be between (and including) x and y.
Any cell that has a date will automatically be assigned the Date format. If you double-click on it, a date picker will appear, providing a convenient way of selecting a day.
Custom Formula Is Criteria
Most of the time the standard data validation options will suffice, however, there will be times when you want a more bespoke solution.
Creating a custom formula gives you more flexibility, as you can create formula that return a TRUE or FALSE value. When the function evaluates to TRUE, then the validation will allow the value.
Numerous examples are shown in the Custom formula is worksheet.
Text Examples
Example 1: Only Allow Uppercase Text
= EXACT ( A7, UPPER (A7) )EXACT – Docs Editors Help (google.com)
UPPER function – Docs Editors Help (google.com)
Any text string is allowed, however, all letters must be uppercase.
The EXACT function is case sensitive and compares two strings to see whether they are identical.
Cell A7 is the first postcode and the second feeds the same cell into the UPPER function. This function is responsible for coercing the postcode to uppercase. Comparing the value of A7 to an uppercase version of it returns TRUE if A7 is uppercase, or FALSE if there are any lowercase letters found.
Example 2: Only Allow Text Starting with DA
= COUNTIF ( C7, "DA*" )COUNTIF – Docs Editors Help (google.com)
All postcodes must start with DA to be valid.
The COUNTIF function evaluates each cell and checks for instances of “DA*“. The asterisk represents a wildcard, meaning any characters that appear after the letters are included in the count. As only values that start with DA are valid, there is no preceding asterisk, otherwise, this would include entries that feature DA anywhere.
It should be noted that this method is not case-sensitive, so strings starting with da or Da are treated the same as ones with DA.
Example 3: Only Allow Text Starting with DA [Case Sensitive Version]
= EXACT ( LEFT( E7, 2 ),"DA" )EXACT – Docs Editors Help (google.com)
LEFT – Docs Editors Help (google.com)
If you want the input to be case sensitive, a different formula is required. The solution is to wrap the EXACT function around LEFT to extract the first two characters, and then compare those to the “DA” string.
Example 4: Restrict Text Length [8 Characters or Less]
= LEN ( A20 ) < 9LEN – Docs Editors Help (google.com)
The postcodes are restricted to a maximum of eight characters only. The LEN function holds the cell reference and returns its character length. If it is less than nine, then this formula evaluates to TRUE.
Example 5: Only Allow Text Values
= ISTEXT ( C20 )ISTEXT – Docs Editors Help (google.com)
Only text is permitted in this list, and this is achieved with the ISTEXT function.
Example 6: Must Contain Given Keywords with OR Criteria
= COUNTIF ( E20, "*Park*" ) + COUNTIF ( E20, "*Ward*" )COUNTIF – Docs Editors Help (google.com)
This table only allows towns and cities containing Park or Ward.
Whereas the standard text validation options are limited to a single criterion, a formula allows separate text strings.
Searching for one string or another is achieved by adding together COUNTIF statements, as the plus symbol (+) represents OR logic. TRUE is returned if either statement finds a match because the number will always be above 0. If it is not, then FALSE is the result.
Example 7: Must Contain Given Keywords with OR Criteria
= OR ( ISNUMBER ( SEARCH ( "Park", A33 ) ), ISNUMBER ( SEARCH ( "Ward", A33 ) ) )OR function – Docs Editors Help (google.com)
ISNUMBER – Docs Editors Help (google.com)
SEARCH – Docs Editors Help (google.com)
This example yields the same results as Example 6, except the OR function encompasses two statements that look for each text string.
The SEARCH function returns the first character position of the word, and this fed into the ISNUMBER function means TRUE is returned. As a #VALUE! error occurs if a string is not found, this is not a number so FALSE is the result.
Example 8: Must Contain Given Keywords with AND Criteria
= COUNTIF ( C33, "*West*" ) * COUNTIF ( C33, "*Ward*" )COUNTIF – Docs Editors Help (google.com)
Whereas Example 7 used the plus symbol (+) to check if one string or another appeared, this one uses an asterisk for AND logic by multiplying each COUNTIF statement. As multiplying anything by zero returns zero, both COUNTIFs have to find each string for TRUE to occur.
Example 9: Must Contain Given Keywords with AND Criteria
= AND ( ISNUMBER ( SEARCH ( "West", E33 ) ), ISNUMBER ( SEARCH ( "Ward", E33 ) ) )AND function – Docs Editors Help (google.com)
ISNUMBER – Docs Editors Help (google.com)
SEARCH – Docs Editors Help (google.com)
This method is similar to Example 7, except both the West and Ward strings must be found as the AND function is used instead.
Example 10: Must Contain a Given Keyword [Case Sensitive Version]
= ISNUMBER ( FIND ( "ham", A46 ) )ISNUMBER – Docs Editors Help (google.com)
FIND function – Docs Editors Help (google.com)
Unlike the SEARCH function, FIND is case-sensitive, so in this list only values containing ham are deemed valid. As already mentioned, the ISNUMBER function returns TRUE as long as the FIND statement successfully produces the first character position of the string.
Example 11: Prohibit a Particular Character
= C46 = SUBSTITUTE ( C46, " ", "" )SUBSTITUTE – Docs Editors Help (google.com)
The SUBSTITUTE function swaps one string for another, and here it has been used to remove any spaces. Comparing the result of this to the original value returns TRUE if there are not any spaces and FALSE if there are.
Example 12: Prohibit Leading and Trailing Spaces
= E46 = TRIM ( E46 )TRIM function – Docs Editors Help (google.com)
By comparing a cell value to the same one put through the TRIM function, it can be established if there are any leading or trailing spaces. If there are, then FALSE is the result.
Example 13: Only Allow Proper Case Text
= EXACT ( A59, PROPER ( A59 ) )EXACT – Docs Editors Help (google.com)
PROPER – Docs Editors Help (google.com)
Checking whether a string is in proper case can be achieved using the EXACT function to compare it to itself via the PROPER function.
Example 14: Format X/YYYYY
= AND ( LEN ( C59 ) = 7,
CODE( LEFT ( C59 , 1 ) ) >= 65, CODE ( LEFT ( C59, 1 ) ) <= 68,
MID ( C59 , 2, 1 ) = "/",
CODE ( MID( C59, 3, 1 ) ) >= 48, CODE ( MID ( C59, 3, 1 ) ) <= 57,
CODE ( MID( C59, 4, 1 ) ) >= 48, CODE ( MID ( C59, 4, 1 ) ) <= 57,
CODE ( MID( C59, 5, 1 ) ) >= 48, CODE ( MID ( C59, 5, 1 ) ) <= 57,
CODE ( MID( C59, 6, 1 ) ) >= 48, CODE ( MID ( C59, 6, 1 ) ) <= 57,
CODE ( MID( C59, 7, 1 ) ) >= 48, CODE ( MID ( C59, 7, 1 ) ) <= 57
)- AND function – Docs Editors Help (google.com)
- LEN – Docs Editors Help (google.com)
- CODE – Docs Editors Help (google.com)
- LEFT – Docs Editors Help (google.com)
- MID – Docs Editors Help (google.com)
This example features a set of employee IDs in the format X/YYYYY:
- X is a letter from A–D and refers to the employee’s department.
- Y is a random number from 0–9 and forms the rest of the employee’s ID.
Stringent requirements are imposed on the user’s input:
- length must be seven characters.
- department letter must only be A, B, C or D.
- a slash (/) must be the second character.
- only numbers 0–9 are permitted for the last five characters.
All of this is achieved using an AND statement to separate each condition, LEN to specify the string length and a combination of CODE, LEFT or MID to extract each character. Then the character’s code number is checked to see whether it falls into the boundaries of the permitted ones. If it does, TRUE is the result, and this is what all conditions need to be.
Numeric Examples
Example 15: A Greater Than B
= H7 > I7For a valid figure to stand in the first column, it requires that its corresponding value for A is greater than B.
Example 16: Only Allow Numbers
= ISNUMBER ( G20 )ISNUMBER – Docs Editors Help (google.com)
To restrict valid input to numeric values only, use the ISNUMBER function.
Example 17: Only Allow Unique Numbers
= ISNUMBER ( G33 ) * ( COUNTIF ( $G$33:$G$42, G33 ) = 1 ) = 1ISNUMBER – Docs Editors Help (google.com)
COUNTIF – Docs Editors Help (google.com)
Preventing duplicate numbers can be achieved using a combination of ISNUMBER and COUNTIF.
ISNUMBER checks for a number, whilst COUNTIF features a range argument that contains the list, with the criterion housing the cell reference.
To check that the value does only appear once in the range, it is necessary to affix =1 to the COUNTIF so TRUE or FALSE is returned. Another =1 is also added to the end of the formula to verify both conditions are TRUE.
Date and Time Examples
Example 18: Only Allow Weekday Dates
= WEEKDAY ( K8, 2 ) <= 5WEEKDAY – Docs Editors Help (google.com)
Perhaps you have a list of dates but only want ones that are weekdays. For this scenario, use the WEEKDAY function.
The value of K8 has been passed into the date parameter and [type] set as 2, which means the starting day is Monday (1 is Sunday). Checking if this date is less than or equal to 5 (the fifth day from the start) returns TRUE when the date is a weekday.
Example 19: Only Allow Weekends Dates
= WEEKDAY ( N8, 2 ) > 5WEEKDAY – Docs Editors Help (google.com)
To only allow dates that are weekends, the same formula is used as the previous example, except it looks for numbers that are greater than 5, as 6 and 7 represent Saturday and Sunday respectively.
Example 20: Date Greater Than Current Date
= K21 > NOW ( )NOW – Docs Editors Help (google.com)
The default output of the NOW function is date and time, but you can adjust this by clicking on the More formats button in the toolbar and changing it to Date.
Example 21: Date and Time Greater Than Current Date and Time
= N21 > NOW ( )NOW – Docs Editors Help (google.com)
Following on from Example 20, if you want the date as well, just use the NOW function on its own.
Example 22: Time Greater Than Current Time
= K34 > TIME ( HOUR ( NOW ( ) ), MINUTE ( NOW ( ) ), SECOND ( NOW ( ) ) )TIME – Docs Editors Help (google.com)
NOW – Docs Editors Help (google.com)
HOUR – Docs Editors Help (google.com)
MINUTE – Docs Editors Help (google.com)
SECOND – Docs Editors Help (google.com)
Only times that are later than the current time are permitted in this table.
With the > operator sandwiched between the cell reference and current time, a comparison can be made. To retrieve the latest time, the TIME function uses the HOUR, MINUTE and SECOND functions – with NOW nested in each.
The default behaviour for NOW is the current time only updates when the worksheet does. You can change this by going to File ➜ Spreadsheet Settings ➜ Calculation and choosing another option in the Recalculation dropdown. Your alternatives are: On change and every minute and On change and every hour.
Other Examples
Example 23: Custom TRUE or FALSE Values
= IF ( R9 = $T$6, TRUE )IF function – Docs Editors Help (google.com)
You can customise the TRUE and FALSE values with symbols of your choice. This formula checks whether a tick (TRUE) or cross (FALSE) is present to determine which names are valid. These symbols have been taken from fsymbols.com.
It is not possible to copy and paste symbols into the Criteria textbox in the data validation window. That is why you need to house them in cells and ensure any reference to them is made absolute.
Tick box
A tick box determines whether the cell value is TRUE (checked) or FALSE (unchecked).
They are treated in the same way as text, so you can resize them with Font Size and change their colour with Text color.
Checking the Use custom cell values option allows you to customise the output of the ticked and unticked states with your own values.
In the first custom example, the TRUE value is 1 and the FALSE is 0.
It is often easier to use numbers because you can use a simple SUM formula instead of COUNTIF for collecting the total. Either way will work for numbers, but for text you must use COUNTIF.
Dependent Dropdowns
When you have many items in a list, sometimes it is better to break them down into categories so the dropdowns aren’t overloaded with items.
This is where dependent dropdowns come in handy. You select a value in one dropdown and the values available in another dropdown dynamically updates accordingly.
In this example, there is a list of counties and towns/cities.
As some of the towns/cities are situated in the same county, it makes sense to use the county as the independent dropdown and the town/city as the dependent one.
There is an annoying behaviour where updating the independent dropdown does not change the displayed value of the dependent one based on the last selection. This means you are left with an invalid value until you access the dropdown to choose a new item. None of this has any bearing on the actual validation, however, as the previously selected value will still be adjudged to be invalid.
The way to resolve this is to use Google Apps Script to run a script that triggers a reset.
To see where the code is stored, go to Tools ➜ Script editor.
This is the code snippet:
//Reset selection in dependent dropdown
function onEdit(e) {
var row = e.range.getRow();
var col = e.range.getColumn();
if (row === 7 && col === 5 && e.source.getActiveSheet().getName() === "Dependent Dropdowns" ) {
e.source.getActiveSheet().getRange(row,col+1).setValue('');
}
}
The code starts with the function name onEdit(e). This is what is called a simple trigger. These are functions that run automatically when a certain event is executed.
Passed to the function is (e), which is an event object that contains information about the causation of the triggered event.
In this case, it is used by the row and col variables to retrieve the row and column number of the cell edited. If these equal 7 and 5 respectively, as well as being in the Dependent Dropdown worksheet (location of the first dropdown), the cell that is one column to the right (the second dropdown) returns an empty string.
Conditional Formatting
Although it has no effect on data validation, conditional formatting can complement it by providing enhanced visual indication. It can make it a lot easier to see the invalid values on a worksheet, rather than relying on the tiny red prompts in the top right of a cell.
To access the conditional formatting window, go to Format ➜ Conditional Formatting. This opens a panel on the right with an Add another rule button.
Many of the rule options are the same as those found in the data validation window.
You will have to use conditional formatting that is the opposite of the data validation rule to fill an invalid cell. For instance, the Between 1 and 10 example has a rule that fills the cell with red if the value is not between 1 and 10.
A separate rule has also been made for the valid cells, with green as the fill colour.
Conclusion
Hopefully, now you are fully clued up on the different forms of data validation and when to use them.
It is always important to challenge your existing practices. Were you already using data validation? If so, were you using it effectively?
At a basic level, the default data validation settings are sufficient. However, they only allow for one condition at a time and you cannot combine them with custom formulas. Multiple conditions are only possible with the Custom formula is option.
Sometimes you will want bespoke and complex rules. As the custom examples showed, there are a plethora of ways to validate a cell. You saw how the AND and OR functions can be used in formulas to allow more complex validation.
How stringent the validation you employ depends on the data you are dealing with. If you are less concerned about a user entering something invalid, opt just for a warning flag. If it is more important, then choose the reject option.
Also, consider the advantages of mixing data validation with conditional formatting. From a UX perspective, visibility matters, so make those unwanted values stand out!
0 Comments