How to Apply Data Validation in Google Sheets

How to Apply Data Validation in Google Sheets

Start using ClickUp today

  • Manage all your work in one place
  • Collaborate with your team
  • Use ClickUp for FREE—forever

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.

Summarize this article with AI ClickUp Brain not only saves you precious time by instantly summarizing articles, it also leverages AI to connect your tasks, docs, people, and more, streamlining your workflow like never before.
ClickUp Brain
Avatar of person using AI Summarize this article for me please

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 message

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

Number data validation rules
Example: Ensuring the customer enters an order quantity less than or equal to 100

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

Data validation rules for text
Example: Ensuring the email ID is valid

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

Date

Date formatting rules for data validation
Example: Ensuring the date of birth is the correct format

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

List

Drop-down in data validation
Example: Drop-down in data validation

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

Tickbox

Tick box for data validation
Example: How to use tickbox for data validation

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!

Summarize this article with AI ClickUp Brain not only saves you precious time by instantly summarizing articles, it also leverages AI to connect your tasks, docs, people, and more, streamlining your workflow like never before.
ClickUp Brain
Avatar of person using AI Summarize this article for me please

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)

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

Spreadsheet for task progress tracker
Spreadsheet for task progress tracker

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:

How to reach Google Apps Script
How to reach Google Apps Script

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.

Summarize this article with AI ClickUp Brain not only saves you precious time by instantly summarizing articles, it also leverages AI to connect your tasks, docs, people, and more, streamlining your workflow like never before.
ClickUp Brain
Avatar of person using AI Summarize this article for me please

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.

Summarize this article with AI ClickUp Brain not only saves you precious time by instantly summarizing articles, it also leverages AI to connect your tasks, docs, people, and more, streamlining your workflow like never before.
ClickUp Brain
Avatar of person using AI Summarize this article for me please

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
Summarize this article with AI ClickUp Brain not only saves you precious time by instantly summarizing articles, it also leverages AI to connect your tasks, docs, people, and more, streamlining your workflow like never before.
ClickUp Brain
Avatar of person using AI Summarize this article for me please

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.

Summarize this article with AI ClickUp Brain not only saves you precious time by instantly summarizing articles, it also leverages AI to connect your tasks, docs, people, and more, streamlining your workflow like never before.
ClickUp Brain
Avatar of person using AI Summarize this article for me please

Google Sheets Alternatives

FeatureGoogle SheetsClickUp
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.

Summarize this article with AI ClickUp Brain not only saves you precious time by instantly summarizing articles, it also leverages AI to connect your tasks, docs, people, and more, streamlining your workflow like never before.
ClickUp Brain
Avatar of person using AI Summarize this article for me please

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. 

Sign up with ClickUp today and start transforming your data quality.

Questions? Comments? Visit our Help Center for support.

Sign up for FREE and start using ClickUp in seconds!
Please enter valid email address