How to Add Data Validation Rules in Google Sheets

Manually cleaning up inconsistent data in your spreadsheets can feel like a never-ending task. One person writes "usa," another enters "United States," and a third types "US" - all for the same category, creating a reporting nightmare down the road. This article will show you how to use Google Sheets' Data Validation feature to stop messy data at the source by setting simple, clear rules for what can be entered into your cells.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is Data Validation in Google Sheets?

Think of data validation as a bouncer for your spreadsheet cells. You set the rules, and it only lets the right "kind" of data in. It's a proactive way to ensure the information going into your sheets is clean, consistent, and accurate from the very beginning. This saves you from the tedious, error-prone job of manually correcting entries later.

Using data validation is critical for any collaborative spreadsheet or Master Sheet used for reporting. It's especially useful for:

• Creating dropdown menus: Limit choices to a predefined list, like project statuses ("To Do," "In Progress," "Complete") or sales regions ("North," "South," "East," "West").
• Controlling data formats: Ensure cells contain only dates, a valid email address, numbers within a certain range, or even whole numbers.
• Preventing errors: Stop users from entering text into a numerical column or entering a duplicate ID in a list.

By enforcing consistency upfront, you make your data far more reliable for formulas, pivot tables, and BI tools that connect to your sheet.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

How to Add Basic Data Validation Rules

The most common and immediately useful data validation rule is creating a dropdown list. This simple feature drastically improves data consistency. Let's walk through creating one to manage a "Status" column in a project tracker.

Creating a Dropdown List from a Range

For this example, imagine you have a project task list and want a "Status" column (Column C) where users can only select "Not Started," "In Progress," or "Complete."

Step 1: Create Your List of Options

First, create the list of acceptable values. It's best practice to put this list in a separate tab, often called 'Lists', 'Config', or 'Data'. This keeps your main sheet clean and makes it easy to update the options later.

1. Create a new tab in your Google Sheet and name it "Lists."
2. In column A of the "Lists" tab, type your status options, one per cell:

Step 2: Apply the Data Validation Rule

Now, go back to your main project tracking sheet.

1. Select the cells where you want the dropdown to appear. You can select a single cell, like C2, or an entire range, like C2:C100.
2. Go to the menu and click on Data > Data validation.
3. This will open the "Data validation rules" sidebar on the right. Click + Add rule.

Step 3: Configure the Rule

In the sidebar, you'll see several options to configure:

1. Apply to range: This should already be filled with the cells you selected (e.g., 'Sheet1'!C2:C100).
2. Criteria: Click the dropdown menu here. This is where you define the type of rule. By default, it's "Dropdown."
3. Select Dropdown (from a range).
4. A box will appear below it. Click the grid icon to select the data range. A small window will pop up.
5. Navigate to your "Lists" tab and select the cells containing your options (in our case, A1:A3). Click "OK." The text box should now contain 'Lists'!A1:A3.

Now your dropdown is technically set up. When you click on any cell in C2:C100, a small dropdown arrow will appear with your three status options. But let's refine it further with the advanced options.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step 4: Refine with Advanced Options

Inside the Data validation rules sidebar, click on "Advanced options."

• If the data is invalid: You have two choices. "Show a warning" will flag the cell with a red corner but still allow the invalid entry. "Reject the input" will completely block any entry that isn't from your dropdown list. For forcing consistency, Reject the input is almost always the better choice.
• Show help text for a selected cell: Check this box and customize the message. A good message guides the user, for example: "Please select a status from the dropdown list." Now, when a user clicks on a cell, this instructional text will pop up, which is incredibly helpful.

Click Done. Your rule is now active! Go ahead and test it out in column C.

Beyond Dropdowns: Exploring Different Validation Criteria

Dropdown lists are just the beginning. The "Criteria" menu is packed with other powerful rules to keep your data pristine.

Numbers

Use this to ensure numerical fields are formatted correctly. Select "Number" from the criteria dropdown and you'll get further options like:

• between / not between: Perfect for a rating scale. Set it to "between 1 and 10" to ensure consistent review scores.
• greater than / less than or equal to: Useful for inventory counts ("greater than or equal to 0").
• is equal to / is not equal to: For specific numerical codes or identifiers.

Text

This allows you to check a text string for certain patterns. Common uses include:

• is a valid email: A one-click way to ensure any email addresses entered are in the correct format (e.g., user@example.com). It won't verify if the email actually exists, but it will catch typos like user@example or user.example.com.
• contains / does not contain: A great choice for product SKUs or IDs that must contain a specific prefix, like "PROD-".
• is a valid URL: Checks that the entry is a properly formatted web link.

Date

Essential for timelines, project due dates, and logging timestamps. The criteria here let you enforce rules like:

• is a valid date: The simplest rule, it just makes sure the entry is a recognized date format.
• is before / is on or after: Perfect for ensuring a "Project Start Date" is set for today or a future date ("is on or after" > today's date).

Checkbox

This rule turns a cell into a visual checkbox. The checked state has an underlying value of TRUE, and the unchecked state has a value of FALSE. It's a much clearer way to manage binary options (Yes/No, True/False, On/Off) than typing them manually.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Using Custom Formulas for Advanced Rules

The "Custom formula is" option is where data validation becomes truly powerful, letting you write your own logical tests. The formula you enter must evaluate to either TRUE (the data is valid) or FALSE (the data is invalid).

A brilliant example is creating a rule to prevent duplicate entries in a column - something you often need for customer IDs, invoice numbers, or unique coupon codes.

Example: Prevent Duplicate Entries

Let's say you want to ensure every entry in Column A (e.g., "Invoice ID") is unique.

1. Select the entire column where you want to prevent duplicates (click the 'A' at the top of Column A).
2. Go to Data > Data validation and click + Add rule.
3. In the "Criteria" dropdown, select Custom formula is.
4. In the formula box that appears, enter the following formula:
5. In "Advanced options," select Reject the input and add some helpful text like, "This Invoice ID already exists. Please enter a unique ID."
6. Click Done.

How this formula works:

• COUNTIF is a function that counts how many times cells within a range meet a given criteria.
• A:A tells it to check the entire range of Column A.
• A1 represents the value being entered into the currently selected cell. Google Sheets is smart enough to adjust this logic for every cell in the range you selected (A2, A3, etc.).
• =1 checks if the count is exactly one. If it is, the entry is unique and accepted (TRUE). If you try to enter an existing ID, the count becomes 2, the formula returns FALSE, and your designated help text will appear before rejecting the input.

Final Thoughts

Mastering data validation is one of the most effective ways to move from reactive data cleaning to proactive data management. By setting clear rules for your data from the start, you ensure consistency, reduce errors, and build a reliable reporting foundation that you and your team can trust right inside Google Sheets.

But even with a perfectly organized Google Sheet, getting the full picture of your business often involves hours of manually pulling data from tools like Google Analytics, Shopify, Facebook Ads, and your CRM. We built Graphed to automate that entire process. You can connect your marketing and sales platforms in minutes, then use plain English to build real-time dashboards and ask questions about your performance. No more weekly CSV downloads, just a live, unified view of what's fueling your growth.