Data Validation in Excel: 14-Chapter Course with Drop-Down Lists and Custom Rules

FREE COURSE · MODULE 6 OF 12

Data validation stops bad data before it reaches your formulas. Ten short chapters cover every rule type in the Data Validation dialog, from a simple drop-down list to formula-based rules, input messages and error alerts.

📚 14 chapters⏱ About 1 hour🖼 Screenshot for every step🎓 Beginner

Start Chapter 1 →

What is data validation in Excel?

Data validation is the feature on the Data tab that restricts what a user can type into a cell: only whole numbers, only dates in a range, only values from a list, or only entries that pass a formula you write. It also lets you show an input message before typing and an error alert after an invalid entry. Used well, it keeps trackers, forms and templates clean without any VBA.

Data Validation dialog box in Excel showing the Settings, Input Message and Error Alert tabs

Course curriculum

  1. Whole Number ValidationAllow only integers, with between, greater than and other operators.
  2. Decimal Number ValidationPercentages, prices and any value with decimals.
  3. List (Drop-Down) CreationThe most used rule: typed lists, lists from a range and dynamic lists.
  4. Date ValidationAccept only dates in a range, after today, or within a month.
  5. Time ValidationShift start and end times, appointment slots.
  6. Text Length ValidationFixed-length codes such as PIN, phone or employee ID.
  7. Custom (Formula) ValidationBlock duplicates, force upper case, restrict by another cell, and more.
  8. Input MessageShow instructions when the cell is selected.
  9. Error Alert: Stop, Warning and InformationChoose how strictly Excel reacts to invalid entries.
  10. Circle Invalid DataAudit existing data against your rules.
  11. Dependent Drop-Down List in ExcelCreate a dependent drop-down list in Excel so the second list changes with the first.
  12. Dynamic Drop-Down List in ExcelCreate a dynamic drop-down list in Excel that grows as you add items, using an Excel Table, the UNIQUE function in Excel 365, or OFFSET and COUNTA.
  13. Searchable Drop-Down List in ExcelBuild a searchable drop-down list in Excel with the built-in autocomplete in Excel 365, or a SEARCH helper column plus FILTER or INDEX for older versions.
  14. Copy, Find and Remove Data Validation in ExcelRemove data validation in Excel with Clear All, copy rules with Paste Special, find validated cells with Go To Special and stop paste from wiping rules.
Where you will use this: every data entry form, tracker and template on this site relies on drop-down lists and custom rules. Combine it with Conditional Formatting (next module) to highlight what the validation lets through.

See validation in real templates

Our data entry systems and trackers on NextGenTemplates use dependent drop-downs, custom rules and error alerts throughout. Open one and inspect the rules to learn faster.

Browse Excel trackers →

Frequently asked questions

Can validation stop values pasted into a cell?

No. Paste bypasses validation. Chapter 10 shows how to circle invalid data afterwards, and the VBA module shows how to block paste if you must.

How do I make a drop-down list that grows automatically?

Base the list on an Excel Table or use OFFSET/COUNTA. Chapter 3 covers both methods.

Does it work in Google Sheets?

Google Sheets has an equivalent Data validation feature with similar rule types. The concepts transfer directly.

Continue the learning path

← Previous: Excel Formulas100+ functions explainedAll modulesThe full Excel learning pathNext: Conditional Formatting →12 chapters of rules