How to Use Data Validation in Google Sheets

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.

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

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.

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.

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

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.

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 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).

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

As you can see, the new range has been added to the existing data validation 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.

You will see the same options as when you created the 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.

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

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.

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’.

1. 2. Select the range to which you want to apply the rule. In this case, all of column B except the header.
   

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.
   

1. 4. Add the items you want to see on the drop-down. You can assign a different color for each item.
   

1. 5. Click ‘Done’ to apply the 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’.
   

1. 2. Under ‘Criteria’, select ‘Is valid date’.
   

1. 3. Click ‘Advanced options’ to choose the behavior you want upon invalid data input. Click ‘Done’ to apply.
   

1. 4. Double-click on any cell to see the pop-up calendar and select a date.
   

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.

Experience Confluence: The Ultimate Team Workspace

Transform the way your team collaborates with Confluence, a remote-friendly workspace designed to bring knowledge and collaboration together. Say goodbye to scattered information and disjointed communication, and embrace a platform that empowers your team to accomplish more, together.

Key Features and Benefits:

• Centralized Knowledge: Access your team’s collective wisdom with ease.
• Collaborative Workspace: Foster engagement with flexible project tools.
• Seamless Communication: Connect your entire organization effortlessly.
• Preserve Ideas: Capture insights without losing them in chats or notifications.
• Comprehensive Platform: Manage all content in one organized location.
• Open Teamwork: Empower employees to contribute, share, and grow.
• Superior Integrations: Sync with tools like Slack, Jira, Trello, and more.

Limited-Time Offer: Sign up for Confluence today and claim your forever-free plan, revolutionizing your team’s collaboration experience.

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:

• How to Add a Checkbox in Google Sheets
• How to Add a Drop-Down List in Google Sheets