[et_pb_section fb_built=”1″ _builder_version=”4.17.4″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row _builder_version=”4.17.4″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.17.4″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n
Google Sheets offers a variety of features and tools that allows you to have complete control over how you manage your data – one of these is data validation.<\/p>\n
The data validation feature in Google Sheets<\/a> allows you to control what type of data you can add to the cells in your spreadsheet. This is a particularly useful feature when sharing your Google Sheets files with other users, or trying to control the type of data you have inside of your files.<\/p>\n Not only can data validation give you more control over your spreadsheet data, but it also saves you a huge amount of time and effort from having to edit pieces of data. Once you have set up data validation, users are reduced to a specific range of data to input.<\/p>\n Let\u2019s take a look in more detail at what the data validation feature offers in Google Sheets, and how you can validate data in your own spreadsheets in just a few simple steps!<\/p>\n [\/et_pb_text][et_pb_text module_id=”data-validation” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Data validation is a type of formatting feature in Google Sheets that allows you to determine the type of inputs you can add to your spreadsheet file. This will then limit the type of input a user can enter into your spreadsheet; if the input is not within the data range you have established, then the input becomes invalid.<\/p>\n Let\u2019s take a look at each element within the data validation feature:<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/Data-validation-2.png” alt=”Data validation 2″ title_text=”Data validation 2″ align=”center” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”why-validate” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n There are many reasons why you should use data validation in Google Sheets. Here are some of the most important benefits:<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.17.4″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” background_color=”#f6f8f9″ custom_margin=”0px||||false|false” custom_padding=”10px|10px|10px|10px|false|false” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Sheetgo Forms can make your life even easier. With Sheetgo, you can input data in Google Sheets easily by simply filling out a form. Click the button below to start using Sheetgo Forms.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”use-case” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n These benefits apply to everyday data management, so data validation can indeed be used in almost any spreadsheet process and use case.<\/p>\n For example, let\u2019s say I am in charge of collecting employee data to monitor monthly salary payments. I can use data validation to control the type of input received, such as the formatting of their date of birth, the department they belong to, or their employee ID.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/Data-validation.png” alt=”Data validation 1″ title_text=”Data validation” align=”center” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.17.4″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n With data validation, there is no risk of accidental errors from manual input, or employees inputting incorrect data.<\/p>\n As a result, I will have a large file of accurate, employee data that I don\u2019t have to read through and edit, ultimately saving me hours of work.<\/p>\n [\/et_pb_text][et_pb_text module_id=”how-to-do-data-validation” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Let\u2019s use the use case above to go through a step-by-step data validation process.<\/span><\/p>\n As mentioned previously, there are several different criteria for data validation in Google Sheets.<\/span><\/p>\n Let\u2019s go through each of these to understand exactly what they do and what they may look like inside your own Google Sheets file, using the use case mentioned above.<\/span><\/p>\n [\/et_pb_text][et_pb_text module_id=”dropdown” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This is the option to create a pull-down menu in the sheet by inputting the choices manually.<\/span><\/p>\n First, under <\/span>Criteria<\/b>, select <\/span>Dropdown<\/b>.<\/span><\/p>\n Then choose the range of cells where you want to create the dropdown list.<\/span><\/p>\n Add the text to the boxes. In this example, I’ll add all the departments.\u00a0<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/Data-validation-4.png” alt=”Data validation 4″ title_text=”Data validation 4″ align=”center” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n You can also choose different colors for each textbox you create.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/Data-validation-5.png” alt=”Data validation 5″ title_text=”Data validation 5″ align=”center” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n If you prefer, you can select the options from a range of cells instead of typing all the text in the boxes.<\/span><\/p>\n In this case, select <\/span>Dropdown (from a range)<\/b> in the <\/span>Criteria<\/b> section.<\/span><\/p>\n After selecting the range where you want to create the dropdown, choose the range of cells containing all the options you need to add to the list.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/Data-validation-6.png” alt=”Data validation 6″ title_text=”Data validation 6″ align=”center” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]Once I have applied this, you will see my dropdown now contains all the departments. I’ll click <\/span>Done<\/b> to create the dropdown list.<\/span>[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/Data-validation-7.png” alt=”Data validation 7″ title_text=”Data validation 7″ align=”center” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now, I don’t need to input the data manually. All I need to do is choose one of the options from the dropdown list.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/Data-validation-1-1.png” alt=”Data validation 8″ title_text=”Data validation (1)” align=”center” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”text” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The <\/span>Text<\/b> criterion allows you to control the type of text a user inputs into a cell range. This criterion gives you a lot of options of how to control data, from only accepting an exact text match, to accepting a valid email address or URL.<\/span><\/p>\n In this example, I can ensure employees input a valid email address in my \u2018Email\u2019 column by selecting <\/span>Text is valid email<\/b>.<\/span><\/p>\n Under <\/span>Advanced options<\/b>, you can choose what will happen if I input invalid data. Google Sheets can <\/span>show a warning<\/b> or <\/span>reject the input<\/b> when the data is invalid.<\/span><\/p>\n I’ll choose <\/span>Show a warning<\/b> and click <\/span>Done<\/b>.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/Data-validation-8.png” alt=”Data validation 8″ title_text=”Data validation 8″ align=”center” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now if I input invalid data, Google Sheets will show a warning message.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/Data-validation-9.png” alt=”Data validation 9″ title_text=”Data validation 9″ align=”center” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”date” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The Date<\/strong> criterion helps you make sure only valid dates are added to the cells and even lets you control date inputs before or after a certain date, or between certain dates.<\/span><\/p>\n For example, let\u2019s say in my “Date of Birth” column, I want to make sure that each employee adds their dates correctly. By choosing the <\/span>Date<\/b> criterion and the <\/span>Is valid date<\/b> option, any invalid dates will be rejected by Google Sheets.<\/span><\/p>\n <\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/Data-validation-10.png” alt=”Data validation 10″ title_text=”Data validation 10″ align=”center” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now Google Sheets will only accept a valid date when you input data into the cells where the data validation is applied. If you double-click a cell within this range, a calendar will appear.<\/span><\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/Data-validation-11.png” alt=”Data validation 11″ title_text=”Data validation 11″ align=”center” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”number” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The Number criterion allows you to define a range of numbers that users can input. You have options to validate numbers above or below a certain value, or between two values.<\/span><\/p>\n For example, in my “Employee ID” column, I want to make sure that employees correctly add their 4-digit ID number. I can choose <\/span>Number<\/b> is between<\/b> and add the lowest value as 1000 and the highest as 9999.<\/span><\/p>\n This means that users can only input a 4-digit number.<\/span><\/p>\n <\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/Data-validation-12.png” alt=”Data validation 12″ title_text=”Data validation 12″ align=”center” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”checkbox” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The <\/span>Checkbox<\/b> criterion is perhaps the most straightforward data validation. It allows you to format the cell as a checkbox.<\/span><\/p>\n For example, let\u2019s say I want to add a checkbox to my \u2018Paid?\u2019 column so that employees can check this when they have received their paycheck. Simply select the <\/span>Checkbox<\/b> criterion as shown below.<\/span><\/p>\n <\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/04\/Data-validation-13.png” alt=”Data validation 13″ title_text=”Data validation 13″ align=”center” _builder_version=”4.20.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”how-to-validate” _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Sheetgo Forms offer a powerful data validation tool for inGoogle Sheets.<\/p>\n By integrating Sheetgo Forms, you can streamline data collection while ensuring that entries meet predefined validation criteria, reducing errors from manual input.<\/p>\n Sheetgo Forms automatically populate data into your spreadsheets, enabling instant validation. This feature ensures that the collected data adheres to the correct format and rules from the start, improving accuracy and efficiency.<\/p>\nWhat is data validation?<\/h2>\n
\n
Why validate your data in Google Sheets?<\/h3>\n
\n
Use case<\/h3>\n
How to do data validation in Google Sheets<\/span><\/h2>\n
Dropwdown and dropdown from a range<\/span><\/h3>\n
Text<\/strong><\/h3>\n
Date<\/b><\/h3>\n
Number<\/span><\/h3>\n
Checkbox<\/span><\/h3>\n
Sheetgo Forms: Elevating Data Validation<\/h3>\n