Data validation
Data → Data Validation restricts what a cell accepts: whole numbers in a range, dates, text length, or a List for a dropdown.
Source a list from a Table column so it grows automatically, or use a named range. Add an Input Message to explain the expectation and an Error Alert to say what went wrong.
Validation only fires on typing — pasting can bypass it. Use Circle Invalid Data periodically to catch what slipped through.
Protection
Cells are Locked by default, but locking only takes effect once you protect the sheet. The workflow is: unlock the cells people should edit, then Review → Protect Sheet.
Protect Workbook prevents adding, deleting or renaming sheets. Neither is real security — passwords are trivially removable — but both prevent accidents, which is the actual purpose.
Auditing formulas
Formulas → Trace Precedents shows which cells feed a formula; Trace Dependents shows what depends on it. Evaluate Formula steps through a calculation one operation at a time, which is the fastest way to find where a long formula goes wrong.
Common errors: #REF! means a referenced cell was deleted, #VALUE! a type mismatch, #DIV/0! division by zero, #N/A a lookup found nothing, #NAME? a misspelled function or undefined name.
Ctrl+` toggles showing all formulas instead of results — invaluable when auditing an inherited workbook.
Habits that prevent breakage
Never hard-code a constant inside a formula; put it in a labelled cell and reference it. Keep raw data, calculations and presentation on separate sheets. Use Tables so ranges grow. Avoid merged cells — they break sorting, filtering and structured references.
Document non-obvious logic in a notes sheet. The person maintaining the workbook in a year is probably you, without the context.