Manage and format data
What Domain 2 tests
This domain tests whether you can shape and constrain data: custom number formats, validation rules, formula-driven conditional formatting, advanced filters, multi-level sorts, grouping, and subtotals.
Core rules
- Custom number formats have up to four sections: positive;negative;zero;text. Codes include 0 (required digit), # (optional digit), ? (space placeholder), comma (thousands scaling), % and [Red]. Dates use m, d, y, h, mm, ss, AM/PM. Literal text goes in quotes. Formatting never changes the stored value.
- Data validation types include whole number, decimal, list, date, time, text length, and custom formula. Set Input Message and Error Alert (Stop, Warning, Information). A list can point to a range, a named range, or delimited text.
- Conditional formatting formulas must return TRUE/FALSE and are written relative to the top-left cell of the Applies To range. Use Stop If True and rule priority to control overlap.
- Advanced Filter uses a criteria range: conditions on one row are AND, conditions on separate rows are OR. It can filter in place or copy to another location and can extract unique records.
- Sort supports multiple levels, custom lists, cell or font color, and left-to-right orientation. Subtotals require sorted data and create outline groups.
Worked example
Highlight whole rows in A2:H100 where column D says Overdue and column F is before today. Select A2:H100, New Rule > Use a formula, and enter =AND($D2="Overdue",$F2<TODAY()). The row number stays relative so the rule moves down each row, while $ locks the columns so every cell in the row tests D and F. Entering D2 without $ lets the reference drift across columns, highlighting single cells instead of rows.
Traps
- Validation is bypassed by pasting, and existing values are not re-checked until edited.
- Conditional formatting formula references must match the active cell at the top-left of the range.
- Advanced Filter criteria headers must exactly match the data headers.
- Subtotals on unsorted data produce fragmented groups.