Data Management, Sorting, and Filtering
Scope
Data Management, Sorting, and Filtering tests turning a range into a structured table and reshaping or reordering large data sets safely.
Tables and sorting
Ctrl+T converts a range into a table with a header row, banded rows and structured references such as Table1[Sales]. Sorting can be single-level from the Data tab or multi-level through the Sort dialog: add levels, choose the column, Sort On (values, cell colour, font colour, conditional formatting icon) and Order (A to Z, Z to A, custom list). Options include case sensitivity and left-to-right sorting. Excel expands the selection automatically when the active cell is inside a contiguous range; otherwise only the selected column sorts and rows are scrambled.
Filtering and cleanup
AutoFilter adds dropdowns with text, number and date filters, Top 10, filter by colour and a search box. Filters hide rows without deleting them. Advanced Filter copies results to another location using a criteria range and can extract unique records. Remove Duplicates deletes repeated rows permanently. Text to Columns splits a column on a delimiter or at fixed widths. Subtotal inserts grouped totals and outline levels but requires the data sorted by the grouping field first.
Worked example
A sales list must be sorted by Region, then by Sales from largest to smallest. Select any cell in the list, open Data > Sort, tick My Data Has Headers, set level 1 to Region A to Z, then add level 2 with Sales and Largest to Smallest. Sorting the Sales column alone with a single-column selection would detach values from their rows - the classic error this question tests.
Traps
- Subtotal on unsorted data creates repeated group totals.
- Filtered-out rows are still included by COUNTA but excluded by SUBTOTAL with function number 109.
- Remove Duplicates keeps the first occurrence and cannot be undone after saving.
- Advanced Filter criteria ranges need header spellings identical to the data.