Manage tables and table data
The Manage tables and table data domain tests whether you can convert a range into a structured table, use table names and structured references, and sort or filter data without damaging records. It also checks the difference between table features and ordinary range features.
Creating and structuring
- Ctrl+T or Insert > Table creates a table; confirm My table has headers.
- The table name appears in the Name Box and Table Design tab. Names must be unique, contain no spaces, and cannot look like a cell reference.
- Table styles control banded rows/columns, header row, total row, and first/last column emphasis. Convert to Range keeps the data but removes table behavior.
- The Total Row inserts SUBTOTAL formulas. A calculated column fills the entire column with one formula.
Sort, filter, and deduplicate
- AutoFilter drop-downs sort A-Z or by color and filter records. The Sort dialog supports multiple levels and custom lists.
- Slicers filter tables and PivotTables visually. Remove Duplicates permanently deletes rows based on selected columns.
- Structured references use table and column names:
=SUM(Sales[Amount]); in a calculated column,=[@Qty]*[@Price]; in a total row,=SUBTOTAL(109,[Amount]).
Worked example
A table named Sales has Qty and Price. Add a calculated column Total with =[@Qty]*[@Price]. The [@...] means the current row, and Excel fills the column. Add a total row and choose Sum for Total; Excel inserts =SUBTOTAL(109,[Total]), which ignores filtered-out rows. If you typed =SUM(Sales[Total]), the total would include hidden filtered rows.
Traps
Converting a table to a range replaces structured references with cell references. The total row uses SUBTOTAL, not SUM, when filtering must be respected. Table names cannot contain spaces. Filters hide rows rather than delete them. Sorting only a selected column can misalign records. Remove Duplicates deletes data permanently.