IntermediateLesson 16 of 305 min read

Data validation and drop-down lists

Stop bad data at the door with drop-down lists, number and date rules, helpful messages and custom formulas, and learn the limits of validation.

In this lesson you will

  • Create a drop-down list
  • Set number, date and custom rules
  • Write helpful messages
  • Know the limits of validation

About 15 minutes

Stopping bad data before it starts

Beatriz shares a sales sheet with five colleagues. Within a week the Region column has North, north, Nort and N. A PivotTable then shows four regions that should be one, and the monthly total is wrong. Instead of cleaning up afterwards, she can stop the mistakes at the door with data validation: rules that decide what a cell will accept. It is one of the most useful features for shared sheets and forms.

A drop-down list in five steps

  1. Select the cells that need the list, such as A2:A100.
  2. Go to Data, Data Validation.
  3. Under Allow, choose List.
  4. In Source, type the choices separated by commas (North,South,East,West), or click the arrow and select a range of cells that holds them.
  5. On the Error Alert tab, write a helpful message, then click OK.

A small arrow appears beside each cell; people pick instead of typing. Keeping the list in a range of cells is better than typing it inside the rule, because you can add a new region later by adding a cell. Make the list range a Table or a named range so it grows by itself.

Other kinds of rules

Allow Use it for
Whole number Units, ages, quantities between limits
Decimal Prices, percentages, weights
Date A date after a start or within a range
Text length Codes that must be exactly 6 characters
Custom Any formula that returns TRUE for acceptable entries

Three messages

  • Input Message: a small tip that appears when the cell is selected, such as ‘Whole number from 1 to 500’.
  • Error Alert: what the person sees when they type something not allowed. Choose Stop to refuse it, Warning to let them choose, or Information to simply tell them.
  • Write messages in plain words that say what to do, not ‘Invalid entry’.

A real rule set

The program below builds a workbook with three rules: a list for Region, whole numbers from 1 to 500 for Units, and dates from 1 January 2026 on for the sale date. It saves the file, reloads it and prints the rules stored in it. The output is real:

import openpyxl
from openpyxl.worksheet.datavalidation import DataValidation
wb = openpyxl.Workbook(); ws = wb.active
ws["A1"], ws["B1"], ws["C1"] = "Region", "Units", "Sale date"
dv_list = DataValidation(type="list", formula1='"North,South,East,West"', allow_blank=False)
dv_list.errorTitle = "Pick from the list"; dv_list.error = "Choose North, South, East or West."
dv_num = DataValidation(type="whole", operator="between", formula1="1", formula2="500")
dv_num.promptTitle = "Units"; dv_num.prompt = "Whole number from 1 to 500"
dv_date = DataValidation(type="date", operator="greaterThanOrEqual", formula1="46023")   # 1 Jan 2026
for dv, rng in [(dv_list, "A2:A100"), (dv_num, "B2:B100"), (dv_date, "C2:C100")]:
    ws.add_data_validation(dv); dv.add(rng)
wb.save("rules.xlsx")

wb2 = openpyxl.load_workbook("rules.xlsx")
for dv in wb2.active.data_validations.dataValidation:
    print(dv.type, "|", dv.sqref, "|", dv.operator or "-", "|", dv.formula1, dv.formula2 or "")
list | A2:A100 | - | "North,South,East,West" 
whole | B2:B100 | between | 1 500
date | C2:C100 | greaterThanOrEqual | 46023

This shows the rules are saved with the file. The sandbox cannot click into a cell and type, so it did not test how Excel shows the drop-down or the alert on screen. Try it in your own copy.

Custom rules

Choose Custom and type a formula that must be TRUE. For example, to refuse duplicate codes in column A, select A2:A100 and use =COUNTIF($A$2:$A$100,A2)=1. To allow only text that begins with PEN-, use =LEFT(A2,4)=“PEN-“. Write the formula as if for the first selected cell; Excel adjusts it for the rest.

Dependent drop-downs, briefly

Sometimes the second list depends on the first: choose a country, then see only its cities. This uses named ranges and the INDIRECT function, and is a good next step once you are comfortable. Google Sheets and newer Excel also offer other approaches, so search for ‘dependent drop-down’ in your version when you are ready.

Limits you must know

  • Validation does not stop pasting. Pasting over a cell replaces the rule too, and invalid values can sneak in.
  • Existing data is not checked when you add a rule. Use Data Validation, Circle Invalid Data to find old entries that break it.
  • Anyone who can unprotect the sheet can change the rules.
  • A list from a range on another sheet works in modern Excel, and older versions need a named range.

Pair it with protection

Lock all cells except the input cells (Format Cells, Protection) and use Review, Protect Sheet. Then people can only type in the cells you chose, and the cells they type in only accept valid values. For shared data entry this combination is the closest thing to a form without writing code.

Practice

Build a simple order-entry sheet with a Product drop-down, a Quantity rule (whole numbers 1 to 100) and a Delivery date rule (not before today’s date). Add a helpful input message to each. Try typing a wrong value in each one and read the alert you wrote.

Practice answer

For a date no earlier than today, use Allow: Date, Data: greater than or equal to, Start date: =TODAY(). If the drop-down arrow does not show, make sure the In-cell dropdown box is ticked in the rule.

Common mistake

Assuming validation protects the sheet completely. Pasted values bypass it, so check key columns with a formula or Circle Invalid Data before reporting.

Check what you learned

Five quick questions. Pick an answer and you will see straight away whether it is right and why.

What does a drop-down list prevent?

  1. Saving
  2. Printing
  3. Sorting
  4. Typing variations such as north and Nort

People pick from fixed choices.

Which validation type suits ‘between 1 and 500’?

  1. Whole number
  2. List
  3. Date
  4. Custom only

Choose the between rule.

What does Circle Invalid Data do?

  1. Deletes them
  2. Hides them
  3. Marks existing entries that break the rules
  4. Sorts them

Rules are not applied to old data automatically.

Which action can bypass a validation rule?

  1. Saving the file
  2. Typing a valid value
  3. Choosing from the list
  4. Pasting a value into the cell

Pasting replaces the cell’s rule.

Why keep list choices in cells instead of typing them in the rule?

  1. It looks nicer
  2. You can add choices later
  3. Excel requires it
  4. It protects the sheet

Edit the cells and the list updates.

Summary

  • Validation stops bad entries before they happen.
  • Drop-down lists keep names consistent.
  • Write clear input and error messages.
  • Pasting bypasses rules; check with Circle Invalid Data.

Next lesson

Next: conditional formatting.

Questions

Can a drop-down allow typing as well?

Yes. Set the Error Alert style to Warning or Information and people can type something not in the list after a prompt.

How do I remove a validation rule?

Select the cells, open Data Validation and click Clear All.

Do drop-down lists work in Google Sheets?

Yes. Use Data, Data validation and choose a dropdown, with the same ideas.

Progress is saved in this browser.