With data validation in Google Sheets, you can eliminate incorrect entries by checking the data as soon as you enter it. This allows you to confirm text, numbers, dates, and other types of data with simple rules you set up.

Because there are several types of validation rules you can apply to your dataset, let’s walk through each one individually so you can pick the one that fits best.

Table of Contents
    How to Use Data Validation in Google Sheets image 1

    Validate Text

    With the text validation feature in Google Sheets, you can check for entries that contain, don’t contain, or are the same as specific text. You can also validate the cell for an email address or URL.

    For example, we’ll validate the text in our cell to make sure it contains “ID” for our product numbers.

    Select the cell or range and use Data > Data validation to open the sidebar and pick Add rule.

    How to Use Data Validation in Google Sheets image 2
    Online Tech Tips is reader-supported. We may earn a commission when you buy through links on our site. Learn more.

    Apply to range: Confirm the cell or range and use the icon on the right to add another cell range. Note: As shown below, you should use the sheet (tab) name with the cell reference or data range. Our sheet name is Dates.

    Criteria: Pick the text option you want to use and enter the text in the field beneath. For our example, we choose “Text contains” and enter “ID.”

    How to Use Data Validation in Google Sheets image 3

    Advanced options: To show and enter help text or choose a warning or rejection for invalid data, expand Advanced options, and mark the ones you want to use.

    How to Use Data Validation in Google Sheets image 4

    Select Done to apply the rule. You can then test your data validation rule. Input a valid entry and then an invalid entry to make sure the rule works as you expect.

    How to Use Data Validation in Google Sheets image 5

    Validate Dates

    For validating dates in Google Sheets, you have several options. You can check for a date on, after, before, or between as well as a valid date.

    As an example, you may want to confirm that the dates entered fall after a particular date, such as January 1, 2023, for your company’s 2023 financials.

    Select the cell or range and use Data > Data validation to open the sidebar and pick Add rule.

    Apply to range: Confirm the cell or range and optionally add another.

    Criteria: Pick the date option you want to use the drop-down menu or text field to pick or enter the corresponding date. For our example, we pick “Date is after,” choose “exact date,” and enter “1/1/23” in the field beneath.

    How to Use Data Validation in Google Sheets image 6

    Advanced options: Like the text validation above, you can expand this section to add help text and pick an invalid input action.

    How to Use Data Validation in Google Sheets image 7

    Select Done when you finish. You can then test your date validation by entering a valid and invalid date per your rule.

    How to Use Data Validation in Google Sheets image 8

    Validate Numbers

    If you want to validate numbers in Sheets, you can set up a rule that checks for those greater than, less than, equal to, between, and more.

    For this example, we want to confirm that the number entered is between 1 and 17 for parents entering their minor child’s age.

    Select the cell or range, pick Data > Data validation, and choose Add rule.

    Apply to range: Confirm the cell or range and optionally add another.

    Criteria: Pick the date option you want to use and enter the text in the field beneath. For our example, we pick “Is between” and enter “1” in the first field and “17” in the second.

    How to Use Data Validation in Google Sheets image 9

    Advanced options: Like the above validations, expand this section to add help text and pick an invalid data action.

    How to Use Data Validation in Google Sheets image 10

    Select Done to apply the rule. Test your number validation rule by entering both a correct and incorrect number.

    How to Use Data Validation in Google Sheets image 11

    Create a Drop-Down List

    Drop-down lists are another validation type you can use in Sheets. What’s different about these is that you can insert a drop-down list using the Insert or the Data menu. Either way, you’ll use the Data Validation sidebar to set up the list of items.

    Here, we’ll set up a drop-down list to choose a menu item.

    Do one of the following to add a drop-down list:

    • Select the cell and pick Insert > Dropdown in the menu. The sidebar will open.
    • Right-click the cell and pick Dropdown.
    • Select the cell, pick Data > Data validation, and choose Dropdown in the Criteria menu in the sidebar.
    How to Use Data Validation in Google Sheets image 12

    Enter your list items in the Option 1 and Option 2 fields and use the Add another item button to include more. You can also reorder the items using the grid icons on the left of each.

    Choose a color for each list item in the color palette drop-down box on the left.

    How to Use Data Validation in Google Sheets image 13

    Advanced options: Expand this section to show help text, choose an invalid data action, and pick the display style for the list.

    How to Use Data Validation in Google Sheets image 14

    Select Done when you finish, and you’ll see your drop-down list ready to go.

    How to Use Data Validation in Google Sheets image 15

    Insert a Checkbox

    Similar to the drop-down list validation above, you can add a checkbox to a cell using one of two options and customize the values in the Data Validation sidebar.

    Here, we’ll add checkboxes to add dishes to our meals.

    • Select the cell and pick Insert > Checkbox in the menu. The sidebar will open.
    • Select the cell, pick Data > Data validation, and choose Checkbox in the Criteria menu in the sidebar.
    How to Use Data Validation in Google Sheets image 16

    To use specific values for the checked and unchecked box statuses, mark the option Use custom cell values and enter those you want to use. For our example, we enter “Yes” and “No.”

    How to Use Data Validation in Google Sheets image 17

    Advanced options: Expand this section to show help text and choose an invalid input action.

    How to Use Data Validation in Google Sheets image 18

    Select Done to apply the checkbox rule to the cell or range of cells.

    Use a Custom Formula

    One more way to use data validation is with a custom formula in Google Sheets. This is a good option when none of the above preset rules apply. You can do things like make sure the cell contains text or limit the number of characters in the cell.

    As an example, we’ll set up the validation rule to check for text in the cell. If a number or date is entered, this invokes the invalid data action.

    Select the cell or range and pick Data > Data validation.

    Apply to range: Confirm the cell or range and optionally add another.

    Criteria: Pick “Custom formula is” and enter the formula in the field beneath. Using our example, we enter the formula “=ISTEXT(A2)” to check that cell A2 contains text.

    How to Use Data Validation in Google Sheets image 19

    Advanced options: Expand this section to enter help text and choose an invalid data action. For our example, we’ll mark the option to show help text and enter a custom message to display along with rejecting the input.

    How to Use Data Validation in Google Sheets image 20

    Select Done to apply the rule. Then, give your new validation rule a test by entering both valid and invalid data to make sure the formula works as expected. In the screenshot below, you can see our message for an invalid entry.

    How to Use Data Validation in Google Sheets image 21

    Edit or Remove Data Validation

    If after you set up a data validation rule, you want to change it or simply remove it, both are easy to do.

    Select the cell or range containing the validation and choose Data > Data validation in the menu to open the sidebar.

    Then, do one of the following:

    • To edit a rule, select it, make your changes, and choose Done to save it.
    • To remove a rule, hover your cursor over it and select the Delete (trash can) icon.
    • To remove every rule in the list, use the Remove all button.
    How to Use Data Validation in Google Sheets image 22

    With the data validation feature in Google Sheets, you can make sure your data is entered correctly. Whether you pop up a warning message or provide drop-down list options, you can save yourself the aggravation of checking for invalid data later.

    If you also use Microsoft applications, look at how to create a drop-down list in Excel too.

    Leave a Reply

    Your email address will not be published. Required fields are marked *