Cómo utilizar la validación de datos en hojas de cálculo de Google: consejos y trucos que debes saber

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.

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

Google Sheets data validation 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 Do You Use 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

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. 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 Designer Cloud data preparation platform

Designer Cloud is widely recognized as the industry leader in data preparation. Designer Cloud’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. 

Designer Cloud 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 Designer Cloud. Get started today with Designer Cloud on the platform of your choice.