Don’t forget to share this post

Google Sheets is a powerful and flexible tool that is frequently used collaboratively. When multiple users input data, mistakes are quite likely: incorrect data types or format, misspelled items, or other typos. Fortunately, adding data validation rules to your spreadsheets is easy in Google Sheets. You can create anything from simple rules restricting input to texts of a particular length or create more complex rules, like dependent drop-down lists.

In this guide, you have an overview of the options available regarding data validation in Google Sheets. You have step-by-step instructions on creating, copying, editing, and removing data validation rules in Google Sheets. You will also learn how to use data validation to create a drop-down list and restrict input to a valid date format.

What is Data Validation in Google Sheets?

Data validation rules allow you to restrict the permitted input in specific cells or ranges. For example, you can restrict the data type - text, number, date - or provide a list of acceptable values. These rules can be combined with other tools, like built-in functions or conditional formatting.

How to Use Data Validation in Google Sheets?

In Google Sheets, you can add and manage your data validation rules by going to Data > Data validation.

How to Use Data Validation in Google Sheets Data Validation
How to Use Data Validation in Google Sheets - Data Validation

From the side panel, click ‘Add a rule’ to start adding data validation.

How to Use Data Validation in Google Sheets Add a Rule
How to Use Data Validation in Google Sheets - Add a Rule

Below, you will learn about the options available and how to add a simple rule: the ‘amount’ value must be greater than zero.

Cell Range

Using the “Cell range” input box, select the cell or range of cells to which you want to apply the data validation rule. Alternatively, you can select the cells before adding the rule. Below, I have selected everything but the header in column D. Click ‘OK’ to confirm your choice.

How to Use Data Validation in Google Sheets Cell Range
How to Use Data Validation in Google Sheets - Cell Range

Criteria

There are multiple options for adding criteria. Click the downward pointing arrow to select from the drop-down menu. In this case, I want the ‘greater than’ option.

How to Use Data Validation in Google Sheets Criteria Options
How to Use Data Validation in Google Sheets - Criteria Options

Type zero in the value box, as shown below. Click ‘Done’ to apply or click ‘Advanced options’ to see more options.

How to Use Data Validation in Google Sheets Greater than Value
How to Use Data Validation in Google Sheets - Greater than Value

Advanced Options

The advanced options allow you to specify what happens when invalid input is entered. First, check the box to ‘Show help text for a selected cell’. You can use the suggested message or type your own.

How to Use Data Validation in Google Sheets Show Help Text
How to Use Data Validation in Google Sheets - Show Help Text

Next, you can choose whether to show a warning or reject the input. The help text will be shown in either case. Click ‘Done’ to apply the validation rule.

How to Use Data Validation in Google Sheets Show Warning or Reject Input
How to Use Data Validation in Google Sheets - Show Warning or Reject Input
How To Share Only One Tab in Google Sheets
How To Share Only One Tab in Google Sheets

When sharing a Google Sheets spreadsheet Google usually tries to share the entire document. Here’s how to share only one tab instead.

READ MORE

How to Copy Data Validation in Google Sheets?

Copying data validation to other cells is simple. First, select the cell or cells with the data validation. Right-click on the cell and select copy or use the shortcut: Ctrl/Cmd + c (Windows/Mac).

How to Use Data Validation in Google Sheets Select Copy Cells
How to Use Data Validation in Google Sheets - Select & Copy Cells

Paste to the cell where you want to add the rule. Use ‘Paste special’ if you only want to paste the data validation.

How to Use Data Validation in Google Sheets Paste or Paste Special
How to Use Data Validation in Google Sheets - Paste or Paste Special

As you can see, the new range has been added to the existing data validation rule.

How to Use Data Validation in Google Sheets Range Added to Rule
How to Use Data Validation in Google Sheets - Range Added to Rule

How to Edit Data Validation in Google Sheets?

You can edit any aspect of your existing rules by going to Data > Data validation. From the side panel, select the rule you want to edit.

How to Use Data Validation in Google Sheets Select Rule
How to Use Data Validation in Google Sheets - Select Rule

You will see the same options as when you created the rule.

How to Use Data Validation in Google Sheets Edit Rule
How to Use Data Validation in Google Sheets - Edit Rule

How to Remove Data Validation in Google Sheets?

Removing data validation is simple. Go to Data > Data validation and hover over the rule you want to delete in the side panel. Click the trash can icon to delete.

How to Use Data Validation in Google Sheets Delete Rule
How to Use Data Validation in Google Sheets - Delete Rule

Alternatively, select the rule and click “Remove validation” to delete it.

How to Use Data Validation in Google Sheets Remove Rule
How to Use Data Validation in Google Sheets - Remove Rule

Examples of How to Use Data Validation in Google Sheets

Below, you have two examples of possible data validation rules in Google Sheets. First, you will learn how to create a drop-down list in your cells using data validation. Second, you will learn how to enforce a valid date format in your cells.

How to Password Protect a Google Sheet
How to Password-Protect a Google Sheet?

If you work with important data in Google Sheets, you probably want an extra layer of protection. Here's how you can password protect a Google Sheet

READ MORE

Example 1. Create a Drop-Down List with Data Validation

Follow the steps below to create a drop-down list with data validation in Google Sheets. To learn more about this, check out this guide on How to Add a Drop-Down List in Google Sheets.

  1. 1. Go to Data > Data Validation and click ‘Add a rule’.
How to Use Data Validation in Google Sheets Add a Rule 2
How to Use Data Validation in Google Sheets - Add a Rule
  1. 2. Select the range to which you want to apply the rule. In this case, all of column B except the header.
How to Use Data Validation in Google Sheets Select Range 2
How to Use Data Validation in Google Sheets - Select Range
  1. 3. You can choose whether to type the list items or select a range of cells containing them: Dropdown (from a range). Since I have a short list, I will use the first option and type them in directly.
How to Use Data Validation in Google Sheets Select Dropdown
How to Use Data Validation in Google Sheets - Select Dropdown
  1. 4. Add the items you want to see on the drop-down. You can assign a different color for each item.
How to Use Data Validation in Google Sheets Add Items
How to Use Data Validation in Google Sheets - Add Items
  1. 5. Click ‘Done’ to apply the rule.
How to Use Data Validation in Google Sheets New Rule
How to Use Data Validation in Google Sheets - New Rule

Example 2. Enforce Date Format with Data Validation

Follow the steps below to add a data validation rule that enforces a valid date format and adds an in-cell pop-up calendar to make it easier for users to input dates.

  1. 1. Select the cells whose input you want to restrict and go to Data > Data validation. In the side panel, click ‘Add a rule’.
How to Use Data Validation in Google Sheets Add Rule 3
How to Use Data Validation in Google Sheets - Add Rule
  1. 2. Under ‘Criteria’, select ‘Is valid date’.
How to Use Data Validation in Google Sheets Is Valid Date
How to Use Data Validation in Google Sheets - Is Valid Date
  1. 3. Click ‘Advanced options’ to choose the behavior you want upon invalid data input. Click ‘Done’ to apply.
How to Use Data Validation in Google Sheets Advanced Options
How to Use Data Validation in Google Sheets - Advanced Options
  1. 4. Double-click on any cell to see the pop-up calendar and select a date.
How to Use Data Validation in Google Sheets In Cell Pop Up Calendar
How to Use Data Validation in Google Sheets - In-Cell Pop-Up Calendar

Why is My Data Validation Not Working?

If you can’t access the data validation options from the menu, the most likely reason is that the spreadsheet is protected. Go to Review > Protect > Unprotect sheet. If a specific rule is not applied, check whether the current cell is in the rule’s cell range. Go to Data > Data validation to check.

Conclusion

It’s easy to use data validation in Google Sheets. You can add validation rules as simple or complex as you wish and prevent mistakes related to data input. Additionally, you can make it easier for users to input data by adding drop-down lists or checkboxes to your cells. You can manage these rules from the data validation side panel, where you can edit or remove them at any time.

You now know how to create, copy, edit, and remove data validation rules. You have step-by-step instructions for two different examples of data validation rules using different types of criteria. The first example showed you how to create a drop-down list in your cells, so users can quickly select valid options. You also know how to enforce a valid date format in your cells, allowing users to choose the date from an in-cell pop-up calendar.

To learn more about data validation in Google Sheets, check out these guides on:

Maria Del Olmo
Originally published Mar 14 2023, Updated Jun 26 2023

Layer is now Sheetgo

Automate your procesess on top of spreadsheets