How to Apply Data Validation in Google Sheets

Have you ever found yourself double-checking your reports, anxious that you’ve misplaced a decimal?

Or worse, maybe you discovered that misplaced decimal and had to rework your report from scratch?

Game-changing business insights and well-deserved recognition may depend on the accuracy and integrity of your data in a single spreadsheet.

That’s where data validation becomes a necessity.

Data validation ensures that the data entered into a system or spreadsheet meets specific criteria or rules. It saves you hours you’d otherwise spend sifting through mountains of data.

Understanding how to leverage data validation is the key to seamless data management.

If you’re using Google’s spreadsheet app, Google Sheets, like 900 million other people in the world, then this guide is exactly what you need.

We’ll walk you through everything from setting up basic validation rules to mastering advanced techniques, ensuring your data stays squeaky clean and error-free.

How to Apply Data Validation in Google Sheets

Understanding the Basics of Data Validation in Google Sheets

Data validation is a vigilant gatekeeper that ensures that only the correct data stays in your Google Sheets database. Like most validation techniques, this involves setting specific rules for what is allowed and what isn’t.

Here’s a clear, logical example to help break it down:

Scenario: You’re creating a customer order form
What’s needed: You want to ensure that customers enter valid data, such as a valid email address
The rule: Your spreadsheet’s ‘Email’ field should follow a specific email format
The result: Data validation rules constrain user-entered values and prevent incorrect data. It also allows you to create a pop-up box to inform the customer of the issue if they update the wrong information

While this was a fairly straightforward example, it shows how much control data validation has over data quality.

Setting up basic data validation in Google Sheets

Here are the six steps involved in creating data validation rules in Google Sheets:

Step 1: Open your spreadsheet

Open your spreadsheet. For this, we’ll consider the above example of a customer order form.

Customer order data for data validation in google sheets — *Create a spreadsheet template with customer order data*

Step 2: Select your data range

Select the cells for which you want to set the rule.

Let’s set a rule for the field Order Quantity. Select the cell where customers would add their order quantity—D8.

Cell range for data validation in Google Sheets — *Select a cell range for data validation in Google Sheets*

Step 3: Go to the Data Validation option

Under the Data menu, select ‘Data Validation’ and click on ‘Add Rule.’

Data validation in Google Sheets — *Navigate to data validation in Google Sheets*

Step 4: Define your rule criteria

In the ‘Criteria’ section, choose the type of validation you want to apply.

Here, we’ll validate numbers, select ‘less than or equal to,’ and update 100 in the Value Field.

Criteria for data validation in google sheets — *Establish your criteria for data validation*

Step 5: Define your error message

Next, navigate to the ‘Advanced Option’ section. Under ‘If the data is invalid,’ select what you want to do when someone enters invalid data. You can also add help text to guide users.

Here, we’ll select ‘Reject the input’ because your business won’t be able to accept the order. We’ll also add the help text ‘Order up to 100 pieces in one go.’

Defining a data validation rule's error message — *Define a data validation rule’s error messag*e

Step 6: Save

Select ‘Done’ to save and apply the rules you’ve defined.

Note: It’s best to set up data validation before entering your data. This is because existing values won’t be checked automatically. To validate existing data, you’ll need to review it manually or re-enter it.

With these steps, basic data validation is in the bag!

That said, before exploring advanced techniques, it’s important to understand more about the various types of data validation.

Types of data validation

While we used numerical rules in the previous section, having a detailed overview of various data validations helps ensure accuracy and quality.

Here’s a breakdown of the data validation types available in Google Sheets:

Number

Restrict data input to numerical values within a specified range. This includes conditions like ‘greater than,’ ‘less than,’ ‘equal to,’ or even ‘between a custom range.’

Text

Limit text input based on length and specific characters. This also includes patterns such as email IDs or valid URLs.

Date

This data validation type ensures that entered values are valid dates or within a specific range.

List

If you need to restrict the inputs to a list of options, you will use the ‘List’ or ‘Drop-down’ data validation.

Tickbox

Here, you restrict input to either ticking a box or not. You can also set what it means to leave the field ticked or unticked. This is used best when collecting customer preferences and quality checklists.

Bonus: Ready to transform your data management? Follow our step-by-step guide to create a Google Sheets database!

Advanced Data Validation Techniques in Google Sheets

While we’ve covered the basics, Google Sheets offers more advanced data validation techniques, such as custom formulas and in-app feature integration.

Custom formula

This feature is an advanced form of data validation in Google Sheets that is available when setting the criteria. Unlike other validation types, a custom formula allows you to create rules beyond basic options like numbers or dates.

This customized rule is set up with the help of formulas you usually use for calculations. A key advantage is that you define the kind of data allowed in a cell through this logical formula with higher precision.

Custom formulas create rules powered by elements such as cell ranges or dependencies. They also allow you to add multiple layers of regulations.

Here’s an example:

Using a custom formula as a rule in tracking inventory details — *Use a custom formula as a rule in tracking inventory details*

Scenario: Imagine you’re tracking product inventory before a customer dispatch.

What’s needed:

You want to ensure the ‘inventory available for dispatch’ column only accepts:

Numbers (no text allowed)
Values greater than zero (you can’t have negative stock)

Customer formula rule:

=AND(ISNUMBER(A1), A1>0)

This breaks down the rule into three parts:

ISNUMBER(A1): Checks if the value in cell A1 is a number
A1>0: Checks if the value in cell A1 is greater than zero
AND(…): Makes sure BOTH conditions are true

Result: Accurate inventory details reach customers, and you don’t need to worry about the formulas you’ve used because the inventory figures will always be reliable and easy to read.

Integrating data validation in Google Sheets

Advanced data validation also involves integrating other features into data validation.

Confused? Let’s review a few advanced data validation integrations that boost functionality.

Using conditional formatting in data validation

Data validation in Google Sheets is easily paired with conditional formatting, creating a much more interactive and clear spreadsheet.

In this integration, a separate data validation rule triggers conditional formatting. It visually highlights important data points or potential errors, making it easier to identify trends and anomalies.

Let’s show you how with a clear example:

Scenario: You need a project management spreadsheet to help track tasks. It must visually highlight when you’re behind schedule.

Step 1: Create a spreadsheet with columns for task name, due date, and status. Add your latest tasks and statuses, too.

Step 2: Go to Format and click on conditional formatting.

Conditional formatting in Google Sheets — *Navigate to conditional formatting in Google Sheets*

Note: You can create a rule for the due dates column to ensure the user enters a valid date.

Step 3: Add your data validation rule, linking it with data validation.

Establishing the data validation rule through conditional formatting — *Establish the data validation rule through conditional formatting*

Here, we’ll use the custom formula ‘=AND(TODAY() > C2, D2 <> ‘Completed’)’.

Here’s how that works:

TODAY(): This function returns today’s date
TODAY() > C2: This checks if today’s date is greater than the date in the ‘Due date’ field
D2 <> ‘Completed’: This checks if the ‘Status’ field is NOT Completed
=AND: This checks both conditions are met

Step 4: Add your formatting style. Here, we’ll keep the result as red (because it’s overdue).

Setting up the formatting style — *Set up the formatting style*

Once you’ve clicked ‘Done,’ your rules will be active. The ‘Send performance report’ is pending and running behind schedule.

Linking data validation with automation

Another advanced data validation technique that Google Sheets features is automation.

Here, Google features its App Script extension, which creates code that triggers an action based on your data validation dialog.

Here’s an example of automating expense approvals with data validation:

If your data validation rule states that:

‘Approval Required’ field is ‘TRUE’ (which means the box is ticked)
AND the ‘Status’ field is Pending

You can write a code that triggers a mail with all expense details to your manager based on the data in the ‘Approver’s email’ field.

Bonus: You can unlock the power of Google Sheets as your CRM with our easy-to-follow guide and ready-to-use templates.

Practical Applications: Drop-down List and Data Validation

While we’ve covered data validation techniques, one method stands out in almost every real-world application and deserves a deep dive—the drop-down list.

So, to kick things off, here are the steps involved in creating a drop-down list using data validation in Google Sheets:

Step 1: List down your options

Create your spreadsheet and list down what you want in your drop-down.

For this, we will review employee onboarding as a part of your Google CRM sheet.

List options in a drop-down list — *Create the list options you want in your drop-down list*

Afterward, go to the Data tab, then Data validation, and ‘Add rule.’

Step 2: Select the drop-down in the data validation

Drop-down list criteria in data validation — *Establish the drop-down list criteria in data validation*

Once the data validation rules dialog box is open, select the cell range (here, it’ll be the entire ‘Department’ column), then choose your Criteria.

Now, there are two types of drop-down list criteria.

Drop-down list: This involves manually adding your options (departments)

Drop-down list for manually entering list options — *Select the drop-down list and enter list options manually*

Drop-down list (from a range): This allows you to choose a range of cells and automatically add cell values as list options

Drop-down list from a range — *Select the drop-down list from a range and select the range of list options*

Note: Each list option can also be assigned a color to keep things aesthetic and appealing. You must manually update each color while the list changes with the reference data.

Step 3: Click Done and review

The final step is to click ‘Done’ and watch the validation reflect in the cells.

For this example, all your list options will drop down when you click on the ‘Department’ field.

Drop-down lists in a spreadsheet data validation in google sheets — *A final view of drop-down lists in the spreadsheet*

Just like that, your drop-down list is ready. However, creating a basic drop-down list is just the start of this versatile data validation method. Let’s cover how to use an additional layer of data validation.

Dependent drop-down lists in Google Sheets

While drop-down lists help focus responses, Google Sheets allows users to create dependent drop-down lists to extend control to upcoming data fields.

A dependent drop list of active filters will be used to revise the list of options. By creating these filters, options in one drop-down list change based on the selection made in a previous drop-down list.

This dynamic data validation is incredibly useful for organizing and filtering data efficiently, particularly when dealing with large datasets.

Shortcuts to tickbox and drop-down data validation in Google Sheets — *Shortcuts to tickbox and drop-down in Google Sheets*

💡 Pro Tip: It’s important to remember that you don’t need to access data validation every time you need a drop-down list or tickbox. A quick Google Sheets hack is that it’s readily available in the Insert tab.

Troubleshooting Common Data Validation Challenges

Now that you know how to set up data validations, you will also encounter common errors. Whether these may be from the input, its use, or complex range issues, it’s essential to know how to navigate them.

Here are two challenges or errors and how to troubleshoot them:

Error #1: The data you entered violates the data validation rules set for this cell

This error message appears when you attempt to enter data into a cell that doesn’t comply with the rule you’ve established to validate data for that cell.

This most likely means one of the following:

The entered value doesn’t match the specified criteria (e.g., numerical data range, list of allowed values, custom formula)
There are issues with the rules established

When this occurs even when the value entered seems to be correct, here are the key troubleshooting steps you must cover:

Ensure the data validation criteria are accurate and logical
Double-check for typos, extra spaces, case mismatches, or data type differences
Retype manually to eliminate hidden characters if the value was copy-pasted
Check for circular references that might cause conflicts if the validation rule involves formulas

Error #2: Issues with filter sorting that may break data validation

Another issue occurs when applying filters or sorting data. It disrupts the functionality of dependent drop-down lists or other data validation features. It might lead to incorrect or empty options in dependent lists or incorrectly enforced validation rules.

Here are the common factors behind these errors:

If your data validation relies on named ranges and these ranges are not set up to adjust automatically when data is filtered or sorted, the references break
Using relative references in data validation formulas causes issues when the data’s position changes due to filtering or sorting
Some data validation setups are not compatible with the way Google Sheets handles filtering and sorting, leading to unexpected behavior

If you want to resolve this issue, you need to

Make named ranges dynamic: If you’re using named ranges, ensure they are dynamic and update automatically when the data is filtered or sorted. You achieve this by using formulas within the named range definitions to account for filtering or sorting
Use absolute references: In your data validation formulas, replace relative references with absolute references (e.g., $A$1) to ensure they always point to the correct cells, even after filtering or sorting
Introduce helper columns: Consider creating helper columns to store the original, unfiltered data. Reference these helper columns in your data validation formulas to keep them unaffected by filtering or sorting

Google Sheets Challenges and Limitations

While Google Sheets is used by many and offers features that update in real time, here are some challenges and limitations that they come with:

Performance with large data sets: When working with thousands of rows and complex calculations, this productivity app often becomes sluggish
Limited formatting: As you use Google Sheets, you’ll notice that creating visually appealing or precise layouts is quite challenging. Its formatting and visualizations are quite limited
No spell check: Compared to other spreadsheet software, this application does not come with spell check. This makes it quite inconvenient when handling data with text or using it for documentation
Confusing pivot tables: Google Sheets does not have a great track record in handling pivot table creation and manipulation. Its summarization features are less than robust and sometimes confusing
Analytical features: Setting up Google Analytics with Sheets is quite complex. It also lacks complex statistical functions or advanced data modeling tools

Google Sheets is an excellent tool for data organization, basic calculations, and merging data sheets. However, its capabilities are limited to those cells.

When you need to manage tasks and projects and collaborate more comprehensively, ClickUp—a project management veteran—emerges as a powerful alternative.

Google Sheets Alternatives

Feature	Google Sheets	ClickUp
Spreadsheet functionality	✅	✅
Task management	❌	✅
Project management	❌	✅
Collaboration	✅	✅
Integrations	✅	✅
Gantt charts	❌	✅
Time tracking	❌	✅

ClickUp

ClickUp is a platform solution that delivers tools to increase productivity and empower insights. It’s perfect for ensuring data accuracy and quality through data validation.

The platform also presents a dedicated spreadsheet tool that integrates functionality with robust work and project management features.

ClickUp 3.0 Table view Simplified — *Create spreadsheets that drive efficiency, deliver key insights, and ensure data accuracy with ClickUp Table View*

ClickUp Table View is designed to create fast spreadsheets and powerful visual databases. It’s an ideal solution for optimizing budgets, powering dashboards, and more. Use it to:

Create, assign, and track ClickUp Tasks with due dates, priorities, and Custom Fields within a spreadsheet. ClickUp also offers several spreadsheet templates to reduce the effort of setting up data fields
Structure and manage your spreadsheets and related projects with views such as ClickUp Board View. This helps you ensure your team isn’t overburdened and your spreadsheet isn’t compromised
Share documents, comment on tasks, and assign permissions for seamless teamwork at the click of a button using ClickUp Docs
Visualize data from Table View with Gantt charts, Timelines, and Dashboards. This helps track project progress and deliver clear insights
Connect with over 1000+ tools like Slack, Google Drive, and Power BI to streamline your workflow and insights with ClickUp Integrations
Automate any data validation and rules instantly with ClickUp Brain. This tool is also compatible as an AI tool for Google Sheets

In essence, ClickUp’s features, such as Table View, instantly familiarize you with spreadsheets while adding depth and functionality.

Enhance Your Data Validation with ClickUp

Data validation is a concept that transforms data accuracy in every business. Just by creating customized rules, it can potentially reject any incorrect data right when it tries to slip in.

Our comprehensive breakdown of how to use data validation in Google Sheets also applies to many other spreadsheet software.

That said, if you’re handling larger volumes of data or want to connect your projects with data processing, ClickUp’s Table View is a much better choice.

Questions? Comments? Visit our Help Center for support.

Google Workspace

How to Apply Data Validation in Google Sheets

Start using ClickUp today

Understanding the Basics of Data Validation in Google Sheets