Data Validation and Dropdown Lists in Google Sheets and Excel
Practice4 minInnovialabs editorial team
In short
Data validation restricts what can be typed into a cell: a list of values, a number or date range, or a custom formula; set it to reject bad input with a clear message and shared tables stop collecting junk.
What data validation does
Data validation is a rule attached to a range of cells: only values that pass the rule are accepted. It solves the most common problem of shared spreadsheets — the same status written five different ways ("Done", "done", "Completed", "ready"), dates as text and amounts with currency signs. Filters, pivot tables and formulas then work without manual cleanup.
Where to find it:
- Google Sheets: Data → Data validation → Add rule.
- Excel: Data tab → Data Validation.
In both tools a rule consists of three parts: the range, the criterion and the reaction to invalid input.
Creating a dropdown list
Google Sheets. Select the range, open Data validation and choose a criterion:
- Dropdown — type the options directly and optionally give each a color, which makes statuses readable at a glance.
- Dropdown (from a range) — the options come from cells, for example a separate "Lists" sheet. Change the list once and all dropdowns update.
Excel. In Data Validation choose Allow: List. In Source either type values separated by commas or reference a range: =Lists!$A$2:$A$10. Turn the list into a table or a named range so new items are picked up without editing the rule.
Practical tip: keep all reference lists (statuses, managers, cities) on one hidden or protected sheet.
Dependent lists
A dependent list changes its options based on another cell: choose "Tashkent" in the city column, and the district column offers only Tashkent districts.
In Excel the classic approach uses named ranges and INDIRECT:
- Put each group of options in its own column and name the range exactly like the parent value (for example, a range named
Tashkent). Names cannot contain spaces, so use underscores. - For the parent column create a regular list.
- For the child column create a list with the source
=INDIRECT(A2), where A2 is the parent cell.
In Google Sheets INDIRECT is not accepted in the dropdown source, so a helper range is used: a formula like =FILTER(Lists!B:B, Lists!A:A = A2) outputs the matching options, and the dropdown takes values from that range. This works neatly for one input row or a form-like sheet; for many rows you need a helper range per row or a small Apps Script.
Number, date and text rules
Lists are only part of the story. Other useful criteria:
| Rule | Example |
|---|---|
| Whole number between | Quantity from 1 to 1000 |
| Decimal greater than | Amount greater than 0 |
| Valid date / date after | Deadline not earlier than today |
| Text length | Tax ID of an exact length |
| Custom formula | Uniqueness, format checks, conditions on other cells |
Custom formulas cover what standard criteria do not. A few examples for a rule applied from row 2:
No duplicates in column A: =COUNTIF($A:$A, A2) = 1
Phone format (Google Sheets): =REGEXMATCH(TO_TEXT(B2), "^\+998\d{9}$")
End date after start date: =D2 >= C2
The formula must return TRUE for valid values. Write it for the first cell of the range: relative references shift for the other rows automatically.
Error messages and help text
The default error says nothing useful. Configure your own:
- Google Sheets: in Advanced options choose "Reject the input" or "Show a warning", and enable help text that appears when the cell is selected.
- Excel: the Input Message tab shows a hint on selection, the Error Alert tab sets the style: Stop blocks the value, Warning and Information allow it after confirmation.
A good message says what is expected, not just that something is wrong: "Enter the date in DD.MM.YYYY format, not earlier than today" instead of "Invalid value".
Choose reject for fields that formulas and reports depend on (status, amount, date). Use warning where exceptions are legitimate.
How validation keeps shared tables clean
- Apply rules to the whole column in advance, not only to filled rows, so new entries are checked too.
- Protect lists and headers. In Google Sheets use protected ranges, in Excel sheet protection with unlocked input cells.
- Remember about pasting. In Excel a regular paste can overwrite a cell's validation; ask the team to paste values only. In both tools pasted data is not always rejected, so check existing data: Excel has Circle Invalid Data, Google Sheets marks invalid cells with a red corner.
- Review rules when the process changes. A new status added to the process but not to the list will make people bypass the dropdown.