See How Data Engineering Gets Done on Our Do-It-Yourself Data Webcast Series

Start Free

Speed up your data preparation with Trifacta

Free Sign Up
Summer of SQL

A Q&A Series with Joe Hellerstein

See why SQL is Back
All Blog Posts

Google Sheets: Data Validation Tips & Tricks

December 13, 2020

Google Sheets is one of the most widely-used spreadsheet tools. Still, many of its best features go undiscovered. Let’s take a closer look at how to do data validation in Google Sheets, which is commonly used to build drop-down lists. 

Why data validation matters

Data validation is like the analytic version of copyediting. As much as you’re careful about the words you write (or the data you input), it’s always good to have someone check your work. Data Validation in Google Sheets allows you to set certain parameters around numbers, dates, and text that prevent you from making input errors. 

For example, say you’re inputting exam grades. You may want to set a data validation parameter that states that any inputted number must be 0 through 100. If a number greater than 100 is entered, you can have Google Sheets either 1) flag that value, indicating that it doesn’t follow your data validation rules, or 2) reject that input. In another instance, let’s say you’re entering a list of purchases made in 2020. Setting a data validation parameter would ensure that if a date was entered for a purchase made before 2020, that value would also be flagged or rejected. 

Google Sheets data validation is also helpful in preventing misspellings or unwanted values by restricting users to select from a drop-down list. Say you’re asking a group of users to enter their addresses. Instead of writing in their state name (which can take on many different forms and misspellings), they must select their state from a drop-down list.

How to create a drop-down list using data validation

Many users have also found the drop-down list a helpful feature for program management as it can clearly track the status of any project. Here’s how to do it.

  1. First, select the column, row, or set of cells where you’d like to create a drop-down list.

  2. Next, go to Data > Data Validation. This will open up the menu bar.

  3. If you need to write your own list of drop-down options, select “List of items.” Separate each item by a comma and then press save.
    google sheets data validation
  4. If you want to create a drop-down list from values already in your Google Sheet, select “List from a range” and identify the range of values that should be included in the drop-down. For example, that could be a list of names.

How to add check marks using data validation

Check marks are a simple addition to your Google Sheet, but they can make a big difference in terms of organization. Here’s how to add them. 

  1. First, select the column, row, or set of cells where you’d like to create your check marks. In this case, we’re adding it next to a list of names, which will indicate that these people have contributed to the fundraiser.
  2. Next, go to Data > Data Validation. This will open up the menu bar.
  3. Under “Criteria,” select “Checkbox” and hit save. You’re done!

How to validate data using custom formulas

Google Sheets data validation also gives you the option to build your own formulas to validate your data. In this example, we’re going to set a parameter for the column listing the donation totals for each person, which cannot logically be lower than their first donation. Here’s how we’ll do it. 

  1. First, select the column, row, or set of cells that will be affected by your validation.

  2. Next, go to Data > Data Validation. This will open up the menu bar.

  3. Under “Criteria,” select “Custom Formula is” and then enter the formula you’d like to use. In this case, we’re going to write “D2<K2” to signify that the totaled donation number needs to be larger than the original donation.
  4. You can choose to either “Show warning” or have Google “Reject input” for any values that don’t coincide with your data validation parameter.

  5. In this case, I’ve chosen to “Show warning” and you can see that when a value less than the first donation is entered, it is flagged by Google.

Data preparation and data validation

To put data validation into context, it is just one part of a larger process called data preparation. Data preparation is the process of readying data for analysis, which includes things like removing errors, structuring columns, standardizing values, etc. 

Specifically, the six steps of data preparation include: 

  • Discovering
  • Structuring
  • Cleaning
  • Enriching
  • Validating
  • Publishing

Of course, data preparation isn’t unique to Google Sheets but is an essential process for any type of data—no matter where it lives. Though data in Google Sheets tends to be rather small and structured, the data world beyond Google Sheets is anything but. And as data explodes in complexity and size, the difficulty of data preparation has grown along with it.

Analysts that have either outgrown spreadsheet tools like Google Sheets or that need to blend data from Google Sheets with other data formats are adopting modern data preparation platforms. These technologies were built for data preparation difficulties of the modern age and allow analysts to reduce time spent preparing data by up to 90%. 

The Trifacta data preparation platform

Trifacta is widely recognized as the industry leader in data preparation. Trifacta’s machine-learning powered platform acts as an invisible hand during the data preparation process, guiding users toward the best possible transformation. Its visual interface automatically surfaces errors, outliers, and missing data, and it allows users to quickly edit or redo any transformation. 

Trifacta is offered as Google Cloud Dataprep by Trifacta on Google Cloud Platform, which allows users to easily prepare data from BigQuery, Google Sheets, or Google Docs. It also allows users to set up automatic data quality reports, which, much like data validation in Google Sheets, will flag any errors, missing data, etc. at a larger scale. 

Learn why organizations are choosing Trifacta. Schedule a free demo from our team or get started right away with Trifacta on the platform of your choice.