Data validation in Google Sheets: rules, drop-down lists and custom formulas

Set up a rule step by step

The steps are the same for every rule. Example:

Suppose you’re working with a sales team dataset and want to make sure the Lead Score column only accepts values between 1 and 10.

Step 1: Right-click the column header where you want to apply the rule and choose Data validation. You can also go to the top menu and click Data > Data validation.

Step 2: In the sidebar that appears, click + Add rule to start setting up your validation.

Step 3: Check the Apply to range box to confirm the selected cells are correct. This is where your rule will be applied.

Step 4: Under Criteria, choose a condition such as Is between, and enter limits like 1 and 10.

Step 5: Open Advanced options and choose how invalid entries are handled: Show a warning keeps the entry and marks the cell, Reject the input refuses it. Click Done to apply the rule.

Data validation rule applied to the Lead Score column, restricting values to between 1 and 10. When an invalid entry is made, a warning message appears to guide the user.

Built-in validation rules

Creating a Drop-Down list with data validation

A drop-down list limits a cell to a set of options. Example:

Suppose you’re working with a team dataset and want to ensure only “Sales” or “Marketing” is entered under the Department column.

Steps to create the dropdown list:

Step 1: Highlight cells C3 to C10 in the Department column. Click Data > Data validation or right-click and choose Dropdown.

Step 2: In the setup panel, choose Drop-down as criteria and enter the allowed values: Sales and Marketing.

Step 3: Click Done, then test the dropdown to confirm it works.

A dropdown list applied to the Department column, allowing only “Sales” or “Marketing” as valid inputs.

Setting up number validation

Example:

Suppose you want to ensure the Monthly Target column only accepts values between $40,000 and $70,000.

Steps to set up a number validation:

Step 1: Select the cells in the Monthly Target column that contain or will contain numeric data, and click on Data > Data validation from the top menu.

Step 2: In the sidebar, set Criteria to is between and enter 40000 as the minimum and 70000 as the maximum value. Click Done to finish and activate the validation rule.

Step 3: Test the rule by entering a value like, 30000 to see if the error message appears for numbers outside the allowed range.

This ensures only valid monthly target values are entered, keeping your data accurate and within the set limits.

Applying text validation

Example:

Suppose you want to make sure all entries in the Email column are valid email addresses.

Steps to apply text validation:

Step 1: Select the cells in the Email column where email addresses will be entered. Click on Data > Data validation from the top menu to open the sidebar.

Step 2: Under Criteria, choose the “Text is valid email” option from the dropdown options.

Under Advanced options, tick Show help text for a selected cell and write a hint such as “Please enter a valid email address”. To block wrong entries completely, select Reject the input under If the data is invalid.

Test the rule by typing an incorrect email. With Reject the input selected, the entry is refused and the help text is shown:

The rule checks the shape of the address, not whether the mailbox exists.

Setting up date validation

Example:

Suppose you want to make sure all entries in the Join Date column are valid dates, not random text or incorrectly typed values.

Steps to apply date validation:

Step 1: Select the cells in the Join Date column where team members’ joining dates are going to be entered and click Data > Data validation to open the rule panel.

Step 2: Under Criteria, choose “Is valid date” from the dropdown options. Click Done to save and apply your rule to the selected cells.

Test it by entering something like “Next Monday” or “Joined last year” to see if it’s blocked.

A calendar will appear when you click the cell, allowing you to select a date in the correct format.

This ensures that only valid calendar dates are accepted, keeping your date-related data clean and consistent.

Using a checkbox for binary validation

A checkbox stores TRUE when ticked and FALSE when not, so formulas such as COUNTIF and FILTER can use the column directly. Example:

Suppose you want to track if an employee is active using the Active column in your dataset.

Steps to apply checkbox validation:

Step 1: Select the cells in the Active column where you want to allow only checkbox input. Go to Data > Data validation from the top menu.

Step 2: Under Criteria, select the Checkbox from the dropdown options.

Step 3: Choose Reject input to prevent users from typing anything else, and click Done to apply the rule.

Now you can easily mark which employees are currently active.

This ensures that only binary input is accepted, keeping employee status entries consistent and easy to use.

Custom formulas and conditional validation

Implementing custom formula validation

A custom formula covers every rule the built-in criteria do not. The formula is written for the first cell of the range and has to return TRUE for a valid entry; Sheets adjusts the reference for the other cells.

Example:

Suppose you want to ensure the values entered in the Monthly Target column are always valid positive numbers – no negative amounts, text, or blanks.

Steps to apply custom formula validation:

Step 1: Highlight the cells in the Monthly Target column where you want the rule applied and click Data > Data validation to open the validation settings panel.

Step 2: Under the Criteria dropdown, select Custom formula is to create a custom logic rule. In the formula field, enter:

=AND(ISNUMBER(C3),C3>=0)

Here’s what each parameter means:

• ISNUMBER(C3): Checks that the input is a number, not text or blank.
• C3>=0: Ensures the number is zero or higher.
• AND(…): Combines both checks, so the value must be numeric and non-negative.

Step 3: Choose Reject input to block any values that don’t match the rule, like negative numbers or text. Click Done to apply the rule and close the settings panel.

Test the rule by entering a negative number (like -1000) or text; it should show an error message.

This validation keeps your sales targets accurate and prevents invalid data from affecting reports or forecasts.

Using conditional formatting with data validation in Google Sheets

Validation controls what goes into a cell; conditional formatting draws attention to what is already there. The two work well on the same range.

Example:

Suppose you want to highlight active employees with a Lead Score below 5 in your dataset.

Steps to apply conditional formatting with data validation:

Step 1: Select the full range of rows where the Lead Score and Active status are recorded, and click on Format > Conditional formatting from the menu to open the formatting panel.

Step 2: Under “Format cells if,” choose Custom formula is and enter the following formula:

=AND($C2=TRUE, $D2<5)

Here’s what each parameter means:

• $C2=TRUE Checks if the value in cell C2 is TRUE.
• $D2<5 Ensures the value in cell D2 is less than 5.
• AND(…): Combines both checks, so the condition is only TRUE if both are met.

Step 3: Choose a formatting style, such as red fill or bold text, to make underperforming active employees stand out. Click Done to apply the rule to the range.

This method makes it easy to catch low lead scores at a glance while maintaining clean, validated data.

Conditional data validation with FILTER

A drop-down can show only the options that meet a condition. The rule itself cannot filter, so the filtering is done by a formula in a helper range, and the drop-down reads that range.

Example:

Suppose you want to create a dropdown in the Assignee column that only shows employees with Active = TRUE, so inactive employees can’t be selected for project tasks. The Main Sheet contains your complete employee data, including the Active status column.

Steps to apply conditional data validation with the FILTER function:

Step 1: In a helper Sheet named “Filtered Data”, use the following formula to list only active employees:

=FILTER(B2:B10, C2:C10=TRUE)

Here’s what each parameter means:

• B2:B10: This is the range of employee names that we want to filter.
• C2:C10=TRUE: This checks if each corresponding employee in the same row is marked as active (TRUE).
• FILTER(…): Returns only the employee names from B2:B10 where the active status in C2:C10 is TRUE.

Step 2: Select the filtered list, and name the range using the Name Box as ActiveList.

Go to the “Assignee” column in the main sheet where you want to apply the dropdown.

Step 3: Click Data > Data validation; for criteria, choose Dropdown (from a range) and enter the named range: ActiveList

Step 4: Click on Done and click on the dropdown to assign the task.

Only employees marked as active appear in the list. When a status changes in the main sheet, FILTER updates the helper list and the drop-down follows.

Drop-down options from another spreadsheet with IMPORTRANGE

Validation rules are not copied between files. What you can share is the list of allowed values: import it with IMPORTRANGE and use the imported range as the source of a drop-down, so that every file offers the same options.

Example:

Suppose you have a master dataset in another Google Sheet file, in a sheet named Main Sheet, and you want to pull the data from there into your current sheet.

Steps to use the IMPORTRANGE function with validated data:

Step 1: Open the sheet where you want to display the department data and enter the formula below in a blank cell.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/spreadsheet_id/edit", "MainSheet!B2:C")

Here’s what each parameter means:

• The URL: the address of the source spreadsheet. Replace it with your own.
• MainSheet!B2:C: Range of data to import.
• IMPORTRANGE: Pulls the data into your current sheet.

When prompted, click Allow access to connect both sheets securely.

Step 2: The data from the Main Sheet will now appear in the new file.

Select the imported column as the range of a Dropdown (from a range) rule, and the list in this file follows the master list in the other one.

Creating dependent dropdown lists

A dependent drop-down changes its options according to the choice made in another cell: pick a department, and the next list shows only its employees.

Dependent dropdown lists using the INDIRECT function

INDIRECT turns a text into a reference. If each list of options is a named range, and the names match the options of the first drop-down, INDIRECT returns the list that belongs to the selected option.

Example:

We’ll select a Department in one cell and use INDIRECT to display a list of matching employees in the next cell using named ranges.

Steps to create a dependent dropdown using INDIRECT:

Step 1: In one sheet (Sheet1 in this example), set up the source data for your dropdowns. Use department names like “Sales” and “Marketing” as column headers in cells B2 and C2. Under each header, list the employees from your main data who belong to that department. This table will be used to create the dependent dropdowns using the INDIRECT function.

Step 2: Select the list under each department (e.g., B3:B for Sales) and name the range exactly as the header (e.g., Sales, Marketing) using Data > Named ranges.

Step 3: In the tab where you want to place the dependent dropdown, click Insert > Dropdown on the respective cell and add the criteria as “Drop-down (from a range)”, then specify the Sheet and the departments (should match exactly with how you named the ranges).

Click Done to create the Department dropdown.

Step 4: In a free cell, enter the formula that reads the department selected in B3:

=INDIRECT(B3)

The formula returns the named range with that name, the employees of the selected department, as a list below the cell. Use that list as the range of a second Dropdown (from a range) rule.

The names have to match exactly, and a named range cannot contain spaces, so this method suits short, stable lists of categories.

Dynamic dependent Drop-Down list using XLOOKUP

XLOOKUP does the same without named ranges: it finds the selected option in the header row of the source table and returns the column underneath.

Example:

Suppose you’re building a form where a user first selects a department, like ‘Sales’ or ‘Marketing’. Based on that choice, the next dropdown should only display employees who work in the selected department.

Steps to create a dynamic dependent dropdown using XLOOKUP:

Step 1: In one sheet (Sheet1 in our example), organize your source data by adding department-based columns. Take the needed departments like “Sales” and “Marketing” as column headers, and list the corresponding employee names under each department. This structured layout will be used to create dynamic dropdown options later.

Step 2: In the sheet where you want the dropdown to appear, click Insert>Dropdown. Then choose Dropdown from a range under Criteria, and enter the sheet and column range where department names are stored (Sheet1!B2:C2 for our example)

Click Done to create the Department dropdown.

Step 3: In a different cell (in the same sheet), enter the following formula:

=TOCOL(XLOOKUP(B3, Sheet1!B2:C2, Sheet1!B3:C6), 1)

Here:

• B3: The cell with the department drop-down
• Sheet1!B2:C2: The header row with department names
• Sheet1!B3:C6: The lists of employee names under each department
• TOCOL(…, 1): Returns the result as one column and leaves out the empty cells

This will return a dynamic list of employees based on the selected department in B3.

Step 4: Go to the next cell, where you want the employee dropdown. Click Insert > Dropdown, choose Dropdown from a range, and type the range that contains the dynamic employee list returned by the XLOOKUP formula.

Click Done to apply the dependent dropdown.

This setup keeps dropdowns connected and auto-updated based on your selections.

Enforcing character restrictions using REGEXMATCH in data validation

For text that has to follow a pattern, such as an email address at one of the company domains, use REGEXMATCH in a custom formula.

Example:

Suppose you want to restrict entries in the Email column to only accept addresses ending with @salescorp.com, @brandboost.co or @marketlead.net.

Steps to Apply REGEX-Based Validation on the Email Column:

Step 1: Select the range of cells under the Email column in your dataset click on Data in the top menu, and choose Data validation. In the Criteria section, select Custom formula is, and enter the following formula:

=REGEXMATCH(C3,"@(salescorp\.com|brandboost\.co|marketlead\.net)$")

Here is what each parameter means:

• REGEXMATCH(C3, …): Checks if the value in C3 matches the given pattern.
• @(salescorp\.com|brandboost\.co|marketlead\.net)$: Ensures the email ends with one of the three domains.
  • | means OR
  • \. escapes the dot, as dots have special meaning in regex
  • $ ensures the email ends with the given domain

Step 2: Click Done to apply the rule. Test it by entering an invalid email, like test@gmail.com, and you’ll get a validation warning.

Only addresses at the listed domains are accepted. The patterns are explained in the guide to REGEX functions in Google Sheets.

Problems with data validation and how to fix them

⚠️ Error: “The data you entered in cell … violates the data validation rules set on this cell.”

✅ Solution: The entry does not meet the rule and the rule is set to reject. Check the value for extra spaces, a number typed as text or an option that is not in the list. If the value should be allowed, edit the rule under Data > Data validation.

⚠️ Error: A cell shows a red corner although nothing was rejected.

✅ Solution: The rule is set to Show a warning, or the value was in the cell before the rule was added. A rule does not clean existing data: fix those cells, or switch the rule to Reject the input for new entries.

⚠️ Error: A custom formula rule accepts or rejects the wrong rows.

✅ Solution: The formula has to refer to the first cell of the range it is applied to. A rule on C3:C100 uses C3, not C2 and not the whole range. For a whole-row condition, fix the column with a dollar sign, as in =$C3>0.

⚠️ Error: A drop-down from a range shows empty or outdated options.

✅ Solution: The range no longer matches the list. Point the rule to the whole column of the helper list, or to a list built with FILTER, so that new options are picked up.

⚠️ Error: The dependent drop-down shows the options of the wrong category.

✅ Solution: The helper formula reads another cell than the one with the first drop-down, or the named range does not match the selected option letter for letter.

Tips for rules that people can live with

• Add help text. Under Advanced options, write what the cell expects. A rule that rejects input without saying why gets worked around.
• Reject only where it matters. Use Reject the input for values that feed calculations, and a warning for fields where exceptions are legitimate.
• Keep option lists in one place. A tab with the lists behind all drop-downs is easier to update than options typed into each rule.
• Test with wrong values. Before sharing the sheet, try entries that should fail as well as ones that should pass.

Related Google Sheets functions

Functions and features that are often used with data validation, each with its own guide:

• FILTER: Builds the helper lists behind conditional drop-downs.
• XLOOKUP: Finds a value and returns the matching row or column.
• REGEX functions: Pattern rules for text.
• IMPORTRANGE: Shares a list of allowed values between spreadsheets.
• IF: Conditions for custom formulas.
• Remove duplicates: Finds and prevents repeated entries.
• Slicers: Adds on-sheet filter controls for pivot tables and charts.
• Exploratory data analysis: Walks through summarising and visualising a dataset before building reports on it.

Validation guards what people type, not what gets pasted in

A validation rule checks entries made by hand. Most of the data in a reporting spreadsheet does not arrive that way: it is exported from a CRM or an ad platform and pasted in by the thousand rows, and no drop-down list stands between that export and the report built on it.

The OWOX Data Marts extension for Google Sheets covers that half. A data analyst publishes a data mart (a table or an SQL query in your data warehouse) and makes it available for reports. In Sheets you open the extension, pick the data mart, tick the columns you want and run it. The rows land in a tab, ready for your formulas.

What changes for the person who maintains the sheet:

• Refresh replaces re-export. Refresh current report in the extension menu re-runs the same pull, and a scheduled refresh keeps the tab current without anyone touching it.
• The source is written on the sheet. The header cell of an imported tab carries a note with the data mart it came from, the time of the import and a link to the report.
• No SQL and no warehouse access needed. The analyst decides which data marts and columns are available. You choose from that list.

The extension does not write or change the analyst’s SQL. The checks on that data, types, allowed values and duplicates, are made there, before the rows reach the sheet, and data validation is left to do its real job on the cells people fill in themselves. The extension comes with the OWOX Cloud plans of OWOX Data Marts; spreadsheet reporting describes both ways data reaches a sheet.

Google Sheets TipsUNIQUE function in Google Sheets: syntax, examples and errorsOctober 2, 2026→Google Sheets TipsHow to link cells, tabs and sheets in Google SheetsOctober 2, 2026→Google Sheets TipsHow to average in Google Sheets: AVERAGE and AVERAGEIFOctober 2, 2026→

See all articles →