Manage Data Cells and Ranges
Shaping and cleaning data
This domain rewards precision with Paste Special (Values, Formats, Transpose, Skip Blanks, Add/Subtract operations), the fill handle and Flash Fill, Text to Columns, Remove Duplicates, sort and multi-level sort, AutoFilter and Advanced Filter, Subtotal with outline grouping, and custom number formats. You also need conditional formatting (highlight rules, data bars, icon sets, formula-based rules), data validation (lists, whole numbers, dates, custom formulas, input and error messages), and named ranges with either workbook or worksheet scope.
Traps exam writers like
- Paste Special > Values destroys formulas; pasting with the default option overwrites formatting and can shift conditional formatting rules.
- Conditional formatting rules evaluate top to bottom; a rule marked Stop If True blocks every rule beneath it. Reorder rules with the arrow buttons, not by re-creating them.
- Remove Duplicates keeps the first occurrence of each set and deletes the rest, so the surviving row depends on sort order.
- Named ranges default to workbook scope; a sheet-scoped name only resolves on its own sheet.
- Data validation only blocks new entries. To flag existing bad values, use Data > Data Validation > Circle Invalid Data.
- SUBTOTAL(9,…) ignores filtered-out rows but still counts manually hidden rows; 109 ignores both.
Worked example
To freeze a formula's results before consolidating: select the range, Ctrl+C, then Paste Special > Values. To build a custom format showing thousands with no decimals and red negatives: #,##0;[Red]-#,##0. Custom formats change display only - the underlying value is untouched, which is exactly the trap questions target.