Google Sheets Data Validation: Create Drop-Downs and Rules

To add data validation in Google Sheets, select the cells, choose Data > Data validation, then add a rule that controls what people can enter. You can create a drop-down list, require a date or email address, add checkboxes, or reject entries that do not meet a condition. These steps are for Google Sheets in a web browser; Excel uses a different menu path and some options behave differently.

What You’ll Need

A Google account with access to the spreadsheet and permission to edit it.
The cells or column you want to control. Decide whether the rule should apply to a few cells or to a larger range that may grow.
For a drop-down sourced from a range, a list of approved choices in the spreadsheet. Put the choices in a clear, stable range, ideally on a separate sheet if they should stay out of the way.
If you want to test a rule without affecting important data, make a copy of the spreadsheet first using File > Make a copy.

Step-by-Step

Select the cells. Click the first cell, then drag across the range or use the name box above the sheet to enter a range such as B2:B100. Select the cells where people will enter values, not the header. Applying a rule to a whole column can be useful, but check whether the header or existing notes would also be affected.
Open the validation controls. In the menu bar, choose Data > Data validation. The data validation rules panel opens on the right. If there is already a rule for the selected cells, you may see it there; choose Add rule to create another one. The panel lists the range that the rule will affect, so verify it before continuing.
Choose what is allowed. In the Criteria section, select the kind of rule that matches your data. For a fixed list, choose Dropdown. For choices stored in cells, choose Dropdown (from a range). Other useful criteria include Checkbox, Date, Text is valid email, and Text is valid URL. The available controls change according to the criterion you choose.
Enter drop-down choices, if that is your rule. For Dropdown, type each choice into its own option field, then use Add another item to include more. For example, a task-status list might contain Not started, In progress, and Complete. Click the color control beside an option if you want to assign it a color. Keep choices distinct and easy to understand; two options that look almost identical can lead to inconsistent records.
Or point the rule to a range of choices. For Dropdown (from a range), enter or select the cell range containing the options, such as Lists!A2:A6. Use the range-picker button if it is shown, then select the source cells and confirm. A range-based list is helpful when other people need to maintain the options in one place. If you later add a choice outside the referenced range, expand the rule’s source range so that the new value appears in the drop-down.
Set the display style and invalid-entry behavior. For a drop-down, choose a display style such as Chip, Arrow, or Plain text if those choices are offered. Under Advanced options, choose whether invalid data should be rejected or should only show a warning. Reject input prevents an entry that does not meet the rule; Show a warning allows it but flags it. Add help text if people may need a reminder about the expected format.
Save the rule. Click Done. Return to the sheet and select one of the cells to confirm that the drop-down or other control appears. For a drop-down, open it and check that every choice is spelled correctly and easy to identify. Test an unapproved value too if you chose to reject invalid input.
Use a suitable rule for other common fields. For dates, choose Date and select a condition such as is valid date or a particular range. For an email field, choose Text is valid email. For a true-or-false field, choose Checkbox. Read the condition options in the panel carefully: a date rule that allows any valid date is different from one that restricts dates to a chosen period.
Use a custom formula only when a standard condition is not enough. For example, with numbers entered in A2:A100, select A2:A100, choose the custom-formula criterion, and use =AND(A2>=1,A2<=100) to allow whole-number values from 1 through 100 only if the formula is adjusted to test whole numbers; as written, it allows any numeric value in that range. The formula is evaluated relative to the first cell in the selected range, so use A2 rather than an absolute reference such as $A$2. Set invalid entries to Reject input if the range must be enforced. A custom formula can be harder for collaborators to maintain, so use a built-in criterion when it can do the job.
Edit or remove a rule when requirements change. Select a cell covered by the rule, open Data > Data validation, and choose the relevant rule in the panel. Update its criteria, range, or invalid-entry behavior and click Done. To remove a rule, open it and choose Remove rule. If multiple rules cover different cells, confirm the rule’s range before changing or deleting it.

For the custom-formula example, the assumed layout is values in A2:A100 and the rule applied to the same cells. The formula checks whether each cell is at least 1 and no more than 100; it does not check that the value is an integer. If you need integers only, use a whole-number criterion from the standard number conditions where available, or build and carefully test a custom formula for that requirement.

Common Mistakes

Applying the rule to the wrong range. A rule can appear to work in one cell while leaving other rows unrestricted. Check the range shown in the panel, especially after copying a rule or selecting nonadjacent cells.
Forgetting to extend a source list. If a drop-down uses a fixed source range, new choices outside that range will not appear. Update the validation rule’s range when the list grows, or set up a source range with room for expected additions.
Assuming a rule repairs existing values. Adding validation does not automatically clean up all data already in the cells. Review existing entries for duplicates, misspellings, or values that do not comply, and correct them separately.
Choosing a warning when you need a strict limit. Show a warning flags an unexpected entry but still lets it be entered. Choose Reject input when the sheet must not accept values outside the rule.
Using a custom formula with the wrong cell reference. A relative reference should normally start with the first cell in the selected range. An absolute reference can make every row test the same cell, producing confusing results.
Using unclear or duplicate choices. Options such as Yes, yes, and Y may represent the same answer but create different data. Standardize labels before adding them to the list.

Tips

Use a drop-down for a small set of approved labels, such as department, priority, or status. Use a range-based drop-down when the options need regular updates by a sheet owner.
Put source choices in a labeled area, and consider using a dedicated sheet named Lists. This makes it easier to find the source range when someone needs to update it.
Add a short help message that explains the expected entry, such as “Choose one status from the list.” Keep the message practical rather than repeating the column heading.
Choose Reject input for values that must be consistent, such as a controlled status field. A warning may be more appropriate when unusual entries are sometimes legitimate and should be reviewed rather than blocked.
Apply the rule to the expected working range, then verify what happens when rows are added. If a rule does not carry into a newly added area, extend its range or copy a correctly validated cell using a method that preserves the rule.
Use descriptive column headings and consistent formatting alongside validation. Validation limits entries, but it does not explain the meaning of a field or guarantee that the entered information is factually correct.
In Excel, the comparable feature is usually under Data > Data Validation on Windows, but its dialog and available settings differ from Google Sheets. These instructions and menu names refer to Sheets in a browser.

Troubleshooting

The drop-down arrow is missing. Select a cell you believe is covered, then open Data > Data validation and inspect the rules panel. Confirm that the cell is included in the rule’s range and that the criterion is a drop-down. If the rule is correct, click the cell; the menu may be easier to see when the cell is selected.

A choice does not appear in the list. If the rule uses Dropdown (from a range), check that the source cell contains the choice and that the source range includes that cell. Look for misspelled sheet names or an incorrectly selected range. If the source value is in a formula-driven list, confirm that the formula actually returns the expected value.

Sheets accepts a value you meant to block. Reopen the rule and check that If the data is invalid is set to Reject input rather than Show a warning. Check that the rule covers the cell where you tested it. Also inspect how the value was entered: existing data may remain after a rule is added, so do not assume validation has retroactively removed it.

Valid values are being rejected. Check the exact criterion and any boundaries. For a custom formula, confirm the reference points to the first cell in the validated range and that the formula is appropriate for blanks, decimals, or text. Test a few permitted and forbidden values in a spare copy before applying a complex rule to a working sheet.

The rule seems to change or disappear. Another rule or a copied cell may have changed the validation range. Select affected cells and review the rules panel. If several people edit the file, ask them to avoid replacing validated cells with pasted content until you have confirmed how the paste affects the rule. Restore the intended rule and test it again.

Frequently Asked Questions

Can I add a drop-down list in Google Sheets?
Yes. Select the cells, choose Data > Data validation, add a rule, and choose Dropdown or Dropdown (from a range). Enter the options, set the behavior you want, and click Done.

Can I make Google Sheets reject values outside a list?
Yes. Open Advanced options for the rule and set invalid data to Reject input. With Show a warning, people can still enter a value that is not on the list.

Can I use a list stored on another sheet?
Yes. Choose Dropdown (from a range) and select the source cells on the other sheet, for example Lists!A2:A6. Make sure the referenced range includes every choice you want to offer.

Does data validation remove invalid values already in the cells?
No. A rule controls entries going forward, but you should review and correct existing data separately. Check the cells after applying the rule if the sheet already contains information.

Can I validate dates, emails, or checkboxes?
Yes. The criteria include options for valid dates, valid email addresses, and checkboxes. Choose the criterion that matches the field, then set any available conditions and invalid-entry behavior.

Can I apply one rule to an entire column?
Yes. Select the relevant column or range and add the rule. Avoid including a header unless you want the rule to apply there, and confirm that newly used rows are covered by the range.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.