How to Use Data Validation in Google Sheets: Tips and Tricks to Know

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 building a drop-down list using data validation in Google Sheets.

What is data validation in Google Sheets?

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 and your data. 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 each grade into one cell next to the student’s name. You may want to set a data validation parameter in the grade cell that requires any inputted number to be within the range of 0 through 100. If a number greater than 100 is entered, you can have Google Sheets either 1) flag that value, indicating that it is outside of your data validation range 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 in the “year” cell would ensure that if a date was entered for a purchase made before 2020, that value would also be flagged or rejected. 

Data validation in Google Sheets is also helpful in preventing misspellings or unwanted values by restricting users to select from a drop-down list or a specific range. 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 apply data validation in Google Sheets

 Follow these easy-to-follow steps in order to validate your data using the data validation features in google sheets.

1. Open a spreadsheet in Google Sheets.
2. Select the cell or cells where you would like to create a drop-down list.
3. Press Data.
4. Next to “Criteria,” choose an option: 
5. The cells will have a Down arrow.
6. If you enter data in a cell that doesn’t match an item on the list you created, you will see a warning.
7. Press Save.

How to create a dynamic data validation in Google Sheets

Many users have struggled with creating dynamic data validation in the Google Sheets software. Follow these tips for a foolproof way to create this type of validation.

1. Select the cell where you want to create the rule and go to Data –> Data Validation.
2. In the Data Validation box, select the Criteria as “List from a range” and specify the cells that contain the names. 
3. Make certain that the “Show dropdown list in cell” is checked.
4. Click Save

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.
   
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, the range of cells you select could be a list of names, allowing you to select from that list of names with the drop-down in the validated cell.

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 of your Google Sheets data. 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!

Data validation in Google Sheets using custom formulas

Google Sheets data validation also gives you the option to build your own custom formula 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 custom 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. In this custom formula, 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 in Google Sheets

To put Google Sheets 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%. 

Faster and easier data preparation with Alteryx

Alteryx is a quick-to-implement, end-to-end data analytics platform that allows you — and everyone you work with — to solve business problems faster and deliver more value. Use our platform to discover, prep, and analyze all your data, plus deploy and share analytics at scale for deeper insights.

Benefits of automated data prep with Alteryx:

• Data preparation at light speed
• Repeatable workflows
• Code-free modeling through an intuitive interface
• Support for nearly every data source and visualization tool
• Performance, security, collaboration, and governance
• You can ingest and prepare data from anywhere and upskill with selfservice analytics

The Alteryx 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. 

Unleash the power of Alteryx to more efficiently explore, transform, and join together diverse data for analysis. Get started with a 30-day free trial.