Managing and Formatting Data
Beyond the Home tab
This domain tests your control over how data is displayed, validated, and cleaned - often where the underlying value stays the same but the presentation changes.
Custom number formats. A format has up to four sections separated by semicolons: positive;negative;zero;text. Use # (optional digit), 0 (forced digit), ? (space-padded digit for alignment), , as a thousands separator, %, and @ as the text placeholder. Conditional sections like [Red][<0] and colour codes are allowed. A format such as 0.0,,"M" divides by a million. Remember: number formats never change stored values, which is exactly the point of many distractors.
Conditional formatting. Formula-based rules must be written relative to the top-left cell of the applied range and must return TRUE or FALSE. Enable Stop If True to prevent lower-priority rules from firing on the same cell. Absolute references (e.g., $B2) are what let a rule look at a single control column for every row.
Data validation. Types include Whole Number, Decimal, List, Date, Time, Text Length, and Custom. The Input Message appears on selection; the Error Alert has three styles - Stop (rejects), Warning (offers Yes/No), and Information (offers OK/Cancel). Circle Invalid Data highlights existing violations.
Cleaning and reshaping. Remove Duplicates, Text to Columns, Flash Fill (Ctrl+E), Advanced Filter (criteria range with matching headers; same row = AND, different rows = OR), and Subtotals (which require sorted data and create outline groups).
Rule of thumb: for a formula-based conditional format, select the range starting from the active cell and reference only the first row with mixed references, e.g. =$D2>100.