Validation, Protection and Output
Validation: Controlling What Can Be Entered
What validation does
Data validation restricts what can be typed into a cell. The main types are Whole Number, Decimal, List, Date, Time, Text Length and Custom (a formula that must return TRUE or FALSE). Each rule can have an input message shown on selection and an error alert with three styles: Stop (rejects entry), Warning (asks for confirmation) and Information (accepts with a comment). Choose Stop unless you want soft enforcement.
Building the rules
Set the Allow type, then the operator (between, not equal to, greater than, and so on) and the limits. For a dropdown list, choose List and either type comma-separated values or point to a range or named range - the reference should be absolute. A dependent list uses INDIRECT against named ranges. A custom formula is written relative to the top-left cell of the applied range, for example =COUNTIF($A$2:$A$100,A2)=1 to forbid duplicates.
Traps worth memorising
Validation only governs direct typing: pasting, filling, dragging or importing data bypasses it, and changing validation afterwards does not re-check existing entries. 'Ignore blank' and 'In-cell dropdown' are separate checkboxes. Validation is not formatting and not protection: a cell can still be formatted conditionally while holding invalid legacy data.
Finding and auditing
Use Circle Invalid Data to highlight cells that already break the current rule, which is also how you prove a rule to an examiner. When copying a validated cell, the rule travels with Paste All but not with Paste Values.
Worked example
To accept only dates from today to 31 December of the current year: Allow = Date, Data = Between, Start = =TODAY(), End = =DATE(YEAR(TODAY()),12,31), alert style Stop with a clear message telling the user the accepted range.
Protection and Output: Securing and Printing the Workbook
Three different locks
Candidates must separate them clearly. Cell locking is a property of cells (Format Cells > Protection) that has no effect until the sheet is protected; the default is locked and visible. Sheet protection then decides which actions stay allowed - selecting cells, sorting, filtering, formatting rows or columns - and optionally takes a password. Workbook protection restricts structural changes such as adding, deleting, hiding or renaming sheets, or fixes the window layout. File-level protection is a password to open or modify, which actually encrypts the file.
Practical protection workflow
Select the input cells you want users to edit, unlock them, then protect the sheet with the appropriate allowances. Hide rather than lock formulas you want concealed. Traps: protection passwords are weak security and can be removed, 'Mark as Final' is only a flag, and protecting a sheet does not protect the file from being opened.
Preparing output
Print settings convert a spreadsheet into a document. Set the print area for the exact block to print, and print titles (rows or columns to repeat) for every page of a long list - these are different features and a common confusion. Then check orientation, paper size, margins, scaling (Fit to page), page breaks in Page Break Preview, headers and footers with page numbers, and gridline and heading options.
Showing formulas
For an audit printout, toggle Show Formulas (Ctrl+`) so formulas are displayed instead of results, and widen columns so nothing is truncated. Export to PDF when layout must be fixed.
Worked example
Print a 300-row price list with the header row repeated: File > Print > Page Setup > Sheet tab, set Rows to repeat at top to $1:$1 and Print area to $A$1:$F$301, landscape orientation, fit to 1 page wide.