Simply put, data validation works by giving a range of expected values to a cell. If the value entered is not in the correct format, or outside of the expected range, Sheets will show an error to the user that says their input is invalid. You can also reject invalid inputs all together.<\/p>\n
The other thing data validation in Google Sheets<\/a> can do is create drop-downs within a cell. A great time saver, because it means that you don\u2019t have to memorize long lists like SKU numbers or how to spell all the names in your spreadsheet.<\/p>\n [\/et_pb_text][et_pb_text admin_label=”Connections T – Automate between spreadsheets – Horizontal” module_class=”sheetgo-post-no-shadow-img md2-contained-button-light vertical-banner-container” _builder_version=”4.27.2″ _module_preset=”default” background_color=”#f2f7ff” max_width=”700px” module_alignment=”center” max_height=”300px” custom_margin=”20px|0px|20px|0px|true|true” custom_padding=”25px|25px|25px|25px|true|true” sticky_limit_bottom=”section” border_radii=”on|20px|20px|20px|20px” border_width_all=”1px” border_color_all=”#d9e7ff” global_module=”50460″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Automate data transfers between spreadsheets Find out how<\/a><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Google Sheets is built for teams. Collaboration is one of G Suite’s strong suits and differentiators when compared to similar softwares. Because it was built for the web from conception, it easily handles multiple users in a single spreadsheet, whether or not you\u2019re working at the same time.<\/p>\n However, many users working on the same spreadsheet can more easily cause errors. When you don\u2019t know who made an error or why they made it, your interpretation of the data is compromised. Errant data can also break complex formulas that take time and resources to fix. Moreover, if you enter wrong data that erases important information, you might not be able to recover it.<\/p>\n On the other hand, when everything works correctly, spreadsheets are a wonderful tool. For example, I have this monthly sales data for a wholesale company. On the side, I made a little chart that tracks total sales for each sales agent. Make a copy of the data here <\/a> to follow along.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/1-Overview.png” alt=”validate input values in Google Sheets 1″ title_text=”1 Overview” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The more you do with your spreadsheets, the more formulas you use, the more likely they are to break when you enter data incorrectly. Formulas are finicky. In large spreadsheets, one missing or mistyped cell can create a string of errors that take valuable time to trace back to the source.<\/p>\n Take a look at the example below. I mistyped an SKU number for one of Cathy\u2019s sales. Not only does it generate errors for that sale, it breaks her total sales chart too.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/2-SKU-Error.png” alt=”validate input values in Google Sheets 2″ title_text=”2 SKU Error” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Formatting isn\u2019t always clear, and it’s difficult to spot errors like a single mistyped digit. Instead of remembering and manually typing each time a sales team member makes a sale, let’s create a dropdown list with all SKU numbers for our products.<\/p>\n That\u2019s where Data validation comes in. Keep reading to find out how we solve this problem<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Find the Data validation menu either in the Data<\/b> tab in the menu bar, or right-click on the range you want to validate and select Data validation from there.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/3-Data-Validation-in-Menu.png” alt=”validate input values in Google Sheets 3″ title_text=”3 Data Validation in Menu” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Once you\u2019ve opened the menu, you\u2019ll see the different values you need to supply in order to have a working data validation for your spreadsheet.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Take a look at the screenshot below. Here is how we filled in the various boxes for our sample data.<\/p>\n Underneath the image, I explain each section and what it means for our set of data.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/4-Data-Validation-Menu.png” alt=”validate input values in Google Sheets 4″ title_text=”4 Data Validation Menu” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This is the range to which you want the validation to apply. This automatically fills with the range you had selected when you opened the data validation menu.<\/p>\n If you don’t have any range selected, type it here directly, or click the Select Data Range button on the right of the Cell Range dialogue box. Because we had our column with SKU data selected when we opened the menu, it auto-populated.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The criteria value defines both the type of range and size of the range of valid data. We\u2019ll go over each option in detail below.<\/p>\n For now, because we have our unique SKU numbers and their corresponding prices listed on our Prices tab, we\u2019ll go with the List from a Range<\/b> option and use the select data range button to pick our SKU numbers from the Prices page. It should generate =Prices!$A$2:$A$11 when you click OK.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This option is a switch that lets you either reject a value that falls outside of the valid range, or let the invalid data be kept and just show a warning that the data is invalid.<\/p>\n Opting for rejecting invalid inputs shows the validation help text in a popup. Opting to show a warning will show the help text as a dialogue box when the cell is selected. We\u2019ll go with Show Warning<\/b> for our example.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This is the message displayed when a cell with a data validation parameter is selected. You can customize the message to say whatever you want. This is especially helpful when you\u2019re passing the spreadsheet off to someone who is less familiar with Google Sheets.<\/p>\n This way, you remind them what value goes where without wasting time training and reminding others.<\/p>\n In our example, we want to show validation help. And instead of it saying Click and enter a number from the range, we want it to say “Click and enter a valid SKU” so our users know which range we\u2019re talking about.\n<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/5-Validation-Help-Text.png” alt=”validate input values in Google Sheets 5″ title_text=”5 Validation Help Text” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Each of the options in the criteria menu has a unique use case. All of these options can be valuable depending on the situation.<\/p>\n Here is how each functions and when you might want to use them.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n This is the criteria we chose in the previous example. It suits best for a set of values that you have elsewhere in your spreadsheet.<\/p>\n You can also select a range that extends past your list of values. Whenever you then add a new item to your list, it automatically adds up to the dropdown of your data validated range.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Like a list from a range, a list of items creates a dropdown menu for your cells. Instead of having to have the list of items you want in your dropdown somewhere else on your spreadsheet, you can list them out right in the criteria option box from the data validation menu.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n A number range lets you set limits on the number values entered in the specified range.<\/p>\n You can use all the traditional comparatives from mathematics (less than, less than or equal to, not equal to, etc.). You can also use between and not between to require or exclude values that fall in a range of numbers.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.27.2″ _module_preset=”default” hover_enabled=”0″ global_colors_info=”{}” theme_builder_area=”post_content” sticky_enabled=”0″]<\/p>\n Text is a uniquely powerful option in the data validation criteria list. With it, you validate strings that contain or don\u2019t contain certain characters or strings of characters.<\/p>\n It also includes rules that require inputs to be valid URLs or email, something that could be useful if you\u2019re using a Google Sheets add-on like YAMM.<\/a><\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The date value criteria does more than you might expect.<\/p>\n Not only can you require values to be entered as a date, but you can also require values to be: before, after, between or not between specified dates. <\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n The checkbox criteria is essentially the regular checkbox cell format, found in the Insert menu dropdown.<\/p>\n The only difference is that you can alter the checked and unchecked values for cells. By default, they\u2019re set to true and false however.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Last but not least, we have the Custom formula is option. This is the most powerful of the options because it allows you to write a custom formula.<\/p>\n For example, imagine our wholesale company has a minimum order number of 500 for their products which cost less than $10.00 per unit. We want to check that either the PPU was over $10.00 or the number of units ordered was over 500.<\/p>\n The formula for that is =OR($G2>10,$F2>500). We want to only warn, not reject, invalid data because it is possible that a salesperson made the mistake of under selling on a low-cost product.<\/p>\n We also include the help text of \u201cMinimum Order Size of 500 on products under $10.00 per unit,\u201d so that it\u2019s clear why the value was not validated.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/6-Custom-Formula.png” alt=”validate input values in Google Sheets 6″ title_text=”6 Custom Formula” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n When we apply that validation to the Order size column, we see that one sale wasn\u2019t validated.<\/p>\n Next, we want to either double-check that the information entered was correct, or fix the mistake if we know the correct values.<\/p>\n [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2021\/03\/7-Invalid.png” alt=”validate input values in Google Sheets 7″ title_text=”7 Invalid” align=”center” _builder_version=”4.16″ _module_preset=”default” width=”640px” max_width=”640px” height=”440px” max_height=”440px” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.16″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Now we\u2019re able to collaborate with our sales team and they are able to manage their data without accidentally breaking the spreadsheet.<\/p>\n You can also protect certain cells or entire pages in Google Sheets. This solution only works if you want to make sure that only certain people can alter certain cells. Read through this blog post<\/a> to find out how.<\/p>\n [\/et_pb_text][et_pb_text _builder_version=”4.22.2″ background_color=”#f9f9ff” custom_margin=”40px||40px||false|false” custom_padding=”15px|25px|15px|25px|true|true” border_width_left=”3px” border_color_left=”#808e95″ global_module=”26540″ saved_tabs=”all” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n Editor\u2019s note<\/strong>: This is a revised version of a previous post that has been updated for accuracy and comprehensiveness.<\/em><\/p>\n [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":" What is data validation? Data validation is a system that makes sure values in your spreadsheet conform to expectations, and thus validate input values in Google Sheets. It works like a gate, only allowing cells to be filled with data that match a defined rule. Simply put, data validation works by giving a range of […]<\/p>\n","protected":false},"author":2,"featured_media":33334,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"Google Sheets is built around the central fabric called collaboration. Therefore, it is a commonplace that multiple people work on a single Google Sheets file. And when doing so, we may have to restrict people from entering unwanted values in cells, accidentally or otherwise. In this article we explain how to validate input values in Google Sheets before they are entered into the cells.\n![\"\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/run-automatically-connect-sheet-icons.webp\"){kind=icon}
<\/p>\n![\"39\"](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2024\/09\/Recommended-for-Google-Workspace-badge.webp\"){kind=icon}
<\/p>\n
<\/span><\/p>\nWhy validate input values in Google Sheets?<\/h3>\n
How to access the Data validation menu<\/h3>\n
Data validation menu options<\/h3>\n
Cell range<\/h4>\n
Criteria<\/h4>\n
Show warning or reject input<\/h4>\n
Show validation help text<\/h4>\n
Data criteria types and functionality<\/h3>\n
1. List from a range<\/h4>\n
2. List of items<\/h4>\n
3. Number<\/h4>\n
4. Text<\/h4>\n
5. Date<\/h4>\n
6. Checkbox<\/h4>\n
7. Custom formula is<\/h4>\n
Wrapping it up<\/h3>\n
Why validate input values in Google Sheets?<\/h3>\nLet us consider an example to understand the usefulness of validating the input values. Below is a snapshot of a simple money returns calculator. The formula in cell B7 considers numeric inputs from cells B3 through B5 and accordingly produces output in the cell B7.\n\nExample<\/strong>
![\"Validate](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/validateinput1.png\")
\n\nSo far so good. What if somebody inadvertently tries to input text values into any of these input cells? Let's find out.\n\nExample<\/strong> ![\"Validate](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/validateinput2.png\")
\n\nThe formula just returned an error as it cannot process text values for calculations. This is where the data validation can help us restrict the type of input(s) that goes into any particular cell(s). Let us try and apply the validation to cells B3 through B5, so that we will only be able to enter numeric values. To do so, we select the corresponding cells and navigate to Data > Data validation<\/strong>.\n\nExample<\/strong> ![\"Validate](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/validinput3.png\")
\n\nWe configure the following criteria in the Data validation<\/strong> window pane that pops up, and click on the Save button.\n\nExample<\/strong> ![\"Validate](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/validateinput4.png\")
\n\nNow, let us try inputting a non-numeric value in one of the cells we applied data validation for.\n\nExample<\/strong> ![\"Validate](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/validateinput5.png\")
\nList of values<\/h4>\nThis Data validation functionality offers multiple ways to validate input values in Google Sheets. We could enforce only text or date values within cells. Or, we can even configure such that it allow only a list of values within the cells. We can accomplish the latter with the following configuration.\n\nExample<\/strong>
![\"Validate](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/validateinput6.png\")
\n\nNow, the cell B1 will now have a drop down that lists the values we've entered as part of the criteria. If we try and enter any other value, it rejects the input with an error. Please note that if the list of values is huge, we can also fetch them from a range of cells with predefined values.\n\nExample<\/strong> ![\"Validate](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/validatedinput7.png\")
\nCustom data validation<\/h4>\nHere's a demonstration that we could also customize data validation using a formula. Let us try and enforce a validation rule that an email should have both '@' and '.' (dot) within the value. Here's the formula that validates it.\n
=AND(NOT(ISERROR(FIND(\"@\",A7))),NOT(ISERROR(FIND(\".\",A7))))<\/strong><\/pre>\nExample<\/strong>
![\"Validate](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/validatedinput8.png\")
\n\nHaving set the above configuration, we try to input some random value that isn't a valid email address. And this is what happens.\n\nExample<\/strong> ![\"Validate](\"https:\/\/static.sheetgo.com\/wp-content\/uploads\/2017\/11\/validateinput9.png\")
\n\nAlternatively, we suggest you could also go with Google Forms where you can structure and validate the inputs in a better way. Here's a blog post<\/a> that can help you in this regard.","_et_gb_content_width":"","footnotes":""},"categories":[37],"tags":[39,28],"class_list":["post-7088","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google-sheets-features","tag-connections-t","tag-spreadsheets"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/7088","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/comments?post=7088"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/posts\/7088\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media\/33334"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/media?parent=7088"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/categories?post=7088"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/pt\/wp-json\/wp\/v2\/tags?post=7088"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}