When working in Google Sheets, it is important that data is entered accurately and consistently. One way to ensure error-free data is to create validation rules.
Validation rules make certain that the data complies with specific conditions as it is entered. These rules can be about having data that conform to an approved list of entries, a set length of the entry, time or date range, and more.
It is important to note that validation rules:
work best when users type data directly into cells
are applied to the cells, not the content in the cells
can be overwritten by copying and pasting data into the cells or using the autofill handle to enter data
Copying the practice workbook
If you are beginning this course at this point, you will need to copy the practice workbook to your Google Drive.
To begin copying the practice workbook in a new browser window,
The file begins to open in a new window of the web browser. Before the document will open and can be edited, we need to make a copy of it.
Move to the new browser window.
To copy the workbook file, in the browser window,
Click
A copy of the file is now open and, by default, is saved in your Google Drive. The filename now has the words "Copy of" at the beginning.
The workbook contains a single worksheet named EmployeeInformation. This is a list of employees including ID numbers, last and first names, hire dates, current salaries, regions, and addresses.
Creating a list validation rule
Let's create a list validation rule. List validation allows users to choose data from a drop-down list. In this case, we want to create a list validation rule that will make sure each entry in the Region column is one of four valid entries: North, South, East, or West.
Validation rules are applied to the selected cells. Therefore, we will begin by selecting the Region column.
To select the Region column,
Click
To open the Data validation rules panel, on the menu bar,
Click Data,Click Data validation
The Data validation rules panel opens:
Let's begin adding a rule.
To add a data validation rule, in the Data validation rules panel,
Click
The Data validation rules panel changes and now shows us several options:
The Apply to range field shows us the selected range, EmployeedInformation!F1:F1000. This indicates that the validation rule will be applied to cells F1:F1000 in the EmployeeInformation worksheet.
Under the Criteria heading, Google Sheets has assumed that we want to create a Dropdown validation rule. This is correct. This type of validation rule will give users a pre-set list of possible data entries.
Under the Dropdown selection, Google Sheets has listed all of the data that is currently in column F: Region, Central, West, East, South, and North. We can add or remove items.
Column F contains the Region field. The four possible regions are: North, South, East, and West. Region is the column heading and Central isn't a valid region. Let's remove those.
To remove Region, in the Data validation rules panel, to the right of Region,
Click
To remove Central, in the Data validation rules panel, to the right of Central,
Click
With correct options in place, let's make more choices about the data validation rule. We want invalid data to be rejected. We would also like Google Sheets to give users information about correct data. These options are under the Advanced options heading. Let's view the Advanced options.
To view the Advanced options, in the Data validation rules panel,
Click
To have Google Sheets offer help text,
Click the Show help text for a selected cell checkbox
To have Google Sheets reject invalid data, under "If the data is invalid,"
Click the Reject input radio button
To change the appearance of the validated cells, in the Data validation rules panel, under Display style,
Click the Arrow radio button
To accept these changes, in the Data validation rules panel,
Click
The Data validation rules panel now shows us that there is a data validation rule applied to column F.
Each cell in the column contains a drop-down list. And the cells that contain invalid data have small, red triangles in the upper right corner.
Before we take a closer look, let's deselect and widen the column.
To deselect the column,
Click in a blank area of the worksheet
To widen the column, if necessary,
Double-Click
Column F is now wide enough to see the contents of each cell.
Testing a data validation rule
Now that the validation rule has been set, let's experiment with how it works. We'll add an entry for a new employee in row 47.
When we reach cell F47, let's test the data validation rule and see what happens when we attempt to enter invalid data.
To enter invalid data in cell F47, type:
Central Tab key
When we attempt to move from cell F47, a warning appears:
Let's retry the data entry. This time we'll use the drop-down list.
To close the warning,
Click
Return to cell F47.
To see the options,
Click
To set the region to North,
Click North
To move to the next cell, on the keyboard, press:
Tab key
To enter the address, in cell G47, type:
9066 Highland Road, Lake Villa, IL, 60046
The new employee information is complete.
Correcting invalid data
In Google Sheets, data validation rules are applied to all of the data in the selected cells. If there is previously entered data that does not meet the requirements, this data can then be manually changed.
In the EmployeeInformation worksheet, cells F1, F2, F23, and F36 have been marked as containing invalid data. Cells F2, F23, and F36 have Central listed as the region. This isn't on the list of regions. Cell F1 is the column heading. Because the data validation rule was applied to all of column F, this data is marked as invalid even though it is correct.
If we hover our cursor over the cell, we will see the warning.
To correct the invalid data, in cell F2,
Click , Click East
To correct the invalid data, in cell F23,
Click in cell F23, type: North
To correct the invalid data, in cell F36,
Click in cell F36, type: North
Removing a data validation rule
Cell F1 has been marked as invalid although Region is the correct column heading. While the small, red triangle isn't prohibiting the use of the data, it may be distracting or other wise annoying. We can remove the data validation rule from this single cell.
To open the Data validation rules panel, in cell F1,
Click, Click
The Data validation rules panel opens. Any changes we make now will be applied only to the active cell: EmployeeInformation!F1.
To remove the data validation rule from cell F1, in the dialog box,
Click
To close the Data validation rules panel,
Click
Deselect the cell.
Cell F1 is no longer flagged as invalid and the data validation remains in the rest of column F.
, Click 
, Click 
