Data Validation Rules in Spreadsheets
Learn how data validation rules work in Excel and Google Sheets, and how to view, change, manage or remove them for dropdown lists and error alerts.
Contents
- Finding validation rules in Excel
- Changing and removing rules in Excel
- Managing rules in Google Sheets
- Questions about spreadsheet data validation rules
- How do I add a dropdown list with data validation?
- Do validation rules check data that was already in the cells?
- Can pasted data get past a validation rule?
- How can data validation help an inventory tracker for press-on nails?
Data validation rules in spreadsheets limit what can be typed into a cell or a cell range. Excel and Google Sheets both let you set rules for numbers, dates, text length or dropdown lists. A rule can show input messages before typing and error alerts when an entry breaks the rule. The sections below explain where each program keeps its rules and how to edit or delete them.
Finding validation rules in Excel
Excel shows its data validation rules one cell at a time through the Data Validation dialog box. Select a cell, open the Data tab and choose Data Validation in the Data Tools group. To see every validated cell on a sheet, use Find and Select on the Home tab. Choosing Data Validation there highlights all cells that carry a rule, so none stay hidden.
Changing and removing rules in Excel
Changing an Excel data validation rule starts with selecting one cell that already carries the rule. Open the Data Validation dialog, edit the settings and tick the box that applies changes to matching cells. The Clear All button in the same dialog removes the rule from the selected cells. Excel offers no rules manager for validation, unlike the one built for conditional formatting.
Managing rules in Google Sheets
Google Sheets keeps its data validation rules in a side panel opened from the Data menu. The panel lists every rule on the sheet with its cell range and criteria. Click a rule to edit its criteria, help text or the choice between a warning and rejecting input. A delete icon beside each rule removes it while the values already typed stay in the cells.
Questions about spreadsheet data validation rules
How do I add a dropdown list with data validation?
A dropdown list is a data validation rule set to List in Excel or Dropdown in Sheets. You type the choices or point the rule at a cell range that holds them. Pointing at a range lets you update the list by editing those cells.
Do validation rules check data that was already in the cells?
No, data validation rules check new entries and do not change values typed before the rule existed. Excel can mark such old entries with the Circle Invalid Data command on the Data tab. Google Sheets flags invalid cells with a small red triangle in the corner.
Can pasted data get past a validation rule?
Yes, pasting into a validated cell can overwrite its data validation rule in Excel and Google Sheets. Pasting values only keeps the rule in place but may still let an invalid entry through. Checking the sheet after a large paste shows whether any entries break the rules.
How can data validation help an inventory tracker for press-on nails?
Data validation keeps an inventory tracker for press-on nails consistent by limiting each column to set entries. A dropdown list for nail shape, such as coffin, almond or square, stops spelling variants. A whole number rule on the quantity column rejects text and negative counts. Clean entries also help filters sort designs and conditional formatting flag low stock.